I almost always alias my tables by default with something short (Usually 1 - 3 characters), but not my subselects for an in list. In this case I would do d1, d2, ps, and p for the different tables. I then do my best to use the same alias in all my queries. I am also big on formatting the SQL here is how I would write what you have for readability
SELECT * FROM deal AS d1 WHERE d1.deal.id IN ( SELECT DISTINCT deal.id FROM deal AS d2
ON ps.partner_id = p.partner.id WHERE p.team_id = 12345 AND (ps.type = 1 AND d2.external_id IS NOT NULL OR ps.type = 2 AND d2.external_id IS NULL) )
On Fri, Oct 4, 2013 at 5:59 AM, Ladislav Lenart <lenartlad@volny.cz> wrote:
Hello.
I have a noob question about table aliases in SQL.
Suppose the following query:
SELECT * FROM deal WHERE deal.id IN ( SELECT DISTINCT deal.id FROM deal JOIN partner_share ON deal.id = partner_share.deal_id JOIN partner ONshare.partner_id = partner.id WHERE partner.team_id = 12345 AND ( partner_share.type = 1 AND deal.external_id IS NOT NULL OR partner_share.type = 2 AND deal.external_id IS NULL ) )
As you can see, the IN (...) references the deal table too.
My questions: * Should I alias one of the references to deal? * The above query works the same both with and without an alias, so is it simply a matter of taste / good practice? * Where can I find more info about this, i.e. when the alias is mandatory and when it is only a convenience? I've scanned through