SQL EXCEPT Vs INTERSECT Operator

In this post, we will learn about the EXCEPT and INTERSECT functions, as well as the differences between them.

SQL EXCEPT Operator

Any separate values from the query to the left of the EXCEPT operator are returned.

Those values are returned as long as the correct query does not return them.

Example: We have two table Author and Book to understand the Except operator

AID	AuthorName	Domain
A-1	Balan	Programming
A-2	Vidyavathi	Management
A-3	Hanumanthan	Administration

BID	BookName	AID
B-1	PHP And MySQL Bible	A-2
B-2	SQL Fundamentals	A-1
B-3	Teach Yourself SQL	A-1
B-4	Art Of SQL	A-1

The query to the left of the EXCEPT operator returns any distinct values from the left query that aren't found on the right query.

SELECT AID 
FROM AB_Author
EXCEPT
SELECT AID
FROM AB_Book
ORDER BY AID;

Output: The output is

aid
A-3

SQL INTERSECT Operator

Any separate values returned by both the left and right sides of the INTERSECT operator are returned.

Example: Any separate values returned by both the query on the left and right sides of the INTERSECT operator are returned by the subsequent query.

AID	AuthorName	Domain
A-1	Dharan	Management
A-2	Geetha	Web Design

BID	BookName	AID
B-1	SQL Server: The Complete Reference	A-1
B-2	SQL Visual Quickstart	A-1
B-3	Getting Started With SQL	A-1
B-4	Expert SQL Server 2005 Development	A-2

Mysql,Sql-server,Sqlite

SELECT AID 
FROM AB_Author
INTERSECT
SELECT AID
FROM AB_Book
ORDER BY AID;

In intersect we get an output which column have common to both of the Select statement. Above example we have two table Author and Book. In this two table we have same coloumn AID.

Output: The output of Intersect value is

aid
A-1
A-2

SQL DIFFERENCE BETWEEN EXCEPT And Operator

Here we will see 4 contrast between except & intersect operator:-

EXCEPT	INTERSECT
EXCEPT retrieves all distinct values from the left query and ensures that those values aren't present in the right query result.	INTERSECT is used to retrieve any distinct values in both the query and the INTERSECT operand on the left and right sides.
Two query expressions are evaluated, and the EXCEPT operator returns the difference between the results. Except for the rows that are also returned from the second set of rows, the result set will contain rows from the first set of rows.	INTERSECT result of two searches is compared and the rows that are similar to both are returned.
In distributed partitioned view definitions, EXCEPT cannot be used.	INTERSECT cannot be used in the definition of a distributed partitioned view.
When utilising SQL Server Management Studio's Graphical Showplan function to depict an EXCEPT operation, the operation appears as a left anti semi join.	When utilising SQL Server Management Studio's Graphical Showplan tool to depict an INTERSECT Operation, the operation displays as a left anti semi join.

All Tutorials