A Simple Query Help

Hi!
I have two tables (Master and Slave). So I want to write a query (using
SQL Query Analyser) which will display all the records in Master which
are not in Slave.
Can anyone help?
Cheers!!!If you mean a 1-n relation that should be somethin like this:
Select * from master m
Where not exists
(
Select * from slave s where s.joinedColumn = m.joinedcolumn
)
HTH, jens Suessmeyer.|||select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null
Regards
Amish|||select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null
Regards
Amish|||Amish,
note that not exists() approach may be up to 20% faster than your one:
select m.* from masters m
left join
slave s
on m.col = s.col where s.col is null|||Sasha
It depends .
I've seen many examples especially with large amount of data that JOIN
approach was faster than EXISTS (correlated subquery)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1134572338.586427.228630@.g14g2000cwa.googlegroups.com...
> Amish,
> note that not exists() approach may be up to 20% faster than your one:
> select m.* from masters m
> left join
> slave s
> on m.col = s.col where s.col is null
>|||>I 've seen many examples especially with large amount of data that JOIN
> approach was faster than EXISTS (correlated subquery)
Uri,
It's very very interesting. Can you please elaborate?
I usually see 2 different situations
- a parent row has on average just several child ones, then there is no
much difference to speak about
- several years down the road, a parent row (a customer) has several
hundred child ones (customer orders), then
the OUTER JOIN approach is up to 20% slower|||In some situations OUTER JOIN query requires fewer that half the number of
logical I/O that EXISTS does
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1134576273.447850.245560@.z14g2000cwz.googlegroups.com...
> Uri,
> It's very very interesting. Can you please elaborate?
> I usually see 2 different situations
> - a parent row has on average just several child ones, then there is no
> much difference to speak about
> - several years down the road, a parent row (a customer) has several
> hundred child ones (customer orders), then
> the OUTER JOIN approach is up to 20% slower
>|||yes, well, it always depends, that's why I said MAY be, not WILL be