11 Methods to Merge Tables in SQL

Introduction

Ever surprise methods to get an entire image of your organization from completely different databases? SQL will help! Merging information from tables is like placing puzzle items collectively. This allows you to analyze and report on all of your info without delay. On this article, we’ll discover methods to use SQL queries like JOIN, UNION, and so on.

Overview

• Uncover methods to combine information from varied tables seamlessly.
• Be taught SQL queries like JOIN, UNION ALL, LEFT JOIN, and so on.

Let’s dive in and see methods to merge these tables utilizing SQL!

The way to Create and Populate Tables?

First, let’s create the tables and insert pattern information.

Create Staff Desk

CREATE TABLE workers (

employee_id INT,

employee_name VARCHAR(50),

department_id INT

);

Insert Information into Staff Desk

INSERT INTO workers (employee_id, employee_name, department_id) VALUES

(1, 'Alice', 1),

(2, 'Bob', 2),

(3, 'Carol', 1),

(4, 'David', 3),

(5, 'Eve', 2);

Create Departments Desk

CREATE TABLE departments (

department_id INT,

department_name VARCHAR(50)

);

Insert Information into Departments Desk

INSERT INTO departments (department_id, department_name) VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Gross sales'),

(4, 'Advertising');

Merging Tables in SQL

1. INNER JOIN

An INNER JOIN retrieves information which have matching values in each tables.

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

FROM workers e

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

End result:

2. LEFT JOIN

A LEFT JOIN retrieves all information from the left desk (workers), and the matched information from the correct desk (departments).

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

FROM workers e

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

End result:

3. RIGHT JOIN

Retrieve all information from the correct desk and matched information from the left desk.

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

Be taught Extra: SQL Interview Fast Information 2024: Ace It in Minutes!

4. FULL OUTER UNION

A FULL OUTER JOIN retrieves all information when there’s a match in both left or proper desk information

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

End result:

5. CROSS JOIN

Retrieve the Cartesian product of each tables.

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

FROM workers e

CROSS JOIN departments d;

End result:

6. SELF JOIN

Be part of a desk with itself.

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

FROM workers e1

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

End result:

7. SEMI JOIN

Retrieve rows from the left desk the place a number of matches exist in the correct desk (normally accomplished with EXISTS).

SELECT e.employee_id, e.employee_name

FROM workers e

WHERE EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

End result:

8. ANTI JOIN

Retrieve rows from the left desk the place no matches exist in the correct desk.

SELECT e.employee_id, e.employee_name

FROM workers e

WHERE NOT EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

End result:

9. UNION

UNION combines the outcome units of two or extra SELECT statements, fetching distinct rows.

SELECT employee_id, employee_name, NULL AS department_name

FROM workers

UNION

SELECT NULL AS employee_id, NULL AS employee_name, department_name

FROM departments;

End result:

10. UNION ALL

Mix the outcome units of two SELECT statements, together with duplicates.

SELECT employee_id, employee_name, department_id

FROM workers

UNION ALL

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

FROM departments;

End result:

11. INTERSECT

Retrieve the intersection of two SELECT statements.

SELECT employee_id, employee_name, department_id

FROM workers

INTERSECT

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

FROM departments;

End result:

Conclusion

By now, you’ve grow to be a grasp of merging tables in SQL! You’ve seen how SQL Queries like JOIN, INNER JOINs, LEFT JOIN, FULL OUTER JOIN, and so on, for an entire image. Bear in mind, mastering these methods unlocks the true potential of your organization’s information. Priceless insights will not be hidden in separate submitting cupboards! You possibly can mix info from workers, departments, gross sales, and extra to create complete reviews and conduct in-depth analyses.

So go forth and conquer your information! With SQL’s desk merging superpowers at your fingertips, you may remodel your information into a strong device for making knowledgeable choices.

Additionally Learn: SQL For Information Science: A Newbie Information!

Ceaselessly Requested Questions