Retrieve Records from Multiple Tables in MySQL

The JOIN clause allows users to retrieve records from multiple tables in MySQL. JOIN clause is used to combine rows of two or more tables based on a specified common column.

This article explores how to retrieve records from Multiple Tables in MySQL and covers the syntax and examples to provide a better understanding.

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.

Retrive Records From Multiple Tables Examples

Let’s look at some examples on 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.

MySQL

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

id	name
1	John Doe
2	Jane Smith
3	David Lee

orders table:

order_id	customer_id	product_name
1234	1	Laptop
5678	2	Smartphone

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

Using INNER JOIN to Retrive Records From Multiple Tables

In this example we have created 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 key 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.

Output of INNER JOIN

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 Retrive Records From Multiple Tables

Retrieve customer names and their corresponding order details with the help of 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.

Output of LEFT JOIN

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‘.

Conclusion

MySQL’s multiple table data retrieval offers comprehensive insights and efficient data manipulation. Learnign JOIN operation is crucial for merging records and finding complex relationships. JOIN operation allows users to get records from multiple tables for fast data retrieval.