Web Analytics Made Easy - Statcounter
Except command in SQL Server returns the distinct values from the left query that are not also found on the right query. For Except command, below basic rules must be followed.

  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.

Syntax

{ query_specification }
EXCEPT
{ query_specification }

In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)

We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A001′,’Vivek’,’Johari’,’samir@abcmail.com’,200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A002′,’Amit’,’Kumar’,’amit@abcmail.com’,100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A003′,’Neha’,’Sharma’,’neha@abcmail.com’,300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A004′,’Chandra’,’Singh’,’vivek@abcmail.com’,320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values(‘A005′,’Avinash’, ‘Dubey’,’avinash@abcmail.com’,250000)
Example

For example, if we execute the below queries individually, we get the following result:-

Select Empnumber from employee where salary between 100000 and 250000

Result

A001
A002
A005

Select Empnumber from employee where salary>200000

Result

A003
A004
A005

Now, if we use Except command between these two above mention queries, we got the following result:-

Select Empnumber from employee where salary>200000
EXCEPT
Select Empnumber from employee where salary between 100000 and 250000
Results

A003
A004
If we reverse the order of the SQL Queries, we get the different results

Select Empnumber from employee where salary between 100000 and 250000
EXCEPT
Select Empnumber from employee where salary>200000
Result

A001
A002

From the above example’s results, we can say that Except command returns the distinct values from the left query that are not also found on the right query

DMCA.com


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

19 thought on “Except command in SQL Server”
  1. … [Trackback]

    […] Here you can find 87286 additional Information on that Topic: techmixing.com/2014/08/except-command-in-sql-server.html […]

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading