How to Retrieve Data From Multiple Tables in SQL?

Q: 4. Is it possible to JOIN more than two tables?

Yes, Multiple JOIN Clause can be used for this SELECT column_names FROM table1 JOIN table2 ON table1.common_column = table2.common_column JOIN table3 ON table2.common_column = table3.common_column;

To prevent redundancy and preserve normalization, data in MySQL is frequently spread across several tables. We must merge data from several tables to obtain relevant information. SQL has several methods to accomplish this, mostly through JOIN and UNION operations.

In this blog, let’s detail the different techniques for retrieving data from multiple tables with examples.

Table of Contents:

Methods to Retrieve Data From Multiple Tables
Performance Comparison of Each Method
Real-world Examples
Conclusion

Methods to Retrieve Data From Multiple Tables

Before getting into the methods to retrieve data from multiple tables, let us create four tables that can be used as an example for the following methods.

1. Let us create a table called Customers and insert some values into it

-- Creating Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50)
);

-- Inserting Sample Data into Customers
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago'),
(4, 'David', 'Houston');
-- To display the table
Select * from Customers;

This is how the Customer table looks after the creation and insertion of data.

2. Let us create a table called Orders and insert some values into it

-- Creating Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
-- Inserting Sample Data into Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount) VALUES
(101, 1, '2024-01-15', 250.00),
(102, 2, '2024-02-10', 150.00),
(103, 1, '2024-03-05', 300.00),
(104, 3, '2024-04-20', 400.00);
-- To display the table
Select * from Orders;

This is how the Orders table looks after the creation and insertion of data.

3. Let us create a table called Employees and insert some values into it

-- Creating Employees Table for 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT
);
-- Inserting Sample Data into Employees
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'John', NULL),    -- CEO (No manager)
(2, 'Mike', 1),       -- Reports to John
(3, 'Sarah', 1),      -- Reports to John
(4, 'David', 2),      -- Reports to Mike
(5, 'Emma', 2);       -- Reports to Mike
-- To display the table
Select * from Employees;

This is how the Employees table looks after the creation and insertion of data.

4. Let us create a table called Product and insert some values into it

-- Creating product table for Cross Join Example
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);
-- Inserting Sample Data into Products
INSERT INTO Products (ProductID, ProductName) VALUES
(1, 'Laptop'),
(2, 'Phone'),
(3, 'Tablet');
-- To display the table
Select * from Products;

This is how the Product table looks like after the creation and insertion od data.

Method 1: Using JOINS in MySQL

Multiple JOINS in SQL can be used to return the data from multiple tables. Some of the JOINS include INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS, and SELF JOIN.

INNER JOIN in MySQL

The INNER JOIN only returns the rows with a match in both tables based on the common column.

Syntax:

SELECT columns
FROM Table1
INNER JOIN Table2 ON Table1.common_column = Table2.common_column;

Example:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate, Orders.Amount
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: The query JOINS Customer and Orders using CustomerID. Only customers who have placed orders are in the result set. Thus, customers without orders like David are excluded.

LEFT JOIN (LEFT OUTER) in MySQL

A LEFT JOIN returns all the rows from the left table and matching rows from the right table. If there is no match, then NULL values are returned for the right table

Syntax:

SELECT columns
FROM Table1
LEFT JOIN Table2 ON Table1.common_column = Table2.common_column;

Example:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate, Orders.Amount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: All customers appear in the result even if they haven’t placed any order. Customer David has no order, so a NULL value has appeared for the order details.

RIGHT JOIN (RIGHT OUTER) in MySQL

A RIGHT JOIN returns all the rows from the right table and only the matching rows from the left table. If there is no match, then a NULL value is returned for the left table.

Syntax:

SELECT columns
FROM Table1
RIGHT JOIN Table2 ON Table1.common_column = Table2.common_column;

Example:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate, Orders.Amount
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: This query ensures that all the orders are from the right table even though there is no match in the left table.

FULL JOIN (FULL OUTER) in MySQL

A FULL JOIN returns all the records from both tables. If there is no match, then NULL values are returned.

Syntax:

SELECT columns
FROM Table1
FULL JOIN Table2 ON Table1.common_column = Table2.common_column;

Example:

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderDate, Orders.Amount
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: This includes all customers and all orders. Since David has no orders, his order details are NULL.

CROSS-JOIN in MySQL

Every row from the first table is paired with every row from the second table when a CROSS JOIN yields the Cartesian product of two tables.

Syntax:

SELECT columns
FROM Table1
CROSS JOIN Table2;

Example:

SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;

Output:

Explanation: Every product is matched with every buyer. The output includes 4 * 3 = 12 rows if Customers have 4 rows and Products have 3 rows.

SELF JOIN in MySQL

One kind of JOIN operation in MySQL that joins a table with itself is called a SELF-JOIN. It enables you to group rows from the same table according to a particular criterion.

Syntax:

SELECT A.column1, B.column2
FROM Table A
JOIN Table B ON A.common_column = B.common_column;

Example:

SELECT E1.EmployeeName AS Employee, E2.EmployeeName AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

Output:

Explanation: The Employees table joins itself to find managers. John has NULL because he has no manager. Mike and Sarah report to John, and David and Emma report to Mike.

Method 2: Using UNION in MySQL

UNION combines the result sets of two queries and removes duplicates.

Syntax:

SELECT column1, column2 FROM Table1
UNION
SELECT column1, column2 FROM Table2;

Example:

SELECT CustomerID, CustomerName FROM Customers
UNION
SELECT CustomerID, 'Order Customer' FROM Orders;

Output:

Explanation: Since UNION removes duplicates, there won’t be any repeated records in the result or output.

Method 3: Using UNION ALL in MySQL

UNION ALL combines the result sets of two queries without removing duplicates.

Syntax:

SELECT column1, column2 FROM Table1
UNION ALL
SELECT column1, column2 FROM Table2;

Example:

SELECT CustomerID, CustomerName FROM Customers
UNION ALL
SELECT CustomerID, 'Order Customer' FROM Orders;

Output:

Explanation: Unlike UNION, UNION ALL does not remove duplicates. If a CustomerID appears multiple times in Orders, it will appear multiple times in the result as well.

Method 4: Using SUBQUERIES in MySQL

A subquery is a query that can be placed inside another SQL query. It can be used in SELECT Statements, WHERE conditions, and FROM Clauses.

Using SUBQUERY in SELECT Statement

A query that returns a single value and is nestled inside a SELECT statement is called a subquery in the SELECT Clause.

Syntax:

SELECT column1, column2, 
       (SELECT aggregate_function(column_name) 
        FROM AnotherTable 
        WHERE condition) AS AliasName
FROM TableName;

Example:

–To find customers and their last order date

SELECT CustomerID, CustomerName,

(SELECT MAX(OrderDate)

FROM Orders

WHERE Orders.CustomerID = Customers.CustomerID) AS LastOrderDate

FROM Customers;

Output:

Explanation: The outer query chooses CustomerID and CustomerName from customers, while the subquery obtains the most recent MAX(OrderDate) for every CustomerID. It returns NULL if a customer has no orders.

Using SUBQUERY in WHERE Clause

Based on the findings of the inner query, records in the outer query are filtered using a subquery in the WHERE clause.

Syntax:

SELECT column1, column2
FROM TableName
WHERE column_name operator (SELECT column_name FROM AnotherTable WHERE condition);

Example:

--To find customers who have placed orders
SELECT CustomerID, CustomerName FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

Output:

Explanation: The subquery retrieves the CustomerIDs from Orders and the outer query filters Customers to show only those who have placed orders.

Using SUBQUERY in FROM Clause

A subquery in the FROM Clause allows reference of the result set of the subquery as a derived table in the outer query.

Syntax:

SELECT column1, column2
FROM (SELECT column1, aggregate_function(column2) 
      FROM AnotherTable 
      GROUP BY column1) AS AliasName;

Example:

--To get the total order amount for each customer
SELECT C.CustomerID, C.CustomerName, O.TotalAmount
FROM Customers C
LEFT JOIN 
    (SELECT CustomerID, SUM(Amount) AS TotalAmount 
     FROM Orders 
     GROUP BY CustomerID) O
ON C.CustomerID = O.CustomerID;

Output:

Explanation: The subquery in the FROM Clause calculates the total order amount per customer. The main query performs a LEFT JOIN to include all customers.

Method 5: Using JOINS with GROUP BY in MySQL

When you combine a JOIN with GROUP BY Clause, the query first joins the table and then groups the result set by one or more columns.

Syntax:

SELECT column1, column2, aggregate_function(column3)
FROM Table1
JOIN Table2 ON Table1.common_column = Table2.common_column
GROUP BY column1, column2;

Example:

--To get the total order amount per customer 
SELECT Customers.CustomerID, Customers.CustomerName, SUM(Orders.Amount) AS TotalAmount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CustomerName;

Output:

Explanation: Since Alice placed several orders, the total is equal to the sum of his orders.

Bob and Charlie also calculated their total amounts.

Performance Comparison of Each Method

Method	Use Case	Pros	Cons
JOINS	Combines information from two or more related tables using a common field.	Can join multiple tables	Performance decreases with large datasets
UNION	Combine and remove duplicates from two or more queries’ result sets.	Return distinct rows only	Performance decreases due to the duplicate elimination
UNION ALL	Combine the sets of results from two or more queries, taking into account duplicates.	Faster than UNION because it doesn’t remove duplicates	Performance decreases with large datasets
JOINS with GROUP BY	This can be used when aggregating the data from multiple tables	Outstanding for intricate reporting	If the index isn’t done properly, then there will be a decrease in performance
Subqueries	Usually, the WHERE Clause is used with a query inside another query.	Performs well for dynamic filtering	This may result in a performance decrease if the subqueries are not optimized

Real-world Examples

Case 1. E-commerce Order Management

A business wants to obtain the client’s information and most recent order.

Example:

-- Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    Email VARCHAR(100)
);

INSERT INTO Customers VALUES 
(1, 'Alice', '[email protected]'),
(2, 'Bob', '[email protected]'),
(3, 'Charlie', '[email protected]');

-- Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

INSERT INTO Orders VALUES 
(101, 1, '2024-02-10', 500.00),
(102, 2, '2024-02-12', 150.00),
(103, 1, '2024-03-05', 50.00),  -- Latest Order for Alice
(104, 3, '2024-04-20', 400.00);

--To get customers with their latest order
SELECT C.CustomerID, C.CustomerName, O.OrderID, O.OrderDate, O.Amount
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.OrderDate = (SELECT MAX(OrderDate) FROM Orders O2 WHERE O2.CustomerID = C.CustomerID);

Output:

Explanation: Using CustomerID, the JOIN joins the Customers and Orders tables. For every client, the subquery retrieves the most recent MAX(OrderDate) order.

Case 2. HR System

An HR system needs to retrieve the current and previous salary details of the employee.

Example:

-- Employees Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Department VARCHAR(50)
);

INSERT INTO Employees VALUES 
(1, 'John Doe', 'IT'),
(2, 'Jane Smith', 'Finance'),
(3, 'Michael Brown', 'HR');

-- Salaries Table
CREATE TABLE Salaries (
    SalaryID INT PRIMARY KEY,
    EmployeeID INT,
    Salary DECIMAL(10,2),
    EffectiveDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

INSERT INTO Salaries VALUES 
(201, 1, 50000, '2023-01-01'),
(202, 1, 55000, '2024-01-01'),  -- Latest Salary for John
(203, 2, 60000, '2023-02-01'),
(204, 2, 62000, '2024-02-01'),  -- Latest Salary for Jane
(205, 3, 45000, '2023-03-01');

--Retrieve current and previous salaries of employees
SELECT EmployeeID, EmployeeName, Salary, EffectiveDate, 'Current Salary' AS Status
FROM Employees E
JOIN Salaries S ON E.EmployeeID = S.EmployeeID
WHERE EffectiveDate = (SELECT MAX(EffectiveDate) FROM Salaries S2 WHERE S2.EmployeeID = E.EmployeeID)

UNION 

SELECT EmployeeID, EmployeeName, Salary, EffectiveDate, 'Previous Salary' AS Status
FROM Employees E
JOIN Salaries S ON E.EmployeeID = S.EmployeeID
WHERE EffectiveDate < (SELECT MAX(EffectiveDate) FROM Salaries S2 WHERE S2.EmployeeID = E.EmployeeID);

Output:

Explanation: The SELECT Statement from the first part of the query fetches the latest salary and the UNION combines it with the previous salary. UNION ensures unique results.

Case 3. E-learning Platform

An online learning platform seeks to determine each student’s overall grade across all disciplines.

Example:

-- Students Table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
);

INSERT INTO Students VALUES 
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Scores Table
CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY,
    StudentID INT,
    Subject VARCHAR(50),
    Marks INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

INSERT INTO Scores VALUES 
(101, 1, 'Math', 90),
(102, 1, 'Science', 85),
(103, 2, 'Math', 80),
(104, 2, 'Science', 75),
(105, 3, 'Math', 95),
(106, 3, 'Science', 90);

SELECT S.StudentID, S.StudentName, SUM(Sc.Marks) AS TotalMarks
FROM Students S
JOIN Scores Sc ON S.StudentID = Sc.StudentID
GROUP BY S.StudentID, S.StudentName;

Output:

Explanation: Based on StudentID, the JOIN table joins the Students and Scored tables. Each student appears once thanks to the GROUP BY Clause, which groups the results by StudentID.

Conclusion

Effective database administration requires the ability to retrieve data from numerous tables in SQL. Depending on the performance requirements, distinct advantages are provided by methods like as JOINs, UNION, UNION ALL, SUBQUERIES, and GROUP BY with JOINs. While UNION makes it easy to aggregate the results of many searches, JOINs are more suited for merging similar data. In this blog, you’ve explored different methods to return data from multiple tables in MySQL.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

SQL Query to Retrieve Data From Multiple Tables – FAQs

1. What is the most effective way to get information from several tables?

When tables are connected by a common key, JOINs are the most effective technique.

2. When is UNION a better option than JOIN?

Use UNION to aggregate results from many queries with the same structure rather than linking related tables.

3. What distinguishes UNION from UNION ALL?

UNION ALL is faster since it includes all rows, whereas UNION eliminates duplicates.

4. Is it possible to JOIN more than two tables?

Yes, Multiple JOIN Clause can be used for this

SELECT column_names

FROM table1

JOIN table2 ON table1.common_column = table2.common_column

JOIN table3 ON table2.common_column = table3.common_column;

5. Why is it necessary to use GROUP BY with JOIN in SQL?

GROUP BY with JOIN is used to integrate data from many tables.