CODEWITHSUNDEEP
sqlsql-servert-sql
Zanam
Zanam

Reputation: 4807

SQL select with not statement

I have table 1 as:

Name    Name2    Number 
asd     voe      1.00 
rt      casg     4.00 
qdef    fr       62.00 
erfr    cre      25.00 
fgf     sewq     55.50 
ytyt    dsvf     68.50 
ujer    wqw      81.50 
uyjum   vtr      94.50

I want to select all columns from table 1 which do not match:

Name2    Number 
cre      25.00 
sewq     55.50 
casg     4.00

i.e. my final output should be:

Name    Name2    Number 
asd     voe      1.00 
qdef    fr       62.00 
ytyt    dsvf     68.50 
ujer    wqw      81.50 
uyjum   vtr      94.50

Right now I am doing it in two stages where I use Except statement and temp tables.

Is there a smarter way to do this?

Upvotes: 1

Views: 118

Answers (3)

Hadi
Hadi

Reputation: 37313

If the data is in another table, You can use NOT EXISTS (recommended)

SELECT * FROM TABLE1 
WHERE
NOT EXISTS (SELECT 1 FROM TABLE2  AS T2 WHERE T2.NAME2 = NAME2 AND T2.NUMBER  = NUMBER)

Or a LEFT JOIN

SELECT T1.*
FROM TABLE1 T1 LEFT JOIN TABLE2 T2
ON T1.NAME2 = T2.NAME2 AND T1.NUMBER = T2.NUMBER
WHERE T2.NAME2 IS NULL

For more info, read this article it contains useful information on not in vs not exists vs left join

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7918

What you are doing are trying to accomplish is described as an left anti semi join in relational algebra. If you look at the actual execution plan for each of Gordon's solutions SQL server uses the Nested Loops Anti Semi Join algorithm to filter the rows. In SQL Server you can perform an anti semi join using NOT IN and NOT EXISTS logical operators as Gordon demonstrated. If you did not need to return the Name column from table1 you could also perform an anti semi join using EXCEPT which is a set operator. The solution using EXCEPT would look like this:

SELECT name2, number FROM table1
EXCEPT 
SELECT name2, number FROM table2;

The advantage of EXCEPT over NOT EXISTS and NOT IN is cleaner code. A key disadvantage is how set operators (EXCEPT, INTERSECT and UNION [|ALL]) requires you to have an equal number of columns in each referenced table/set.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The canonical way would be not exists:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.name2 = t1.name2 and t2.number = t1.number
                 );

As Hadi shows you can also write this as a left join.

Contrary to the comment, you can do this using not in, with a correlated subquery:

select t1.*
from table1 t1
where t1.number not in (select t2.number
                        from table2 t2
                        where t2.name2 = t1.name2
                       );

(Other databases do support not in on tuples; SQL Server is not one of them.)

I strongly advocate using not exists or left join, because the above has unexpected behavior when there are NULL values in table2.

Also, this is not safe if number is stored as a floating point number. This should be fine if number is a decimal/numeric type.

Upvotes: 2

Related Questions