Significance Of Random Letter At The End Of Sub-query In FROM Clause - SQL

I have finally succeeded in summing the results of two sql sum queries. One small step for this guy:) My question relates to the last character in the code (Z): SELECT SUM(hr) FRO

Solution 1:

SELECT sum(hr) 
FROM
(
   Select sum(amount) as hr 
   from Try_again.dbo.tuesday_practice_database 
   where account_name like 'concessions'
   union 
   Select sum(amount) as hr 
   from Try_again.dbo.tuesday_practice_database 
   where account_name like 'salaries'
) z

To understand better, look at the above version of your query. It's the same code, just reformatted to help illustrate what is happening. If you notice, the parent FROM clause retrieves data from a sub-query. In this context, SQL requires the subquery to have a name of some kind, and so the z is added as an alias to meet that requirement. You could put anything you wanted there, but since the name doesn't matter to us a single-letter placeholder is fine.

Just like the following query :

select * from table1 as z

By the way, you didn't use wildcard in your LIKE clause! I think you should re-write the query like below :

select sum(hr) from 
(
   Select sum(amount) as hr from Try_again.dbo.tuesday_practice_database 
   where account_name like '%concessions&'
   union all
   Select sum(amount) as hr from Try_again.dbo.tuesday_practice_database 
   where account_name like '%salaries%'
) AS z

If you don't want to use wildcard, then you should avoid using LIKE Use IN instead and re-write to this simple one :

Select sum(amount) from Try_again.dbo.tuesday_practice_database 
where account_name in('concessions', 'salaries')

Solution 2:

Just do this:

SELECT SUM(amount) as hr
FROM Try_again.dbo.tuesday_practice_database
WHERE account_name IN ('concessions', 'salaries')

The existing code has a bug, where it will consolidate your two SUM()s if they are both the same amount. You could also fix this with UNION ALL instead of just UNION, but since the LIKE conditions don't have any placeholders like % or _ we can do better and simplify this whole thing down to just one IN() condition, with no need for any subqueries.

Now, if you wanted to allow more variance in your matches ( ie LIKE '%concessions%' and LIKE '%salaries%'), then the UNION ALL is more helpful... just be warned that leading % placeholders in a LIKE condition are very bad for performance, and should be avoided when possible. Often this means changing the schema in some way, such as adding a table named something like AccountCategories that group each account into a specific category that you can target exactly in your query.

But all of that side-steps the actual question: what is that z character?

In this case, it's an alias. You're using a subquery to union the two smaller queries together. In certain contexts, the SQL language requires subqueries to have a name. This includes using the subquery (derived table) as the target of a FROM, JOIN, or APPLY expression. You can use any name you want — it doesn't matter to the functioning of the query, since it's never referenced again — and so a simple single-letter placeholder, like z, is good enough.