11 Ways to Merge Tables in SQL

Introduction

Ever wonder how to get a complete picture of your company from different databases? SQL can help! Merging data from tables is like putting puzzle pieces together. This lets you analyze and report on all your information at once. In this article, we’ll explore how to use SQL queries like JOIN, UNION, etc.

Overview

• Discover how to integrate data from various tables seamlessly.
• Learn SQL queries like JOIN, UNION ALL, LEFT JOIN, etc.

Let’s dive in and see how to merge these tables using SQL!

How to Create and Populate Tables?

First, let’s create the tables and insert sample data.

Create Employees Table

CREATE TABLE employees (

employee_id INT,

employee_name VARCHAR(50),

department_id INT

);

Insert Data into Employees Table

INSERT INTO employees (employee_id, employee_name, department_id) VALUES

(1, 'Alice', 1),

(2, 'Bob', 2),

(3, 'Carol', 1),

(4, 'David', 3),

(5, 'Eve', 2);

Create Departments Table

CREATE TABLE departments (

department_id INT,

department_name VARCHAR(50)

);

Insert Data into Departments Table

INSERT INTO departments (department_id, department_name) VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Sales'),

(4, 'Marketing');

Merging Tables in SQL

1. INNER JOIN

An INNER JOIN retrieves records that have matching values in both tables.

SELECT e.employee_id, e.employee_name, d.department_name

FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id;

Result:

2. LEFT JOIN

A LEFT JOIN retrieves all records from the left table (employees), and the matched records from the right table (departments).

SELECT e.employee_id, e.employee_name, d.department_name

FROM employees e

LEFT JOIN departments d ON e.department_id = d.department_id;

Result:

3. RIGHT JOIN

Retrieve all records from the right table and matched records from the left table.

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Learn More: SQL Interview Quick Guide 2024: Ace It in Minutes!

4. FULL OUTER UNION

A FULL OUTER JOIN retrieves all records when there is a match in either left or right table records

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

Result:

5. CROSS JOIN

Retrieve the Cartesian product of both tables.

SELECT e.employee_id, e.employee_name, d.department_name

FROM employees e

CROSS JOIN departments d;

Result:

6. SELF JOIN

Join a table with itself.

SELECT e1.employee_id, e1.employee_name, e2.employee_name AS manager_name

FROM employees e1

LEFT JOIN employees e2 ON e1.department_id = e2.department_id AND e1.employee_id != e2.employee_id;

Result:

7. SEMI JOIN

Retrieve rows from the left table where one or more matches exist in the right table (usually done with EXISTS).

SELECT e.employee_id, e.employee_name

FROM employees e

WHERE EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

Result:

8. ANTI JOIN

Retrieve rows from the left table where no matches exist in the right table.

SELECT e.employee_id, e.employee_name

FROM employees e

WHERE NOT EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

Result:

9. UNION

UNION combines the result sets of two or more SELECT statements, fetching distinct rows.

SELECT employee_id, employee_name, NULL AS department_name

FROM employees

UNION

SELECT NULL AS employee_id, NULL AS employee_name, department_name

FROM departments;

Result:

10. UNION ALL

Combine the result sets of two SELECT statements, including duplicates.

SELECT employee_id, employee_name, department_id

FROM employees

UNION ALL

SELECT department_id AS employee_id, department_name AS employee_name, NULL AS department_id

FROM departments;

Result:

11. INTERSECT

Retrieve the intersection of two SELECT statements.

SELECT employee_id, employee_name, department_id

FROM employees

INTERSECT

SELECT department_id AS employee_id, department_name AS employee_name, NULL AS department_id

FROM departments;

Result:

Conclusion

By now, you’ve become a master of merging tables in SQL! You’ve seen how SQL Queries like JOIN, INNER JOINs, LEFT JOIN, FULL OUTER JOIN, etc, for a complete picture. Remember, mastering these techniques unlocks the true potential of your company’s data. Valuable insights will no longer be hidden in separate filing cabinets! You can combine information from employees, departments, sales, and more to create comprehensive reports and conduct in-depth analyses.

So go forth and conquer your data! With SQL’s table merging superpowers at your fingertips, you can transform your data into a powerful tool for making informed decisions.

Also Read: SQL For Data Science: A Beginner Guide!

Frequently Asked Questions

Abhishek Kumar

Hello, I'm Abhishek, a Data Engineer Trainee at Analytics Vidhya. I'm passionate about data engineering and video games I have experience in Apache Hadoop, AWS, and SQL,and I keep on exploring their intricacies and optimizing data workflows 

:)