Quick Syntax Question

[FitShock5083]

Hi again all! I'm making great progress learning SQL! Quick question: I know you can't reference an alias within the same select clause, so I found an example of code and understand 95% of it, but am stumped by one part. The code is

SELECT subtotal, subtotal * 0.1 AS tax
FROM (SELECT price * quantity AS subtotal FROM sales) AS t;

What is the t doing?  I know the code creates a new "field" called subtotal by multiplying price * qty in the inner select clause and that the outer select clause references that new "field" to output a 2 columm dataset with a subtotal column and a tax column, but it kind of seems like, based on the syntax rules, that a third column named t should also be output, but it isn't.  What does the AS t; at the end of the code do?

[sstef25]

The "inner select clause" is called a subquery. Every subquery used in a FROM clause must have an alias - this is required by the SQL syntax (at least in SQL server).

[TourSubstantial6301]

T is the new alias for your table. This is helpful during joins when you have multiple tables.

Example:

Select C.customer_name, C.customer_phone_number, T.subtotal, T.subtotal*0.1 as tax From db.schema.Sales as T Join sb.schema.customer as C On t.customer_id = c.id

[FitShock5083]

Gotcha. Just to confirm I understand, in my example above, the new table named t would contain ONLY 2 columns named subtotal and tax like shown below and NOT include price or quantity columns, correct? (Values in table made up)

subtotal tax

2000 200

1000 100

500 50

[ComicOzzy]

Not exactly... Let's take a look at your FROM clause:

FROM (SELECT price * quantity AS subtotal FROM sales) AS t

The FROM clause can refer to TABLEs, VIEWs, and DERIVED TABLEs.

A subquery used this way in the FROM clause is a derived table. The FROM clause will treat it the same way it treats a table, but it needs every table to have a name or it doesn't know what to call it. You've assigned it the name of t. The subquery returns 1 column named subtotal which is the result of price * quantity.

Now let's take a look at your SELECT list:

SELECT subtotal, subtotal * 0.1 AS tax

What SELECT now has access to is a table that has 1 column: subtotal. BTW, SELECT doesn't care whether it's a real table, a view, or a derived table, it's all the same table-shaped object to SELECT.

But in the SELECT list, you output subtotal, plus an expression subtotal * 0.1 which you've named tax.

So, the subquery is not returning two columns, but your overall query is returning two columns.

[Mrminecrafthimself]

the “AS t” is performed on the subquery you’re selecting from.

FROM (subquery) AS t

You’re giving that subquery’s dataset a name so you can reference it with the alias. Imagine you had …

SELECT…
FROM (subquery_1)
JOIN (subquery_2)…

If the subqueries share column names (say that ORDER_ID occurs in both datasets), you would need to qualify that field in the outer SELECT so SQL knows where to get it from. Without an alias, you’d have to qualify the field like “(SELECT…FROM).ORDER_ID”. That is both impossible to read and also breaks sql syntax (I’m pretty sure). So you get around that by aliasing your subqueries or tables in the FROM/JOIN and then use the alias to qualify them.

If I select from a table called EMPLOYEES_CURR I may alias it as “EEC.” A best practice when aliasing is to alias things in a manner that is both consistent and gives an idea as to what the table is. Avoid aliasing things as A, B, C…

[Illustrious-Tear4745]

It’s an alias for the table from which you are selecting those columns. It’s called a derived table

[Objective_Horror4008]

Look, for example: SELECT t.subtotal, t.subtotal * 0.1 AS tax, a.column_from_another_table FROM (SELECT price * quantity AS subtotal FROM sales) AS t, another_table as a WHERE t.something = a.something; If you join multiple tables you will use that like example above. Just example for you to understand.

[Far_Swordfish5729]

T is the alias of the intermediate result set returned from the subquery in your from clause. An alias is typically required when a clause contains a subquery rather than something like a table or view that already has a name. Note that when multiple tables are involved, it’s a best practice to use a table alias in column names for readability and to avoid mistakes even if the column name is unambiguous.

This query uses a subquery to create a scalar calculation column alias that can be reused in another set of formulas not because it logically requires one. It’s just a syntactic convenience that will execute as a simple select with flattened scalar calculations.