Search Postgresql Archives

Re: [Q] Table aliasing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux