What are SET operators in SQL?
A simple guide to SET operators.
If you've ever played around with SQL, you've likely come across the idea of combining result sets from different queries. You might have thought, "Is there a clean way to do this?" Well, you're in luck—SQL's SET operators are designed to handle exactly that. These operators let you combine two or more result sets, making your data analysis life much easier. Let's break it down in simple terms.
What Exactly are SET Operators?
Think of SET operators as tools that help you merge data from different queries into a single, unified result set. Some common SET operators are:
UNION: Combines results but removes duplicates.
UNION ALL: Combines results, including duplicates.
EXCEPT: Returns rows from the first query that aren’t in the second.
INTERSECT: Returns rows common to both queries.
Each of these has its unique use case, but they all follow the same rules when it comes to merging queries.
The Two Must-Follow Rules for Using SET Operators
Before you can jump into combining result sets, there are two big rules you need to follow:
The Number and order of Columns must match
Every query you're combining should return the same number of columns, and those columns should be in the same order.The Data Types must be Compatible
Not all data types get along. If you're trying to combine two result sets, the columns you're merging should have compatible data types.
What Are Compatible Data Types?
Let's say you have a column with the INT data type and another column with the SMALLINT data type. Those are compatible; they both represent integer numbers, just with different storage sizes.
Now, what happens if you try to combine a column with a DATE data type and a column with a MONEY data type? This will result in an error message. As both data types are not compatible. After all, what does a date have to do with money, right?
Here is a diagram from the Microsoft documentation demonstrating data type compatibility in SQL Server.
Putting It All Together
SET operators are fantastic for combining query results when used correctly. Whether you're trying to remove duplicates with UNION or find common records using INTERSECT, just keep those two golden rules in mind: matching columns and compatible data types.