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

FAQs: Retrieving Records from Multiple Tables in MySQL

What is a JOIN in MySQL?

A JOIN in MySQL is a SQL operation used to combine records from two or more tables based on a related column. It allows you to access and retrieve data from multiple tables in a single query.

How can I retrieve records from multiple tables without matching rows?

Use a LEFT JOIN or RIGHT JOIN to retrieve all records from one table and the matching records from the other table. If there are no matches, NULL values are returned for columns from the table without matching rows.

What is the UNION operator in MySQL?

The UNION operator is used to combine the result sets of two or more SELECT queries. Each SELECT statement within the UNION must have the same number of columns, and the columns must have similar data types. The UNION operator removes duplicate rows between the SELECT statements.