SQL: Intersect, Except and Union

Let's say we have Table A with ID Column  and its values are (1,2,3,4,5) and Table B with its ID Column and values (3,4,5,6,7).

Let's say we want to write different queries giving us something like this:
Find me all the value which common in both table? (3,4,5)

Find me all unique value in both table? (1,2,3,4,5,6,7)

Find me all values which are unique in table A and that are also not in Table B (1,2)

Find me all values which are unique in both Table (1,2,6,7)

Fire your SQL Server and let's create these table and insert data in them and see how it work.


CREATE Table TableA
(ID int);
GO
INSERT INTO TableA VALUES (1),(2),(3),(4),(5);
GO
CREATE Table TableB
(ID int);
GO
INSERT INTO TableB VALUES (3),(4),(5),(6),(7);

Find me all the value which common in both table? (3,4,5)

To find values in both table, take a look at the picture above. The question ask: what are the values which are common to both table? What word comes to your mind? Intersect? right.. there is keyword "INTERSECT" in sql language.

Try this


SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;

Result

ID

3

4

5

Find me all unique value in both table? (1,2,3,4,5,6,7)

To find value which are unique in both table, take a look at picture above again. think about it. programming and database is all about logic.

Select * from Table A

Union

Select * from Table B

Result

ID

1

2

3

4

5

6

7

If you do UNION ALL, we will get repeat value of common number in both table like this.

ID

1

2

3

4

5

3

4

5

6

7

Find me all values which are unique in table A and that are also not in Table B (1,2)

In this case we have to use EXCEPT keyword.

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB;

Result

ID

1

2

Find me all values which are unique in both Table (1,2,6,7)

For this query we have to really think hard.. think about UNION and EXCEPT combining somehow?

SELECT * FROM TableA

Union 

Select * FROM TableB

EXCEPT

SELECT * FROM TableA

INTERSECT

SELECT * FROM TableB;

Result

ID

1

2

6

7

Hope this help you.

Few things to remember about these queries are:

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

1.   The number and the order of the columns must be the same in all queries.

2.   The data types must be compatible.

IF you don't follow this rule, will will get error message.

Cheers!!!