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; 

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

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