In this puzzle, we’re going to learn how to write a SQL UNION
without using 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. But can we do without a SQL UNION
?
Yet a great way to know and understand SQL is to explore alternative methods. Today, we’ll chuck the SQL UNION
operator out the door, and seek an alternative method to arrive at the same result.
Are you up to the challenge?
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 Union Two Results Using No UNION
Given two lists of numbers: A
and B
.
Select all odd numbers ordered from highest to lowest into a single result using one statement WITHOUT Using any SET
operators such as UNION
, EXCEPT
, or INTERSECT
.
Here is a script you can use to create the tables.
Answer
Here is the solution I came up with today:
WITH cteNumber (Number)
AS
(
SELECT ISNULL(A.Number, B.Number)
FROM @A A
FULL OUTER JOIN @B B
ON A.Number = B.Number
)
SELECT Number
FROM cteNumber
WHERE Number % 2 = 1
ORDER BY Number DESC
There are several elements I’ll go over; however, let’s start with the one that really makes this tick – the FULL OUTER JOIN
.
A FULL OUTER JOIN
combines a LEFT
and RIGHT OUTER JOIN
into one operation. This allow us to return every row from both tables. Where the join
condition isn’t met, NULL
s are returned.
Here is an example:
SELECT A.Number ANumber, B.Number BNumber
FROM @A A
FULL OUTER JOIN @B B
ON A.Number = B.Number
And the results show rows exclusive to their respective table, as well as those in common.
If you look closely, you’ll see that we have the beginnings of an UNION
operation. All that we need to do is “combine” the ANumber
and BNumber
column values together for each row.
Due to this, we use the ISNULL function. The function ISNULL(value, replacementValue)
returns a value, and if value IS NULL
, then replacementValue
.
Thus:
ISNULL(ANumber, BNumber)
returns ANumber
, and if ANumber IS NULL
, then BNumber
.
Given this, we can modify our query to:
SELECT ISNULL(A.Number, B.Number)
FROM @A A
FULL OUTER JOIN @B B
ON A.Number = B.Number
Which now returns the union of Number for tables A
and B
.
To finish the solution, I wrapped this FULL OUTER JOIN
in a CTE (Common Table Expression). Then, queried the results, checked for odd numbers, and sorted in descending order.
WITH cteNumber (Number)
AS
(
SELECT ISNULL(A.Number, B.Number)
FROM @A A
FULL OUTER JOIN @B B
ON A.Number = B.Number
)
SELECT Number
FROM cteNumber
WHERE Number % 2 = 1
ORDER BY Number DESC
Keep in mind that % operator
returns the modulo (remainder) of a division. Any number when divided by 2 whose remainder is 1, is odd.
Also, to sort number from highest to lowest, use the DESC
(descending) qualifier.
Here are the results:
Alternative Solution
If you don’t like common table expressions, or your version of SQL doesn’t support them, then you’ll find this answer a bit more traditional:
SELECT ISNULL(A.Number, B.Number) as Number
FROM @A A
FULL OUTER JOIN @B B
ON A.Number = B.Number
WHERE A.Number % 2 = 1 OR
B.Number % 2 = 1
ORDER BY Number
If you’re like me, you’re most likely looking at the WHERE
clause and wondering why we’re using OR
rather than AND
.
At first, I thought AND
would be the way to go, after all, we only want to keep odd values from both tables. Yet, once I thought about it, I realized some of the columns return NULL
, and NULL % 2
is NULL
, so using AND
would limit any rows exclusive to one table.
I then thought about OR
. With this Boolean operator, we’re ensuring we get the odd values, even if the other columns is NULL
, and in the case both columns aren’t null
, they are equal, due to the join
operator, so OR
in this case is OK!
OK – So how would you solve this puzzle? Place your answer in the comments!
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/