Re: 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<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<http://www.postgresql.org/mailpref/pgsql-novice>
>