Обсуждение: select from multiple tables

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.

On 06/12/2012 11:16 AM, 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
);

Cheers,
Steve

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

----- Original Message -----

From: Alessandro Gagliardi

To: Lew

Cc: pgsql-novice@postgresql.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@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

(Top-posting per material quoted)

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

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.

,
Lew

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@path.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.
>