Except command in SQL Server

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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Basic Concepts, SQL Server and tagged . Bookmark the permalink.

19 Responses to Except command in SQL Server

  1. Pingback: fun 88

  2. Pingback: http://ballarini.com.pl/corka-kwiaciarza-1221.php

  3. Pingback: https://biznesblog.biz.pl/forum/misz-masz-f7/smaczne-jedzenie-t33.html

  4. Pingback: Where Does Gold Come From Youtube and Why is Gold Rare

  5. Pingback: กังนัม คลินิก

  6. Pingback: uniccshop bazar

  7. Pingback: Travel

  8. Pingback: 1qiuqiu99

  9. Pingback: paragnost

  10. Pingback: go here

  11. Pingback: here

  12. Pingback: best truck decals

  13. Pingback: qiuqiu99

  14. Pingback: klikdokter.com

  15. Pingback: aco

  16. Pingback: link vao w88

  17. Pingback: Singles clubs

  18. Pingback: Anadrol Bestellen

  19. Pingback: w88.vn

Leave a Reply