More efficient query

[egodette]

I have a table with 2 columns A and B. Column A is unique and B will contain multiple entries of column A.
Example
Column A Column B
10 10
11 10
12 13

Without using a subselect how can I find entries in B that are not in A as above(13)?
I dont what to use a subselect as my table has over a million rows and subselect is too slow.

 

[gmmastros]

Can there be duplicates? Ex:

Column A Column B
10 10
[!]10 10[/!]
11 10
12 13

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Try this:

Select	ColumnB
From	YourTableNameHere

Except

Select	ColumnA
From	YourTableNameHere

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[egodette]

That is the same as a subselect. I figured it out using a Left Outer join where A is NULL
Thanks..

 

[gmmastros]

What do you mean that it is the same? Have you tested the performance and/or looked at the query plans?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For what it's worth, I just tested this on a table I have in my database. My table has 1/2 a million rows. The Except query was many times faster. In fact, it was nearly 10 times faster than the Left Join/NULL method.

I encourage you to try the advice I gave you earlier to see which performs better.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom