What letters are written before column titles

Question

I'm learning SQL language using online resources, but mostly using queries that my predecessors have written at my company. I'm editing fields correspondingly to produce the correct results. But I want to understand more.

I have a few questions about this section of code. 1. Why is there a "p" before TrackingNumber, and oh/cc/im in front of others? It seems to matter which I choose, so I just use trial and error until it runs. 2. Why do I need to have tracking number - when I delete this line, the code won't run!

select 

p.TrackingNumber
,im.Sku
,oh.BusinessUnitCode
,cc.Qty
,oh.ShipCode
,oh.OrigShipCode
,oh.Store
,convert(date,oh.ShipTime) as 'OrderDate'
,oh.ShipToName
,oh.OrderNumber


from dmhost.tblOrderHeader oh

join dmhost.tblContainer c on oh.OrderHeaderID = c.OrderHeaderID
join dmhost.tblPackage p on c.ContainerID = p.ContainerID
join dmhost.tblContainerContents cc on c.ContainerID = cc.ContainerID
join dmhost.tblItemMaster im on im.ItemMasterID = cc.ItemMasterID

where (oh.ShipTime between '04/07/2019' and '05/05/2019')

Can you provide the complete query instead of only the start of it? — , May 10 '19 at 13:39
You should find, if you look at the next part of the query, those 1/2-letter names are defined, I suspect you'll see something like `...from products p, items im...`, etc. Basically they are aliases for the table names that the columns are coming from. — DaveyDaveDave, May 10 '19 at 13:40
For your second point - "Why do I need to have tracking number" - I'm guessing that when you remove it, you're leaving the comma before `,im.Sku`. Presumably the response you see is an error complaining about an invalid character - it's simply not expecting a comma as the first thing in a list, just like in English. — DaveyDaveDave, May 10 '19 at 13:42
Kind-of duplicate of [When to use SQL Table Alias](https://stackoverflow.com/questions/198196/when-to-use-sql-table-alias) — DaveyDaveDave, May 10 '19 at 13:43

score 0 · Accepted Answer · answered May 10 '19 at 13:42

The letters you talk about are referring to table names (or aliases).

Example using aliases would be:

SELECT c.customerName, o.orderNumber from Customers c 
INNER JOIN Orders o on c.id=o.customerid

Same query without aliases:

SELECT Customers.customerName, Orders.orderNumber from Customers  
INNER JOIN Orders on Customers.id=Orders.customerid

or omitting table names

SELECT customerName, orderNumber from Customers  
INNER JOIN Orders on Customers.id=Orders.customerid

The table denomination is specially important when you retrieve columns with the same name from different table. For example id from Customers and id from Orders

SELECT c.id as CustomerId, o.id as OrderId from Customers c 
INNER JOIN Orders o on c.id=o.customerid

score 0 · Answer 2 · answered May 10 '19 at 13:44

The bits before the dot (.) in your field names are table aliases. If you look in the FROM clause of this query you should see these abbreviations in front of the various tables listed in there. They're used to

a) make it less tedious to type table names and

b) make it unambiguous which table you are selecting the column from (this both increases readability of the code and also deals with any cases where two of the tables have columns with the same name)

Here's a simple example of table alias usage:

SELECT emp.ID, emp.Name, dep.ID, dep.Name
FROM employees emp
INNER JOIN departments dep ON dep.ID = emp.DepartmentID

Here you can see that the employees and departments tables have each got aliases to shorten their name. In the query we refer to each field using it's alias. This is especially useful since both tables have fields called "ID" and "Name".

As for why it crashes when you remove p.TrackingNumber, it's likely because you did not also remove the comma (,) from the next line. The comma is used to mark where the name of the next field begins - it could be at the end of the previous line, rather than the start of the next one. Clearly you can't start the list of fields with a comma, because there is no field name preceding it - hence you get a syntax error.

The same query could have been written

select 
  p.TrackingNumber,
  im.Sku,
  oh.BusinessUnitCode,
  -- etc

which might make it easier to see the usage of the comma.

thank you. You were correct about the use of commas. I was forgetting to delete the comma from the second row. — LJG1993, May 17 '19 at 14:13
@LiamGibbons no problem. If the answer has helped you please remember to mark it as "accepted" (click the tick mark next to the question so it turns green) - thanks :-) — ADyson, May 17 '19 at 14:18

What letters are written before column titles

2 Answers2