Retrieve Records from Multiple Tables in MySQL

In relational databases like MySQL, data is often spread across multiple tables to maintain normalization and avoid redundancy. To effectively work with such data, you need to combine and retrieve records from these tables using various types of joins and other methods. This article will guide you through different ways to retrieve records from multiple tables in MySQL.

How to Retrieve Records from Multiple Tables in MySQL

The JOIN keyword connects two or more tables based on a specific column. This can be used to access records of both tables and retrieve them in a single SELECT statement.

The syntax for utilizing JOINs in MySQL hinges on the JOIN keyword followed by the table names and the JOIN type, along with the ON clause to define the joining condition.

Syntax:

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

Parameters:

• SELECT: Specifies the columns you wish to retrieve from the joined tables.
  
• FROM: Indicates the tables involved in the JOIN.
  
• JOIN: Initiates the JOIN operation.
  
• table1, table2: Names of the tables being joined.
  
• ON: Defines the condition that establishes the relationship between the tables.

Retrieve Records From Multiple Tables Examples

Let's look at some examples of how to retrieve records from multiple tables in MySQL.

First, let's create a demo MySQL database on which we will perform the MySQL queries.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO customers (id, name) VALUES
    (1, 'John Doe'),
    (2, 'Jane Smith'),
    (3, 'David Lee');

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_name VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO orders (order_id, customer_id, product_name) VALUES
    (1234, 1, 'Laptop'),
    (5678, 2, 'Smartphone');
    
SELECT * FROM orders;
SELECT * FROM customers;

Output:

customers table:

orders table:

After creating the demo tables, let's look at MySQL queries to retrieve records from multiple tables.

Using INNER JOIN to Retrieve Records From Multiple Tables

In this example, we have created a database as Record and Consider two tables customers and orders. We want to retrieve customer names and their corresponding order details with the help of foreign keys as written here.

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

Output:

This query will only return records where a customer in the customers table has a matching entry in the orders table, based on the customer_id column.

+------------+----------+--------------+
| name       | order_id | product_name |
+------------+----------+--------------+
| John Doe   |     1234 | Laptop       |
| Jane Smith |     5678 | Smartphone   |
+------------+----------+--------------+

Explanation: The SELECT query retrieves the names of customers along with order details such as order_id and product_name by performing an INNER JOIN on the 'customers' and 'orders' tables using the common key 'id' and 'customer_id', respectively.

Using LEFT JOIN to Retrieve Records From Multiple Tables

Retrieve customer names and their corresponding order details with the help of a foreign key.

SELECT customers.name, orders.order_id, orders.product_name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Output:

This query will include all customers from the customers table, with matching order details from the orders table if available. If no order exists for a customer, the order_id and product_name columns will be populated with NULL values.

+------------+----------+--------------+
| name       | order_id | product_name |
+------------+----------+--------------+
| John Doe   |     1234 | Laptop       |
| Jane Smith |     5678 | Smartphone   |
| David Lee  |     NULL | NULL         |
+------------+----------+--------------+

Explanation: The SELECT query fetches customer names along with order details such as order_id and product_name. It uses a LEFT JOIN to include all customers, even those without orders, linking them based on the common key 'id' and 'customer_id'.

Using UNION

-- Retrieve customer names from the customers table
SELECT name AS item_name
FROM customers

UNION

-- Retrieve product names from the orders table
SELECT product_name AS item_name
FROM orders;

Output:

+------------+
| item_name  |
+------------+
| John Doe   |
| Jane Smith |
| David Lee  |
| Laptop     |
| Smartphone |
+------------+

Explanation: This UNION operation combines the results of two queries into a single result set. It retrieves customer names from the 'customers' table and product names from the 'orders' table, ensuring there are no duplicate rows.

Conclusion

Retrieving records from multiple tables in MySQL is a common requirement and can be accomplished using various types of joins, subqueries, and UNION operations. Understanding these techniques allows you to efficiently query your database and extract the necessary data for your applications. Use INNER JOIN for matching records, LEFT JOIN or RIGHT JOIN when you need all records from one table regardless of matches, and UNION or subqueries for more complex retrieval scenarios.