What is use of EXCEPT Clause?

Next>>         SQL Server tutorial  

SQL Server - What is use of EXCEPT Clause? - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

What is use of EXCEPT Clause? Explain with an example

EXCEPT clause returns any distinct values from the left query that are not found in the right side query.

Eg:

USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;

SQL Server - What is use of EXCEPT Clause? - May 05, 2009 at 22:00 PM by Rajmeet Ghai

What is use of EXCEPT Clause? Explain with an example

EXCEPT clause is used to return records that are returned by the first query and NOT the second query. The number of fields and data types of both the queries should be the same.

Example:

SELECT Empid, EmpName FROM Employee WHERE Salary > 1000 EXCEPT SELECT Empid, EmpName FROM Employee WHERE Salary > 1500

SQL Server - What is use of EXCEPT Clause? - June 21, 2009 at 09:00 AM by Amit Satpute

To filter the result of a query to suit a certain criteria, the NOT IN clause can be used as follows:

Data of emp table;

EMPNO           ENAME               TITLE
-----------          ----------               --------
2                      SMITH                 CLERK
1                      ALLEN                SALESMAN
3                      WARD                 CLERK    
4                      JONES                SALESMAN
5                      MARTIN               CLERK

select * from emp where 2 not in empno;

OUTPUT:

1                      ALLEN                 SALESMAN   
3                      WARD                  CLERK
4                     JONES                  SALESMAN
5                     MARTIN                 CLERK

Next>>

Also read