Unmatched query  
Author Message
JamesKilner





PostPosted: Sun Feb 20 11:17:19 CST 2005 Top

SQL Server Developer >> Unmatched query

Hi all,
I was wondering what the best SQL code is to find all records in table_A
where they do not exist is Table_B

I use
select a.* from Table_A a left outer join Table_b b
on a.ID = b.ID
where b.ID is null

Is the the most efficient way to do this or is there better codsde for this?
TIA,
Joe

SQL Server266  
 
 
David





PostPosted: Sun Feb 20 11:17:19 CST 2005 Top

SQL Server Developer >> Unmatched query
> Hi all,
> I was wondering what the best SQL code is to find all records in
> table_A where they do not exist is Table_B
>
> I use
> select a.* from Table_A a left outer join Table_b b
> on a.ID = b.ID
> where b.ID is null
>
> Is the the most efficient way to do this or is there better codsde
> for this? TIA,
> Joe

That query requires a full join of all rows. I would normall use
something like the following, but as with all queries, you should test
all options and examine execution plans before making a final decision.

Select a.*
From TableA
Where Not Exists (
Select *
From TableB
Where TableA.ID = TableB.ID)


--
David Gugick
Imceda Software
www.imceda.com

 
 
jaylou





PostPosted: Sun Feb 20 14:37:03 CST 2005 Top

SQL Server Developer >> Unmatched query Thank you. I Will look at the execution plan of both.

Thanks again,
Joe




> > Hi all,
> > I was wondering what the best SQL code is to find all records in
> > table_A where they do not exist is Table_B
> >
> > I use
> > select a.* from Table_A a left outer join Table_b b
> > on a.ID = b.ID
> > where b.ID is null
> >
> > Is the the most efficient way to do this or is there better codsde
> > for this? TIA,
> > Joe
>
> That query requires a full join of all rows. I would normall use
> something like the following, but as with all queries, you should test
> all options and examine execution plans before making a final decision.
>
> Select a.*
> From TableA
> Where Not Exists (
> Select *
> From TableB
> Where TableA.ID = TableB.ID)
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
>
 
 
Rob





PostPosted: Sun Feb 20 15:32:52 CST 2005 Top

SQL Server Developer >> Unmatched query I think this is the fastest way.Use of 'IN' is the same, but when use 'NOT
IN' then it's slower.

select a.* from Table_A a
where not exists (select 1 from Table_b b where a.ID = b.ID)

The above code is also more readable.
Rob



> Hi all,
> I was wondering what the best SQL code is to find all records in table_A
> where they do not exist is Table_B
>
> I use
> select a.* from Table_A a left outer join Table_b b
> on a.ID = b.ID
> where b.ID is null
>
> Is the the most efficient way to do this or is there better codsde for
> this?
> TIA,
> Joe


 
 
jaylou





PostPosted: Mon Feb 21 09:11:04 CST 2005 Top

SQL Server Developer >> Unmatched query Thank you.

I ran the execution plan for bath and they both have identical plans. I
executed them both and mine took 3 seconds more then yours. Not a
significant difference but yours is more efficient.

Thanks,
Joe



> I think this is the fastest way.Use of 'IN' is the same, but when use 'NOT
> IN' then it's slower.
>
> select a.* from Table_A a
> where not exists (select 1 from Table_b b where a.ID = b.ID)
>
> The above code is also more readable.
> Rob
>


> > Hi all,
> > I was wondering what the best SQL code is to find all records in table_A
> > where they do not exist is Table_B
> >
> > I use
> > select a.* from Table_A a left outer join Table_b b
> > on a.ID = b.ID
> > where b.ID is null
> >
> > Is the the most efficient way to do this or is there better codsde for
> > this?
> > TIA,
> > Joe
>
>
>