What does the “i” stand for in this SQL query?

I’m looking at a bunch of SQL query’s made by another individual and am trying to interpret how they were made. I’m fairly new to SQL so its involved a lot of Googling, its also my first time posting a SQL question.

I’ve seen the letter “i” entered in a lot of queries. Below is an example query. Does anyone know what this “i” stands for in the “select year (i.InspectDate)” line”?

-- Inspection Counts by Type

-- Facility inspections (and a few others) are not associated with wells -- therefore I removed the join to 
-- the Well table to get the correct counts


declare @year int
select @year = '2018'

select year(i.InspectDate), i.InspectType, rc.description, count(i.PKey) 
from Inspect I 
left join refcodes rc on rc.code = i.InspectType and rc.Fld = 'inspecttype'
where i.InspectType in ('AR', 'AI', 'BO', 'CO', 'CR', 'DO', 'DR', 'ER', 'ES', 'EMIT', 'RF', 'FR', 'MW', 'GE', 
'IMIT', 'MITNONWIT', 'CV', 'BT', 'PA', 'PO', 'RP', 'SEISMIC', 'SI', 'SR', 'UI', 'VI', 'WR')
and year(i.InspectDate) = @year
group by year(i.InspectDate), i.InspectType, rc.description
order by rc.description


select 'Totals', count(i.PKey) 
from Inspect I 
left join refcodes rc on rc.code = i.InspectType and rc.Fld = 'inspecttype'
where i.InspectType in ('AR', 'AI', 'BO', 'CO', 'CR', 'DO', 'DR', 'ER', 'ES', 'EMIT', 'RF', 'FR', 'MW', 'GE', 
'IMIT', 'MITNONWIT', 'CV', 'BT', 'PA', 'PO', 'RP', 'SEISMIC', 'SI', 'SR', 'UI', 'VI', 'WR')
and year(i.InspectDate) = @year 


-- select * from inspect


/*
InspectType RefCodes

X   AI  Audit
X   AR  Administrative Request
X   BT  OTHER
X   CO  Casing/Cement Operations
X   CR  Complaint
X   CV  Non-Compliance Verification
X   DR  Drilling
X   ER  Emergency Response
X   FR  Final Restoration/Bond Release
X   MW  MIT Witnessed   
X   PA  Plugging
X   PO  Presite
            0   PW  Plugging Witnessed  (NOT USED)
X   RP  Production/Environmental
X   WR  Workover/Recompletion
X   SI  Spill/Incident
X   RF  Facility Inspection
X   IMIT    Internal Mechanical Integrity Test
X   EMIT    External Mechanical Integrity Test
X   VI  Verification Inspection
X   UI  UIC Well
X   BO  BOPE
X   DO  Documentation
X   GE  Geophysical Exploration
X   ES  Environmental/Sampling
X   SR  Step Rate Test
X   SEISMIC Seismic Inspection
X   MITNONWIT   MIT Not Witnessed
            0   UNKNOWN Unknown (NOT USED)
*/

​x
 
-- Inspection Counts by Type​-- Facility inspections (and a few others) are not associated with wells -- therefore I removed the join to -- the Well table to get the correct counts​​declare @year intselect @year = '2018'​select year(i.InspectDate), i.InspectType, rc.description, count(i.PKey) from Inspect I left join refcodes rc on rc.code = i.InspectType and rc.Fld = 'inspecttype'where i.InspectType in ('AR', 'AI', 'BO', 'CO', 'CR', 'DO', 'DR', 'ER', 'ES', 'EMIT', 'RF', 'FR', 'MW', 'GE', 'IMIT', 'MITNONWIT', 'CV', 'BT', 'PA', 'PO', 'RP', 'SEISMIC', 'SI', 'SR', 'UI', 'VI', 'WR')and year(i.InspectDate) = @yeargroup by year(i.InspectDate), i.InspectType, rc.descriptionorder by rc.description​​select 'Totals', count(i.PKey) from Inspect I left join refcodes rc on rc.code = i.InspectType and rc.Fld = 'inspecttype'where i.InspectType in ('AR', 'AI', 'BO', 'CO', 'CR', 'DO', 'DR', 'ER', 'ES', 'EMIT', 'RF', 'FR', 'MW', 'GE', 'IMIT', 'MITNONWIT', 'CV', 'BT', 'PA', 'PO', 'RP', 'SEISMIC', 'SI', 'SR', 'UI', 'VI', 'WR')and year(i.InspectDate) = @year ​​-- select * from inspect​​/*InspectType RefCodes​X   AI  AuditX   AR  Administrative RequestX   BT  OTHERX   CO  Casing/Cement OperationsX   CR  ComplaintX   CV  Non-Compliance VerificationX   DR  DrillingX   ER  Emergency ResponseX   FR  Final Restoration/Bond ReleaseX   MW  MIT Witnessed   X   PA  PluggingX   PO  Presite            0   PW  Plugging Witnessed  (NOT USED)X   RP  Production/EnvironmentalX   WR  Workover/RecompletionX   SI  Spill/IncidentX   RF  Facility InspectionX   IMIT    Internal Mechanical Integrity TestX   EMIT    External Mechanical Integrity TestX   VI  Verification InspectionX   UI  UIC WellX   BO  BOPEX   DO  DocumentationX   GE  Geophysical ExplorationX   ES  Environmental/SamplingX   SR  Step Rate TestX   SEISMIC Seismic InspectionX   MITNONWIT   MIT Not Witnessed            0   UNKNOWN Unknown (NOT USED)*/​

Thanks

Answer

It’s an alias, declared here:

from Inspect I

 
from Inspect I ​

Remember SQL is not case sensitive, so the I alias declared as a mnemonic for the Inspect table is the same i used in the SELECT list.

It’s good practice to use table/view aliases in queries, because aside from saving you a bunch of typing you sometimes need to reference more than one instance of the same table; aliases allow you to disambiguate which instance you mean. They also help with query portability.

This question demonstrates the use of single-letter or very short mnemonics for each table/view. Single-letter variables are discouraged in most programming circles, but it’s fine for this purpose, as long as it’s a mnemonic for the full name. The practice is very common and well-understood among database developers. You may, however, sometimes run into sequential single-letter aliases (a, b, c, continuing in the order the tables/views appear), and that is not good practice.

Advertisement

Answer