Re: select from multiple tables

From: "Oliver d'Azevedo Christina" <oliveiros(dot)cristina(at)asperger-talents(dot)com>
To: "Alessandro Gagliardi" <alessandro(at)path(dot)com>, "Lew" <noone(at)lewscanon(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: select from multiple tables
Date: 2012-06-18 17:54:53
Message-ID: 99F43A8773124BD0BF6DF77E40A398E7@Moon
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Best,
Oliveiros
----- Original Message -----
From: Alessandro Gagliardi
To: Lew
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Monday, June 18, 2012 5:43 PM
Subject: Re: [NOVICE] select from multiple tables

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.

-Alessandro

On Mon, Jun 18, 2012 at 6:43 AM, Lew <noone(at)lewscanon(dot)com> wrote:

Steve Crawford wrote:

e-letter wrote:

Readers,

A database has table1, table2, table3, table4 and each table has
columns of text and integers:

table1
text1, 10
text2, 20
text3, 30
text4, 40
text5, 50
text6, 60
text7, 70
text8, 80

table2
text1, 10
text2, 20

table3
text3, 30
text4, 40

table4
text5, 50

Is it possible to create a new table (table5) by creating a query that
selects rows that _both_ exist in table1 _and_ are not in any of the
preceding tables (table2, table3, table4)?

Thanks in advance.

create table table5 as
select textcol, intcol from table1 except
(
select textcol, intcol from table2 union
select textcol, intcol from table3 union
select textcol, intcol from table3
);

But why into a separate table?

Isn't just having a SELECT result sufficient? Storing it is a denorm.

Wouldn't a VIEW be better?

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jeff Davis 2012-06-18 18:20:02 Re: Problem creating trigger
Previous Message Ken LaCrosse 2012-06-18 17:34:07 Re: Table name as a variable and/or EXECUTE and NEW.*