Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

SQL Server except operator

Suggested Videos
Part 84 - Handling deadlocks in ado.net
Part 85 - Retry logic for deadlock exceptions
Part 86 - How to find blocking queries in sql server



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. We will use the following 2 tables for this 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

Result :
sql server except operator result

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

Result : 
reverse result

You can also use Except operator on a single table. Let's use the following tblEmployees table for this example.
Employees 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

Result :
single table except result

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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.