September 30, 2014

Using SET Operators in SQL Server




In this article, we will see how to use SET Operators in SQL Server. We will look at UNION, UNION ALL, INTERSECT and EXCEPT operators.

To start with SET Operators, we will first create two sample tables in our Northwind database. The script is shown as below -

USE Northwind
GO

CREATE TABLE Example1
(
    Name CHAR(1)
)

CREATE TABLE Example2
(
    Name CHAR(1)
)


--INSERT FOR Example1 Table
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('B')
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('C')
INSERT INTO Example1 VALUES('D')
INSERT INTO Example1 VALUES('E')


--INSERT FOR Example2 Table
INSERT INTO Example2 VALUES('X')
INSERT INTO Example2 VALUES('A')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('C')
INSERT INTO Example2 VALUES('Z')
INSERT INTO Example2 VALUES('Y')


--SELECT ROWS FROM BOTH THE TABLES [Example1, Example2]
SELECT * FROM Example1
SELECT * FROM Example2


In the above script, we have created two tables, Example1 and Example2 and inserted some dummy data. We will now take a look at how to select the data from both the tables using the SET Operators.

UNION Operator

Let’s take an overview of our very first operator - UNION. The UNION operator combines the result of two or more queries and returns a single result set excluding the duplicate values. The following figure shows the same output -

sqlserver-set-operators
Write the following query in SQL Server Management Studio and observe the result -

union

The output of the above query is as shown here -

unionoutput

Now let’s use the two tables in the Northwind database with the name Dept and Emp.

empdept

We will write a query where we want to find all the unique cities and countries from Customers and Employees table -

union1

The output is shown below -

unionoutput1

UNIONALL Operator

However if you want to include the duplicate values of City and Country from Customers, as well as Employees, include ALL with UNION. The query is shown below -

unionall

Some rules to remember while working with UNION operator -

1. The number of columns must be same.
2. The data type of the columns must be same or implicitly convertible by database.

INTERSECT Operator

We will now take a look at INTERSECT operator. The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -

sqlserver-intersect
Let's try the query on our Example1 and Example2 table. The query is shown below -

intersect1

The output of the above INTERSECT query is shown below -

intersectoutput1

We will try the INTERSECT on Northwind database and find out all the common Cities and countries from Customers and Employees tables excluding duplicates. The query is as shown below -

intersect2

The output of the above query is as shown below -

intersectoutput2

EXCEPT Operator

We will now explore the last operator, the EXCEPT operator. The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. The following figure explains the same -

except1

The output of the above query is shown below -

exceptoutput1

We will now try the same query with our Northwind database. We will find out all the distinct cities from Employees tables which does not appear in Customers table. The query is shown below -

except2

The output of the above query is shown below -

exceptoutput2

And that’s it. In this article, we have seen how to use SET Operators in SQL Server. We have seen UNION, UNION ALL, INTERSECT and EXCEPT operator with some simple examples.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

1 Response to "Using SET Operators in SQL Server"
  1. Mara Kempleh said...
    October 13, 2014 at 8:13 PM

    Aweswome...good articel

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions