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!!!

SubQuery and JOIN: A beginner concept

Microsoft define subquery  as "A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. "

Let's look at an example of this subquery and how can we avoid subquery all together by using JOIN functionality.

For this example I am going to use AdventureWork database.

We are going to look at some sales table and show you how you can avoid subquery by using JOIN.

Lets say we want to look at SalesOrderID, SalesOrderDate and Maximum UnitPrice from our sales table
(SalesOrderDetail and SalesOrderHeader ).

So first write down what we want.

--Select SalesOrderID, SalesOrderDate and Maximum UnitPrice from Table SalesOrderDetail and Table SalesOrderHeader 


So let's go ahead and write on query using subquery



SELECT  Ord.SalesOrderID
,Ord.OrderDate
,(
SELECT max(OrdDet.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderDetail OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID
) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord;


Now let's try writing down similar query which will give the same result.


SELECT  Ord.SalesOrderID
,Ord.OrderDate
,max(OrdDet.UnitPrice) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord
 JOIN AdventureWorks.Sales.SalesOrderDetail OrdDet ON Ord.SalesOrderID = OrdDet.SalesOrderID
GROUP BY Ord.SalesOrderID
,Ord.OrderDate;

If possible try avoiding use of subquery in your T-SQL.

Here is execution plan for the 2 queries.