[GENERAL] [Q] Table aliasing
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 http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html but it only mentions that subselect in FROM must have an alias. I would like to know about IN (...) and EXISTS (...). Thank you, L. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] Table aliasing
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 INNER JOIN partner_share AS ps ON d2.deal.id = ps.deal_id INNER JOIN partner AS p 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 lenart...@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 http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html but it only mentions that subselect in FROM must have an alias. I would like to know about IN (...) and EXISTS (...). Thank you, L. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] Table aliasing
Ladislav Lenart wrote * 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 http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html but it only mentions that subselect in FROM must have an alias. I would like to know about IN (...) and EXISTS (...). Aliases are mandatory for self-joins. Basically two relations at the same query level cannot have the same name. With sub-selects the inner level shields the outer level from being visible so an alias is optional. I believe the case of a correlated sub-query requires the alias for the same reason - otherwise you end up with a always true where clause when joining the supposed two deal tables together. There is no right/best way but it is always more clear to rename such that you avoid repeating the same relation name in the query. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Q-Table-aliasing-tp5773355p5773364.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] Table aliasing
On 4.10.2013 15:11, David Johnston wrote: Ladislav Lenart wrote * 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 http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html but it only mentions that subselect in FROM must have an alias. I would like to know about IN (...) and EXISTS (...). Aliases are mandatory for self-joins. Basically two relations at the same query level cannot have the same name. With sub-selects the inner level shields the outer level from being visible so an alias is optional. I believe the case of a correlated sub-query requires the alias for the same reason - otherwise you end up with a always true where clause when joining the supposed two deal tables together. There is no right/best way but it is always more clear to rename such that you avoid repeating the same relation name in the query. I think I understand. Thank you, Ladislav Lenart -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
