SQL - UNION Operator

Hello there, future SQL wizards! Today, we're going to embark on an exciting journey into the world of SQL UNION operators. Don't worry if you're new to programming – I'll be your friendly guide, explaining everything step by step. So, grab a cup of coffee (or tea, if that's your preference), and let's dive in!

The SQL UNION Operator

Imagine you're planning a big party and you have two guest lists: one for your friends and another for your family. The SQL UNION operator is like combining these two lists into one master list, without any duplicates. Neat, right?

In SQL terms, the UNION operator allows us to combine the result sets of two or more SELECT statements. Here's the basic syntax:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

Remember, for UNION to work properly:

1. The number of columns in each SELECT statement must be the same.
2. The data types of corresponding columns should be compatible.
3. The column order in each SELECT statement should be the same.

Let's look at some examples to make this clearer.

UNION on a Single Field

Suppose we have two tables: fruits and vegetables. We want to create a list of all items.

SELECT name FROM fruits
UNION
SELECT name FROM vegetables;

This query will give us a single list of all fruits and vegetables, without any duplicates. If there's an 'Apple' in both tables, it will appear only once in the result.

UNION on Multiple Fields

Now, let's say we want to include prices along with the names. We can do that like this:

SELECT name, price FROM fruits
UNION
SELECT name, price FROM vegetables;

This will give us a list of all items with their prices. Remember, the order and number of columns must match in both SELECT statements.

UNION with WHERE Clause

We can also use WHERE clauses with UNION to filter our results. For example, if we only want items that cost less than $5:

SELECT name, price FROM fruits WHERE price < 5
UNION
SELECT name, price FROM vegetables WHERE price < 5;

This query will give us a list of all fruits and vegetables that cost less than $5.

UNION with ORDER BY Clause

What if we want to sort our combined list? We can use ORDER BY, but it should come after the last SELECT statement:

SELECT name, price FROM fruits
UNION
SELECT name, price FROM vegetables
ORDER BY name;

This will give us an alphabetically sorted list of all fruits and vegetables.

UNION with Aliases

Sometimes, we might want to give our columns different names in the output. We can do this using aliases:

SELECT name AS item_name, price AS item_price FROM fruits
UNION
SELECT name, price FROM vegetables;

In this case, the 'name' column will be displayed as 'item_name', and 'price' as 'item_price' in the result set.

Here's a table summarizing the UNION methods we've discussed:

Method	Description
Basic UNION	Combines result sets of two or more SELECT statements
UNION on Single Field	Combines one column from multiple tables
UNION on Multiple Fields	Combines multiple columns from multiple tables
UNION with WHERE	Filters the results before combining
UNION with ORDER BY	Sorts the combined results
UNION with Aliases	Renames columns in the output

Remember, practice makes perfect! Try writing your own UNION queries, experiment with different tables and conditions. Before you know it, you'll be UNIONing like a pro!

I hope this tutorial has helped demystify the SQL UNION operator for you. Keep coding, stay curious, and don't forget to have fun along the way. After all, every great programmer started exactly where you are now. Happy querying!

Credits: Image by storyset