intro
Let’s discover what happens when developers use the DISTINCT and UNION SQL clauses together with each other.
Many developers know that databases are the backbone of their applications. As such, database management systems have many interesting features and quirks unique to themselves, and one of those features is SQL clauses like those facilitating CRUD operations (INSERT, SELECT, UPDATE, DELETE), those used for search operations (LIKE, BETWEEN, and others), as well as operators that allow us to filter duplicate values, like DISTINCT.
The DISTINCT SQL operator is frequently used with the SQL UNION clause. So, let’s see what happens when you use them together with each other!
What Is DISTINCT?
The DISTINCT SQL clause is a frequent friend for developers who want to select unique rows from a dataset. For example, an SQL query like so:
1
SELECT DISTINCT email FROM users
Would return all unique email addresses from a table. In other words, such an SQL query would return all email addresses, excluding duplicates. The DISTINCT clause in SQL instructs your database to ignore duplicate rows when deleting, updating, or selecting data.
The Significance of UNION
The UNION SQL clause, on the other hand, allows us to combine results from two or more SQL queries into a single result set. It implicitly removes duplicates unless your database is instructed not to do so by you using a SQL UNION ALL query.
So, in a way, UNION is similar to a DISTINCT SQL clause but at the end of the day, its purpose is a little different: UNION aims to combine results while DISTINCT aims to remove duplicates.
At the same time, UNION is frequently used together with DISTINCT SQL queries. Here, we select the distinct car makers from the cars table after counting the rows in a table (there are 6,002 rows, but only 74 unique car makers):
1
SELECT COUNT(*) FROM `cars`;
2
SELECT DISTINCT `car_make` FROM `cars`;
Execute the above queries in a feature-rich database client like DbVisualizer:
As you can see, the first query returns 6002 (Tab 1) while the second returns 74 (Tab 2).
If we had two separate tables (say, employees and contractors) and had a name column in both of them, we could issue something like:
1
SELECT Name FROM employees
2
UNION
3
SELECT Name FROM contractors;
This SQL query would combine results from both of the tables and exclude duplicates (we use UNION but not UNION ALL) in the result set. Not very difficult, is it?
DISTINCT SQL with UNION
Now when it comes to the DISTINCT SQL clause and the UNION SQL clause being used in conjunction, in many cases it’d be redundant to start with since the UNION removes duplicates by default and that’s what DISTINCT would do, too. With that being said, there are a couple of corner cases. An SQL query like so would use both DISTINCT and UNION:
1
SELECT DISTINCT Name FROM (
2
SELECT Name FROM Employees
3
UNION
4
SELECT Name FROM Contractors
5
) AS CombinedNames;
This query would use the outer DISTINCT clause to ensure that the result set has unique names in the Name column. Technically it’s redundant, but DISTINCT and UNION can be used like this too.
Consider using DISTINCT together with UNION if you want to ensure that the result set produced by your database remains unique after multiple complex queries or joins. In other words, DISTINCT and UNION may be used in unison to ensure a correct result set is returned when complex subqueries or joins are in use.
Is UNION necessary when employing the DISTINCT SQL clause? No, not at all since they would both take care of duplicates so one negates the other. But, at the same time, if you have complex queries, you may have some corner cases where such SQL queries may be of interest.
To avoid complexity, consider using SQL clients like DbVisualizer. Its extensive features will ensure that you won’t get lost in the data(base) jungle and even if you do, DbVisualizer will be the one lending you a helping hand. Don’t want to craft complex DISTINCT and UNION SQL queries? Make use of the visual query builder. Use variables and bookmarks to re-use your queries as you see fit. Drag and drop tables you want to query into the SQL client, and so much more!
DbVisualizer will help you in every step of the way. Using variables? You can even modify how you do so - isn’t that cool?
At the end of the day, no matter if you elect to use UNION, DISTINCT , or other SQL clauses to help your database thrive, an SQL client will help you stay out of many problems. Give it a spin today!
Summary
The DISTINCT SQL clause isn’t anything new and neither is the UNION clause. While both are used by developers, they have different avenues they take care of: UNION is used to combine two or more data sets into one result set while DISTINCT removes duplicates. They have their own similarities in that UNION would remove duplicates unless UNION ALL is specified, however, sometimes, when complex SQL queries are concerned, those SQL clauses can be used together.
Regardless, they’re not without their issues: those running bigger data sets will quickly notice that both SQL clauses are rather slow (that’s because your database has to perform a lot of additional work), but nonetheless, with SQL editors like DbVisualizer on the way to help you, even the biggest problems aren’t that scary.
We hope that this blog has cleared up the differences between the two, keep in touch with us by reading our blog and following us on social media, and we’ll see you in the next one.
FAQ
When to use UNION and DISTINCT?
Consider using UNION when you need to return one result set from what otherwise would comprise multiple result sets and DISTINCT when you need to remove duplicates from a table.
What’s the difference between UNION and DISTINCT in SQL?
UNION is used to combine result sets into a singular result set. DISTINCT is used to remove duplicates.
Can I use UNION and DISTINCT at the same time?
Sure, just keep in mind that both of these SQL clauses are similar in that both of them remove duplicates. To use them together at once, consider them once you’re working with a complex set of data and need to employ them within subqueries or in similar situations.
Where can I learn more about SQL clauses and databases?
To learn more about relational and NoSQL databases, follow database blogs like TheTable, read books, and attend industry conferences.
