In the realm of Structured Query Language (SQL), the UNION operator plays a pivotal role in combining the result sets of two or more SELECT statements. When applied correctly, SQL UNION can simplify complex queries and enhance data analysis. Let's delve into the world of SQL UNION, starting with the basics and then exploring its intricacies with examples from W3Schools.
Before we dive into the details, it's essential to understand that the UNION operator is used to combine the result sets of two or more SELECT statements. It does so by appending the result set of the second SELECT, to the result set of the first SELECT. However, for this to work seamlessly, both SELECT statements must have the same number of columns. Moreover, the columns should have similar data types in each SELECT statement.
SQL UNION Syntax and Basic Examples
To kickstart our learning, let's look at the basic syntax of the UNION operator and explore a couple of simple examples from W3Schools.
The basic syntax of the SQL UNION operator is as follows:
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
Here's a simple example from W3Schools that showcases the basic UNION syntax. We'll combine the 'FirstName' and 'LastName' columns from two different tables, 'Employees' and 'Customers':
SELECT FirstName FROM Employees UNION SELECT FirstName FROM Customers;
The result of this query will display all the unique 'FirstName' values from both the 'Employees' and 'Customers' tables.
UNION with SYSTEM and W3SCHOOLS TABLES
Now, let's explore a more practical example using W3Schools' SYSTEM and W3SCHOOLS tables. We'll retrieve information about Harrys who are either customers or employees:
SELECT FirstName, LastName, Age FROM Employees WHERE FirstName='Harry' UNION SELECT FirstName, LastName, Age FROM Customers WHERE FirstName='Harry';
In this example, the UNION operator combines the result sets of the two SELECT statements, displaying the unique 'FirstName', 'LastName', and 'Age' data of all Harrys in the Employees and Customers tables.
UNION with DISTINCT
While UNION automatically removes duplicate rows, it's sometimes necessary to use the DISTINCT keyword to ensure that only unique values are returned:
SELECT FirstName, LastName FROM Employees WHERE Salary > 10000 UNION DISTINCT SELECT FirstName, LastName FROM Customers;
In this example, the DISTINCT keyword is used to ensure that only the unique 'FirstName' and 'LastName' values are returned from the Employees and Customers tables, regardless of their salary.
SQL UNION ALL: Combining Result Sets with Duplicates
So far, we've explored the basic SQL UNION operator, which by default removes duplicate rows. However, there might be situations where you'd like to retain these duplicates. This is where the SQL UNION ALL operator comes into play.
The UNION ALL operator functions similarly to the UNION operator but retains duplicate rows:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
UNION ALL with w3schools CARS TABLE
Let's see an example using the CARS table from W3Schools. We'll fetch data about cars with high prices and high mileage, while preserving duplicates:
SELECT Car, Price FROM CARS WHERE Price > 100000 UNION ALL SELECT Car, Mileage FROM CARS WHERE Mileage > 50000;
The result of this query will display all 'Car' values from the CARS table that have a 'Price' greater than 100000 and 'Mileage' greater than 50000, retaining any duplicated 'Car' entries.
UNION ALL with ORDER BY Clause
You can also use the ORDER BY clause in conjunction with the UNION ALL operator to sort the combined result set. Here's an example that combines data from the CARS and COMPUTERS tables while ordering the result by 'Car' or 'Model' in ascending (ASC) order:
SELECT Car, Price FROM CARS ORDER BY Car UNION ALL SELECT Model, Price FROM COMPUTERS ORDER BY Model;
The result of this query will display the combined 'Car' and 'Model' data from the CARS and COMPUTERS tables, sorted in ascending order.
With that, we've explored the fundamental aspects of the SQL UNION and UNION ALL operators. By mastering these concepts, you'll be well-equipped to handle complex queries and enhance your data analysis skills. Keep practicing and expanding your knowledge to become a SQL expert!