Except Operator In SQL Server

In this article we will learn about Except operator in SQL Server.
  • 1901

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

StudentTable.jpg

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]

StudentDetailsTable.jpg

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:

ExceptOperator.jpg


© 2019 DotNetHeaven. All rights reserved.