Click here to Skip to main content
15,868,164 members
Articles / Database Development / SQL Server

SQL Server and Set Operations

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
30 Dec 2017MIT4 min read 7.3K   2  
SQL set operations

In this puzzle, we’re going to talk about SQL set operations. Set operations allow us to compare rows from two or more tables to arrive at a result. For several classes of problems, it is much easier to use a set operation, than join data.

Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post your answer in the comments so we can all learn from one another.

SQL Set Operations Puzzle – Part I

Given two lists of numbers: A and B:

  1. Select all odd numbers ordered from highest to lowest
  2. Select all numbers from A that aren’t in B
  3. Select all numbers common to A and B

*** Don’t use joins or subqueries to answer these questions.

Answer

Before we get into the answers, I think it is important for you to know the difference between a join and set operation.

Though both concepts are used to combine data from multiple tables, join combine columns from separate tables; whereas, set operations combine rows from separate tables.

For instance, union is used to combine rows from two or more tables into a single result. It is difficult to do this using JOIN.

Today to answer the question, we are going to use UNION, EXCEPT, and INTERSECT respectively. Below is a visual summary of these operations:

Set Operations - Visual Guide Union, Intersect, Except

If you’re not familiar with set operations, I would recommend reading this article on UNION, INTERSECT, and EXCEPT.

Let’s answer the first question.

Use a Set Operation to Select All Odd Numbers Ordered from Highest to Lowest

To do this, we’ll combine the results of two queries, one from table A the other from B. In both cases, we want only odd numbers. These are numbers, that when divided by 2, have a remainder of one.

Once we have a combined set, we’ll order the results.

Here are the tools we’ll need to use:

  • % – This is the modulus operator we'll use to get the remainder when we divide number by 2. Numbers with a remainder of 1 are odd.
  • UNION – This set operator allows us to combine rows from one result set with another if each result has the same number of columns and datatypes.
  • ORDER BY DESC – We use the ORDER BY clause to sort values.

The query to get odd number from table A is:

SQL
SELECT   Number
FROM     @A
WHERE    Number % 2 = 1

The modulo operator (%) may seem strange, but is it really handy!

We repeat the same query for table B and combine them using UNION ALL and ORDER the results.

SQL
SELECT   Number
FROM     @A
WHERE    Number % 2 = 1
UNION ALL
SELECT   Number
FROM     @B
WHERE    Number % 2 = 1
ORDER BY Number DESC

Notice that I used UNION ALL. This returns every row for the union-ed results, even duplicate values. The above query returns 20 rows, below are some of the results:

Set Operators - UNION ALL

If I ran the query without ALL, then only 15 rows are returned; try it!

If you want to learn more about the UNION operator, I would recommend this article on UNIONS.

Use a Set Operator to Select All Numbers From A that aren’t in B

To answer this question, we’ll use the EXCEPT operator. It will limit our result to only those rows that are exclusive to one table.

Set Operators - Except Operator

For our purposes, think of table A as the orange table, and B as the blue. When we do A EXCEPT B, we are going to return the orange crescent. This represents rows exclusive to A.

Here is the query to do so:

SQL
SELECT   Number
FROM     @A
EXCEPT
SELECT   Number
FROM     @B

In this example, we retrieve all rows from A, they are then compared to all rows from B, and only those rows found in A, but Not B are returned in the result:

Set Operators - Except Operator REsults.

I’m sure some of you were thinking of other solutions, such as those that use a JOIN or Subquery.

Here is the solution as a subquery:

SQL
SELECT   Number
FROM     @A
WHERE    Number NOT IN (SELECT Number
                          FROM @B)

I show you this, so you can understand that there are many ways to solve the same problem!

Use a Set Operator to Select All Numbers Common to A and B

To find all numbers common between two results, we can use the INTERSECT operator. These are the numbers which “overlap” in our diagram.

Though this was the last question in the puzzle, I think it is the easiest to answer!

SQL
SELECT   Number
FROM     @A
INTERSECT
SELECT   Number
FROM     @B

Can you think of another way of showing numbers common to both tables? What about an INNER JOIN?

Here is another way to get the same result:

SQL
SELECT   A.Number
FROM     @A A
         INNER JOIN @B B
         ON A.Number = B.Number

So how did you answer the puzzle questions? I would really like to know. Put your answers in the comments!

Also, if you have any ideas for more puzzles, let me know.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --