[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
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 <lenartlad@volny.cz> wrote:
Show quoted text
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 throughhttp://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
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 throughhttp://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
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 throughhttp://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