Re: select from multiple tables - Mailing list pgsql-novice

From Oliver d'Azevedo Christina
Subject Re: select from multiple tables
Date
Msg-id 99F43A8773124BD0BF6DF77E40A398E7@Moon
Whole thread Raw
In response to select from multiple tables  (e-letter <inpost@gmail.com>)
Responses Re: select from multiple tables
List 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 -----
To: Lew
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@lewscanon.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

pgsql-novice by date:

Previous
From: Ken LaCrosse
Date:
Subject: Re: Table name as a variable and/or EXECUTE and NEW.*
Next
From: Jeff Davis
Date:
Subject: Re: Problem creating trigger