Re: select from multiple tables

(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(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.
>