SQL - UNION vs JOIN: A Comprehensive Guide for Beginners

Hello there, aspiring SQL enthusiasts! I'm thrilled to be your guide on this exciting journey through the world of SQL. Today, we're going to unravel the mysteries of UNION and JOIN, two powerful SQL operations that might seem confusing at first but will soon become your best friends in data manipulation. So, grab a cup of coffee, get comfortable, and let's dive in!

Understanding the Basics

Before we delve into the intricacies of UNION and JOIN, let's start with a simple analogy. Imagine you're organizing a party, and you have two guest lists: one for your friends and another for your family. Now, you have two options:

1. Combine both lists into one big list (that's like a UNION)
2. Find out which of your friends are also related to you (that's like a JOIN)

Sounds interesting? Great! Let's explore each of these operations in detail.

Working of UNION

What is UNION?

UNION is like a master list-maker in SQL. It takes two or more SELECT statements and combines their results into a single result set. It's perfect when you want to merge data from different tables that have a similar structure.

UNION in Action

Let's say we have two tables: employees_usa and employees_uk. We want to get a list of all employees, regardless of their location.

SELECT first_name, last_name, country
FROM employees_usa
UNION
SELECT first_name, last_name, country
FROM employees_uk;

This query will give us a combined list of all employees from both the USA and UK offices. Cool, right?

UNION ALL: The Inclusive Cousin

Now, what if we want to include duplicate entries? That's where UNION ALL comes in handy.

SELECT product_name, category
FROM products_2021
UNION ALL
SELECT product_name, category
FROM products_2022;

This query will list all products from both 2021 and 2022, including duplicates if a product was available in both years.

Key Points to Remember

1. UNION removes duplicate rows by default
2. UNION ALL keeps all rows, including duplicates
3. The number and order of columns must be the same in all SELECT statements
4. Data types of corresponding columns should be compatible

Working of JOIN

What is JOIN?

If UNION is a list-maker, JOIN is like a matchmaker. It combines rows from two or more tables based on a related column between them. It's perfect when you want to retrieve data that's spread across multiple tables.

Types of JOINs

Let's break down the different types of JOINs using a fun analogy. Imagine you're at a dance party:

1. INNER JOIN: Only pairs up dancers who both know the same dance
2. LEFT JOIN: Pairs up all dancers from the left side, even if they don't have a partner
3. RIGHT JOIN: Pairs up all dancers from the right side, even if they don't have a partner
4. FULL JOIN: Pairs up all dancers, even if some end up dancing alone

JOIN in Action

Let's see how these JOINs work in real SQL queries:

INNER JOIN

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

This query will show us all customers who have placed orders, along with their order dates.

LEFT JOIN

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

This query will list all employees, even those not assigned to any department.

RIGHT JOIN

SELECT products.name, categories.category_name
FROM products
RIGHT JOIN categories ON products.category_id = categories.id;

This query will show all categories, even those without any products.

FULL JOIN

SELECT students.name, courses.course_name
FROM students
FULL JOIN enrollments ON students.id = enrollments.student_id
FULL JOIN courses ON enrollments.course_id = courses.id;

This query will list all students and all courses, even if some students haven't enrolled in any course or some courses have no students.

UNION vs JOIN: The Showdown

Now that we've explored both UNION and JOIN, let's compare them side by side:

Aspect	UNION	JOIN
Purpose	Combines rows from similar tables	Combines columns from related tables
Result	Vertical combination (more rows)	Horizontal combination (more columns)
Table Structure	Must have same number of columns	Can have different number of columns
Duplicate Handling	Removes duplicates (unless UNION ALL)	Keeps all matched rows
Performance	Generally faster for large datasets	Can be slower for complex joins
Use Case	Combining similar data from different sources	Retrieving related data across tables

Conclusion: Choosing Between UNION and JOIN

So, when should you use UNION, and when should you opt for JOIN? Here's a simple rule of thumb:

• Use UNION when you want to combine similar data from different tables into a single list.
• Use JOIN when you want to retrieve related data spread across multiple tables.

Remember, like choosing the right tool for a job, selecting between UNION and JOIN depends on your specific data needs. With practice, you'll develop an intuition for which one to use in different scenarios.

I hope this guide has helped demystify UNION and JOIN for you. Keep practicing, stay curious, and before you know it, you'll be dancing through SQL queries like a pro! Happy coding!

Credits: Image by storyset