Table labels and column aliases

A table alias is a temporary name given to a table within a query. It can reduce code clutter and make queries easier to read.

For more complex tasks like correlated subqueries table labels become mandatory so you can reference different instances of the same table within a query.

Query not using table labels or column aliases

-- note without table labels query is quite bulky to look at
select tbl_quotes.quote_id-- get quoteid
, tbl_quotes.quote_name-- get quote name
, tbl_quotes.insert_dt-- get date quote was inserted
, tbl_users.insert_dt-- get date user was inserted
from tbl_quotes-- use tbl_quotes as table on left of join
inner join tbl_users-- join to tbl_users
on tbl_quotes.insert_user_id = tbl_users.[user_id]-- join on user_id, not field is bracketed, this is because it's a reserved name in sql and to use it we have to inform sql we are using our own version
                            

Query using table labels but no column aliases

-- same query using table labels makes is easier to read. in the select statement (projection) instead of form tablename.fieldname we now use tablelabel.fieldname
select tq.quote_id
, tq.quote_name
, tq.insert_dt
, tu.insert_dt
from tbl_quotes tq-- using quotes again, but this time declare tq as table label. we can now refer to 
inner join tbl_users tu-- again provide table label for tbl_users
on tq.insert_user_id = tu.[user_id]-- not that 'join on' is now using the table labels
                            

Column aliases allow us to tailor the names of the selected columns. Few reasons you might want to do this including:

• Multiple columns with the same name, for example insert_dt above exists on the quotes and users table. Aliases allow us to differentiate in the results
• Variable naming schema in sql and the application may differ and you need to help mapping when hydrating a data model. For example the sql column may be named quote_name, but the model property is named QuoteName.

Query using table labels and column aliases

-- same query using column aliases
select tq.quote_id as QuoteId-- the returned column will be named QuoteId
, tq.quote_name as QuoteName-- the returned column will be named QuoteName
, tq.insert_dt as QuoteInsertDt-- the returned column will be named QuoteInsertDt
, tu.insert_dt as UserInsertDt-- the returned column will be named UserInsertDt
from tbl_quotes tq
inner join tbl_users tu
on tq.insert_user_id = tu.[user_id]
                            

#SQL techniques, #SQL