The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there are no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.
Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly the same. EXCEPT and NOT IN does same functions and have the same execution plan, but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as aliases.
Example: (Both of the scripts returns the same number of rows)
----SQL SERVER 2005 Method USE AdventureWorks; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; GO ----SQL SERVER 2000 Method, which works&nbspIN SQL SERVER 2005 USE AdventureWorks; GO SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder); GO
Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan.
Reference : Pinal Dave (https://blog.sqlauthority.com)
63 Comments. Leave new
Pinal,
I would suggest against a NOT IN clause as any time a NULL comes back in the NOT IN set, the query will bring back an empty set.
NOT EXISTS seems like a better option, as NULLs do not cause the same problems and runs with the same performance.
SELECT ProductID
FROM Production.Product P
WHERE NOT EXISTS (
SELECT ProductID
FROM Production.WorkOrder W
WHERE P.ProductID = W.ProductID);
My two cents.
Reeves
There are centain shortcomings while using EXCEPT with TOP expression
When you want to retrieve TOP 1 row from the query, you may get 0 rows, even though there might be many qualifed rows.
This will happen when the first row in the left query is present in the right query.
Example
SELECT TOP (1) ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
Again, when you try to retieve TOP 100, you may get less than 100 rows.
This will not happen when you use NOT IN.
Mahender Pabba
Pinal,
Even if you have qualified rows still you wont get desired result
Example
create table t1 (Product_Id tinyint)
create table t2 (Product_Id tinyint)
insert into t1 values (1)
insert into t1 values (2)
insert into t1 values (3)
insert into t1 values (4)
insert into t2 values (1)
select * from t1
except
select * from t2
Result (3 rows)
2
3
4
select top 1 * from t1
except
select * from t2
Result (0 rows)
select top 2 * from t1
except
select * from t2
Result (1 rows)
2
Regards,
Mahender Pabba
Hi Mahender,
I think this is quite Interesting but could you may explain why with TOP 1 no rows are returned…
thanks in advance!
Hi Mahender,
Try the below one to get the top1 row
select top 1 * from
(
select * from t1
except
select * from t2) as t
Since in your earlier query top CLAUSE is applied on first select statement, so the resultant is what used for except operation.
Regards,
Thivya
The performance of EXCEPT is very bad compared with NOT IN.
Hi,
How can I use NOT IN clause if I have 2 different conditions to check on my query? Here, I can have valid repeating values for both EmpId and GlobalRoldId as in the example below. Right now, I am using an EXCEPT clause though its working fine right now.
For e.g:
SELECT EmpId, GlobalRoleId
FROM dbo.tbl_PTS_EmpEntityRoleMapping
EXCEPT
SELECT EmpId, GlobalRoleId
FROM EmpEntityRoles
Thanks in advance.
You can do it with Not exists in this way
SELECT EmpId, GlobalRoleId
FROM dbo.tbl_PTS_EmpEntityRoleMapping a
where Not Exists
( SELECT * FROM EmpEntityRoles b
where b.EmpId = a.EmpId
and b.GlobalRoleId = a.GlobalRoleId
)
Pötyös
Does anybody know how to compare columns of two records, and replace values in SQL server 2003.
For example, if I have:
Class ID TeacherID Start Date
100 105 9/5/05
100 105 12/5/05
Lets say I have several record sets like this. How do I compare values of similar record sets to get the earliest start date? Thanx!
You can use this :
select classID , TeacherID, max(startDate) from tablename group by classID , TeacherID
Hi.
I have two tables with Same no. of columns.
We can use joins.
How can i get the rows that are different?
Thanks in advance.
I get the results I want when I use Except but it returns no rows when I use NOT IN. Why is this?
SQL SERVER 2005
SELECT RIGHT(Username,6) From Table1
Where RIGHT(Username, 6) NOT IN (Select RIGHT(Username, 6) from Table2)
returns 0 rows
SELECT RIGHT(Username, 6) From Table1
Except (Select RIGHT(Username, 6) from Table2)
returns 3 rows like I expect.
Is it due to the RIGHT modification and its evaluation?
Hi
I got the result from this topics,
Thanks
Hi,
The following is a query which works with not in. How do I get the same output on sql server?
select col1
from table1
where (col2, col3) not in
(select colA, colB
from table 2)
[Here, colA ~ col2 and colB ~ col3, so the data types are same]
Any help will be appreciated!
Thanks,
Kalpa
Hi i have two tables
I have to compare this 2 tables…,
Now which dates are not in table1 that date and that ename have to come in select statement
Table1:
TSID TSdate EName
100 7-21-2008 Name1
101 7-21-2008 Name1
102 7-22-2008 Name1
103 7-26-2008 Name1
104 7-23-2008 Name2
105 7-25-2008 Name2
Table2:
Nodates
7-21-2008
7-22-2008
7-23-2008
7-24-2008
7-25-2008
7-26-2008
output:
Date Name
7-23-2008 Name1
7-24-2008 Name1
7-25-2008 Name1
7-21-2008 Name2
7-22-2008 Name2
7-24-2008 Name2
7-26-2008 Name2
how to do that
@compare 2 tables
I am sure there must be other ways to do this, I used a cursor and while loop,
This can also be done with one cursor with out using a while loop, since the third column in first table contains duplicate rows, the cursor will be executed many times ( how many duplicate rows you have in column 3 in first table, that many times) I thought that would unneccarily consume performance so I chose cursor and while loop to increase performance of the query.
In short, this is also performence consuming, but I could not think of anything else,.. this is atleast a better one in my view..
Here starts your code….
CREATE TABLE TABLE_1 (TSID INT, TSDATE VARCHAR(10), ENAME VARCHAR(10))
INSERT INTO TABLE_1 (TSID , TSDATE, ENAME )
SELECT 100, ‘7-21-2008’ ,’NAME1’UNION ALL
SELECT 101, ‘7-21-2008’, ‘NAME1’UNION ALL
SELECT 102, ‘7-22-2008’, ‘NAME1’UNION ALL
SELECT 103, ‘7-26-2008’, ‘NAME1’UNION ALL
SELECT 104, ‘7-23-2008’, ‘NAME2’UNION ALL
SELECT 105,’7-25-2008’, ‘NAME2’
CREATE TABLE TABLE_2 (NODATES VARCHAR(10))
INSERT INTO TABLE_2 (NODATES)
SELECT ‘7-21-2008’UNION ALL
SELECT ‘7-22-2008’ UNION ALL
SELECT ‘7-23-2008’UNION ALL
SELECT ‘7-24-2008’UNION ALL
SELECT ‘7-25-2008’UNION ALL
SELECT ‘7-26-2008’
We created two tables, and inserted the sample data given by you,
I am creating two temporary tables, and then I dropped those two temporary tables at the end of the code.
SET NOCOUNT ON
DECLARE @ENAME VARCHAR(10)
DECLARE @DATE VARCHAR(10)
DECLARE @VAR INT
CREATE TABLE #RESULT ( DATE VARCHAR(10), NAME VARCHAR(10))
CREATE TABLE #TEMP1 (ID INT IDENTITY, ENAME VARCHAR(10))
INSERT INTO #TEMP1(ENAME) SELECT DISTINCT ENAME FROM TABLE_1
DECLARE @CUR CURSOR
SET @CUR = CURSOR FOR
SELECT DISTINCT NODATES FROM TABLE_2
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @VAR = 1
WHILE @VAR < = (SELECT COUNT(1) FROM #TEMP1)
BEGIN
SELECT @ENAME = ENAME FROM #TEMP1 WHERE ID = @VAR
IF EXISTS ( SELECT TSDATE FROM TABLE_1 WHERE TSDATE = @DATE)
BEGIN
IF EXISTS (SELECT ENAME FROM TABLE_1 WHERE TSDATE = @DATE AND ENAME @ENAME )
BEGIN
INSERT INTO #RESULT (DATE , NAME ) SELECT @DATE ,@ENAME
END
END
ELSE INSERT INTO #RESULT (DATE , NAME) SELECT @DATE, @ENAME
SET @VAR = @VAR +1
END
FETCH NEXT
FROM @CUR INTO @DATE
END
CLOSE @CUR
DEALLOCATE @CUR
SELECT * FROM #RESULT ORDER BY NAME , DATE
DROP TABLE #TEMP1
DROP TABLE #RESULT
GO
Result :
7-23-2008 Name1
7-24-2008 Name1
7-25-2008 Name1
7-21-2008 Name2
7-22-2008 Name2
7-24-2008 Name2
7-26-2008 Name2
Hope this helps,
Thanks,
Imran.
Just wanna say thanks, found this useful. Cheers.
Hello Sir,
I used topic
“17 Responses to “SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN””of( Reeves on July 19, 2007 at 9:57 am
)
It helped me lot in my problem.Thanks lot.
Thank you very much.
See you again.
Nandkishor
Hi all,
I want to compare two tables from two databases,where one is original database and another one is local database table.
Now,how do i compare both the tables,such that.when compared with original database if any record or column is updated in the local database,it should return me updated records.
Using Java and SQL ,how can i do this.Please reply as earle as possible.
Thanks in Advance!!!
Hi Sudha
you can do this with a timestamp column, everytime record is created or update you need to update the timestamp and than to find updated rows you just need to check the timestamp in orginal and local db.
You can also use red-gate to compare data and sql it’s very good DB tool.
USE TDB;
GO
SELECT Sim_No
FROM Sim_Record
EXCEPT
SELECT Sim
FROM Test_in
I need Result Drop In To New Table
Use CTE/into