Mastering Data Retrieval from Multiple SQL Tables

In order to avoid duplication and maintain normalization, information within MySQL is often distributed among various tables. To gather essential data, it is necessary to combine information from different tables. SQL provides numerous techniques to achieve this, primarily through the use of JOIN and UNION operations.

In this article, we will elaborate on the various strategies for fetching data from multiple tables, complete with examples.

Contents:

Approaches for Retrieving Data From Multiple Tables

Prior to delving into the methods for fetching data from numerous tables, let us create four tables that will serve as examples for the upcoming techniques.

1. We will start with a table named Customers and insert a few entries 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 Customers table appears after its creation and data insertion.

2. Next, we will establish a table named Orders and add some entries 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 the final appearance of the Orders table following its creation and data input.

3. Now we will set up a table named Employees and populate it with some entries.

-- Creating Employees Table 
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 appears after it has been created and data has been inserted.

4. Finally, we will create a table called Products and insert some entries into it as well.

-- Creating Products 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 appears after it has been established and data included.

Technique 1: Employing JOINS in MySQL

Utilizing multiple JOINS in SQL

“`html

can be utilized to retrieve data from various tables. Some of the JOIN types comprise INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS, and SELF JOIN.

INNER JOIN in MySQL

The INNER JOIN exclusively retrieves the rows that have a match in both tables according to the shared 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 connects Customer and Orders using CustomerID. Consequently, only those customers who have made orders are included in the result set. Hence, customers without orders like David are omitted.

LEFT JOIN (LEFT OUTER) in MySQL

The LEFT JOIN retrieves all rows from the left table along with the corresponding rows from the right table. If there’s no corresponding row, NULL values are presented 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 show up in the result even if they haven’t placed any orders. Customer David has no orders, which results in a NULL value for the order details.

RIGHT JOIN (RIGHT OUTER) in MySQL

A RIGHT JOIN returns all rows from the right table along with the matching rows from the left table. If there is no corresponding row, 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 guarantees that all orders originate from the right table even in the absence of matches in the left table.

FULL JOIN (FULL OUTER) in MySQL

A FULL JOIN retrieves all records from both tables. Should there be no match, NULL values will be produced.

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 every customer along with every order. Since David has no orders, his order details appear as NULL.

CROSS-JOIN in MySQL

A CROSS JOIN matches each row from the first table with every row from the second table, yielding the Cartesian product of the 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 paired with every customer. The output will comprise 4 * 3 = 12 rows if there are 4 customers and 3 products.

SELF JOIN in MySQL

A SELF-JOIN is a type of JOIN operation in MySQL that merges a table with itself, enabling you to categorize rows from the same table based on specific criteria.

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 connects to itself to establish the hierarchy of managers. John exhibits NULL due to the absence of a manager. Mike and Sarah report to John, while David and Emma report to Mike.

Method 2: Utilizing UNION in MySQL

UNION merges the result sets of two queries and eliminates 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:

“““html
Using UNION in MySQL Output

Clarification: As UNION eliminates duplicates, there will be no recurring records in the output.

Approach 3: Utilizing UNION ALL in MySQL

UNION ALL merges the result sets of two queries without filtering out duplicates.

Syntax:

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

Illustration:

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

Results:

Clarification: In contrast to UNION, UNION ALL does not discard duplicates. If a CustomerID appears several times in Orders, it will also appear multiple times in the result.

Approach 4: Implementing SUBQUERIES in MySQL

A subquery is a query that can be incorporated within another SQL query. It can be utilized in SELECT statements, WHERE clauses, and FROM statements.

Employing SUBQUERY in SELECT Statement

A query that produces a single value and is nested within a SELECT statement is referred to as a subquery in the SELECT clause.

Syntax:

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

Illustration:

–To identify customers and their last order date

SELECT CustomerID, CustomerName,

(SELECT MAX(OrderDate)

FROM Orders

WHERE Orders.CustomerID = Customers.CustomerID) AS LastOrderDate

FROM Customers;

Results:

Clarification: The outer query selects CustomerID and CustomerName from customers, while the subquery retrieves the most recent MAX(OrderDate) for each CustomerID. It returns NULL if a customer has no orders.

Employed SUBQUERY in WHERE Clause

Records in the outer query are filtered based on the results of the inner query using a subquery in the WHERE clause.

Syntax:

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

Illustration:

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

Results:

Clarification: The subquery fetches the CustomerIDs from Orders while the outer query filters Customers to display solely those who have made orders.

Utilizing SUBQUERY in FROM Clause

A subquery within the FROM clause permits referencing 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;

Illustration:

--To obtain the total order value 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;

Results:

Clarification: The subquery in the FROM clause computes the total order value per customer. The primary query executes a LEFT JOIN to encompass all customers.

Approach 5: Utilizing JOINS with GROUP BY in MySQL

When combining a JOIN with a GROUP BY clause, the query first links the tables and subsequently groups the resulting dataset 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;

Illustration:

--To ascertain the total order value for each 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;

Results:

Using JOINS with GROUP BY in MySQL Output

Clarification: Since Alice has made several orders, her total matches the sum of her orders.

Bob and Charlie also calculated their respective total sums.

Comparison of Performance Across Methods

“““html
datasets

Approach	Application Scenario	Advantages	Disadvantages
JOINS	Merges details from two or more interconnected tables using a shared field.	Can link multiple tables	Performance declines with extensive datasets
UNION	Merge and eliminate duplicates from the result sets of two or more queries.	Returns unique rows only	Performance dips due to duplicate removal
UNION ALL	Combines the result sets from two or more queries while considering duplicates.	Quicker than UNION since it doesn’t filter duplicates	Performance declines with large
JOINS with GROUP BY	This method can be applied when collecting data from various tables	Excellent for complex reporting	Improper indexing may lead to a decline in performance
Subqueries	Typically, the WHERE Clause is utilized with a query nested inside another query.	Functions effectively for dynamic filtering	Failure to optimize subqueries could result in a performance drop

Practical Examples

Scenario 1. E-commerce Order Management

A company aims to gather the customer’s details and their latest order.

Illustration:

-- 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),  -- Most Recent Order for Alice
(104, 3, '2024-04-20', 400.00);

--To retrieve customers along 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);

Result:

Clarification: The JOIN operation merges the Customers and Orders tables using CustomerID. For each customer, the subquery retrieves the most recent MAX(OrderDate) order.

Scenario 2. HR System

An HR system needs to extract current and prior salary information of the employee.

Illustration:

-- 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'),  -- Most Recent Salary for John
(203, 2, 60000, '2023-02-01'),
(204, 2, 62000, '2024-02-01'),  -- Most Recent Salary for Jane
(205, 3, 45000, '2023-03-01');

--Retrieve current and previous salaries for 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 &lt; (SELECT MAX(EffectiveDate) FROM Salaries S2 WHERE S2.EmployeeID = E.EmployeeID);

Result:

Clarification: The initial part of the SELECT statement retrieves the latest salary, and the UNION combines it with prior salary records. The UNION operation ensures the results are unique.

Scenario 3. E-learning Platform

An online education platform aims to compute each student’s cumulative grade across all subjects.

Illustration:

-- 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;

Result:

Clarification: The JOIN operation connects the Students and Scores tables based on StudentID. Each student is listed once due to the GROUP BY Clause, which categorizes the results by StudentID.

Summary

Efficient database management requires the capability to retrieve information from multiple tables in SQL. Depending on the performance needs, different benefits are offered by techniques such as JOINs, UNION, UNION ALL, SUBQUERIES, and GROUP BY with JOINs. While UNION is helpful for merging results of various queries, JOINs are more appropriate for consolidating related data. In this article, you have explored various methods to collect data from multiple tables in MySQL.

For further insights into SQL functionalities, consider checking out this SQL course and also explore SQL Interview Questions curated by industry professionals.

SQL Query to Retrieve Data From Multiple Tables – FAQs

The article How to Retrieve Data From Multiple Tables in SQL? was originally published on Intellipaat Blog.

“`

Approaches for Retrieving Data From Multiple Tables

Technique 1: Employing JOINS in MySQL

INNER JOIN in MySQL

LEFT JOIN (LEFT OUTER) in MySQL

RIGHT JOIN (RIGHT OUTER) in MySQL

FULL JOIN (FULL OUTER) in MySQL

CROSS-JOIN in MySQL

SELF JOIN in MySQL

Method 2: Utilizing UNION in MySQL

Approach 3: Utilizing UNION ALL in MySQL

Approach 4: Implementing SUBQUERIES in MySQL

Employing SUBQUERY in SELECT Statement

Employed SUBQUERY in WHERE Clause

Utilizing SUBQUERY in FROM Clause

Approach 5: Utilizing JOINS with GROUP BY in MySQL

Comparison of Performance Across Methods

Practical Examples

Summary

SQL Query to Retrieve Data From Multiple Tables – FAQs

Leave a Reply Cancel reply