CODEWITHSUNDEEP
sql
adam.baker
adam.baker

Reputation: 1485

What is the importance of the table alias in this SQL query?

This answer solves the question originally posed (having to do with converting row data to column data) with this query:

SELECT 
(SELECT count(*) FROM table1 WHERE type2 = 'T1' AND type1 = mTable1.type1) AS T1,
(SELECT count(*) FROM table1 WHERE type2 = 'T2' AND type1 = mTable1.type1) AS T2,
(SELECT count(*) FROM table1 WHERE type2 = 'T3' AND type1 = mTable1.type1) AS T3
FROM table1 mTable1 GROUP BY type1

From testing the code, I can see that the table alias is necessary for the query to work correctly; the query below does not work.

SELECT 
(SELECT count(*) FROM table1 WHERE type2 = 'T1' AND type1 = table1.type1) AS T1,
(SELECT count(*) FROM table1 WHERE type2 = 'T2' AND type1 = table1.type1) AS T2,
(SELECT count(*) FROM table1 WHERE type2 = 'T3' AND type1 = table1.type1) AS T3
FROM table1 GROUP BY type1

Nevertheless I don't understand why the alias is necessary. Why does the second query mean something different from the first?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Forget the table alias. Simplify the code!

SELECT type1,
       SUM(CASE WHEN type2 = 'T1' THEN 1 ELSE 0 END) AS T1,
       SUM(CASE WHEN type2 = 'T2' THEN 1 ELSE 0 END) AS T2,
       SUM(CASE WHEN type2 = 'T3' THEN 1 ELSE 0 END) AS T3
FROM table1 t1
GROUP BY type1;

In your version of the code, the table aliases are needed so the inner references to table1 can be correlated with the outer reference to the same table. The table name is the same in both cases, so something other than the table name is needed to refer to the columns.

Upvotes: 3

Related Questions