Skip site navigation (1) Skip section navigation (2)

Re: select from multiple tables

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: select from multiple tables
Date: 2012-06-19 05:25:48
Message-ID: jrp2gt$f0a$ (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
(Top-posting per material quoted)

One must always be careful with maxims of performance, where they get too 

Fundamentals like "Measure, don't assume" will always hold, but rules of thumb 
like "Joins beat nested queries" can pass quickly from handy tip to unfounded 
superstition when you're not looking.

Thanks for raising the flag, Oliver.


Oliver d'Azevedo Christina wrote:
> On old DBMS,
> nested query had the tendency to be slower than joins.
> But, I believe nowadays the difference is almost negligible...
> Just my two cents
>     ----- Original Message -----
>     *From:* Alessandro Gagliardi <mailto:alessandro(at)path(dot)com>

>     Would not
>     SELECT textcol, intcol FROM table1
>     JOIN table2 ON (table1.textcol = table2.textcol AND table1.intcol =
>     table2.intcol)
>     JOIN table3 ON (table1.textcol = table3.textcol AND table1.intcol =
>     table3.intcol)
>     JOIN table4 ON (table1.textcol = table4.textcol AND table1.intcol =
>     table4.intcol)
>     WHERE table2.textcol IS NULL AND table2.intcol IS NULL
>     AND table3.textcol IS NULL AND table3.intcol IS NULL
>     AND table4.textcol IS NULL AND table4.intcol IS NULL;
>     also work? I'm under the impression that anti-joins (like this) are
>     generally more efficient than nested queries (particularly those with
>     union) though perhaps that depends on indices.

In response to

pgsql-novice by date

Next:From: Jaime CasanovaDate: 2012-06-20 07:08:49
Subject: Re: Unknown winsock error 10061
Previous:From: Ken LaCrosseDate: 2012-06-18 23:21:56
Subject: COPY, Triggers and visibility into pg_tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group