SQL Server except operator

SQL Server except operator

sql except example
sql except operator example
sql server except example
sql server except order by
sql server except clause

SQL Server except operator

In this video we will discuss SQL Server except operator with examples.

EXCEPT operator returns unique rows from the left query that aren’t in the right query’s results.
Introduced in SQL Server 2005
The number and the order of the columns must be the same in all queries
The data types must be same or compatible
This is similar to minus operator in oracle

Let us understand this with an example.

SQL Script to create the tables

Create Table TableA
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10)
)
Go

Insert into TableA values (1, ‘Mark’, ‘Male’)
Insert into TableA values (2, ‘Mary’, ‘Female’)
Insert into TableA values (3, ‘Steve’, ‘Male’)
Insert into TableA values (4, ‘John’, ‘Male’)
Insert into TableA values (5, ‘Sara’, ‘Female’)
Go

Create Table TableB
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10)
)
Go

Insert into TableB values (4, ‘John’, ‘Male’)
Insert into TableB values (5, ‘Sara’, ‘Female’)
Insert into TableB values (6, ‘Pam’, ‘Female’)
Insert into TableB values (7, ‘Rebeka’, ‘Female’)
Insert into TableB values (8, ‘Jordan’, ‘Male’)
Go

Notice that the following query returns the unique rows from the left query that aren’t in the right query’s results.
Select Id, Name, Gender
From TableA
Except
Select Id, Name, Gender
From TableB

To retrieve all of the rows from Table B that does not exist in Table A, reverse the two queries as shown below.
Select Id, Name, Gender
From TableB
Except
Select Id, Name, Gender
From TableA

You can also use Except operator on a single table.

SQL script to create tblEmployees table
Create table tblEmployees
(
Id int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
Salary int
)
Go

Insert into tblEmployees values (‘Mark’, ‘Male’, 52000)
Insert into tblEmployees values (‘Mary’, ‘Female’, 55000)
Insert into tblEmployees values (‘Steve’, ‘Male’, 45000)
Insert into tblEmployees values (‘John’, ‘Male’, 40000)
Insert into tblEmployees values (‘Sara’, ‘Female’, 48000)
Insert into tblEmployees values (‘Pam’, ‘Female’, 60000)
Insert into tblEmployees values (‘Tom’, ‘Male’, 58000)
Insert into tblEmployees values (‘George’, ‘Male’, 65000)
Insert into tblEmployees values (‘Tina’, ‘Female’, 67000)
Insert into tblEmployees values (‘Ben’, ‘Male’, 80000)
Go

Except operator on a single table
Select Id, Name, Gender, Salary
From tblEmployees
Where Salary ]= 50000
Except
Select Id, Name, Gender, Salary
From tblEmployees
Where Salary ]= 60000

Order By clause should be used only once after the right query
Select Id, Name, Gender, Salary
From tblEmployees
Where Salary ]= 50000
Except
Select Id, Name, Gender, Salary
From tblEmployees
Where Salary ]= 60000
order By Name

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-except-operator.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/adsbygoogle-window.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

Get Paid Taking Pictures
Share