Re: [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 <lenartlad(at)volny(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>