SQL SELECT from Multiple Tables
This statement is used to retrieve fields from multiple tables. To do so, we need to use join query to get data from multiple tables.
Let's see the example for the select from multiple tables:
SELECT orders.order_id, suppliers.name
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY order_id;
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY order_id;
Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.
Customer1 table
| Cus_id | Name1 |
|---|---|
| 1 | Jack |
| 2 | Jill |
Customer2 table
| Cus_id | Name2 |
|---|---|
| 1 | Sandy |
| 2 | Venus |
Product table
| P_id | Cus_id | P_name |
|---|---|---|
| 1 | 1 | Laptop |
| 2 | 2 | Phone |
| 3 | P1 | Pen |
| 4 | P2 | Notebook |
Example syntax to select from multiple tables:
SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2
FROM product AS p
LEFT JOIN customer1 AS c1
ON p.cus_id=c1.cus_id
LEFT JOIN customer2 AS c2
ON p.cus_id = c2.cus_id
FROM product AS p
LEFT JOIN customer1 AS c1
ON p.cus_id=c1.cus_id
LEFT JOIN customer2 AS c2
ON p.cus_id = c2.cus_id
| P_id | Cus_id | P_name | P_name | P_name |
|---|---|---|---|---|
| 1 | 1 | Laptop | Jack | NULL |
| 2 | 2 | Phone | Jill | NULL |
| 3 | P1 | Pen | NULL | Sandy |
| 4 | P2 | Notebook | NULL | Venus |
