Except Operator In SQL Server
In this article we will learn about Except operator in SQL Server.
In this article we will learn about Except operator in SQL Server. Except operator is used to find records exists only in left table of the query. It returns distinct rows from the left side of Except operator. The matched records between result set and right side of the Except operator are removed.
Syntax
select col1,col2,...,colN
from FirstTable
[where condition]
EXCEPT
select col1,col2,...,colN
from SecondTable
[where condition]
Example
First Table
CREATE TABLE Student
(
ID int NOT NULL,
Reg_ID int NOT NULL,
Reg_Date date,
CONSTRAINT [PK_StudentDetails] PRIMARY KEY CLUSTERED
([ID] ASC
) )
Insert some values and select records.
select * from Student
Second Table
CREATE TABLE [dbo].[Student1Details](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Branch] [varchar](10) NULL,
[Location] [varchar](10) NULL,
CONSTRAINT [PK_Student1] PRIMARY KEY CLUSTERED
(
[ID] ASC
))
Insert some values and select records.
select * from [Student1Details]
Except operator
Except operator is like NOT IN operator. It returns returns distinct rows from the left side of Except operator.
select ID from Student
where Reg_Date>'2009-05-12'
EXCEPT
select ID from Student1Details
where Location='IND'
Output:
Result from applying Except operator is:
