SQL SELECT from Multiple Tables

It’s very common need to select data from more than one tables in SQL. In this tutorial, we will check how to select data from the multiple tables with syntax and examples.

SQL Joins are used to get data from the multiple tables. Join are mainly performed on the related tables using common columns.

SQL SELECT from Multiple Tables Syntax

Let’s check the syntax for the select from the multiple tables.

SELECT tablename1.colunmname1, tablename2.colunmname2
FROM tablename1
JOIN tablename2 
ON tablename1.colunmname1= tablename2.colunmname1
[ORDER BY expression [ ASC | DESC ]];

In this syntax,

  • tablename1 – The name of the first table.
  • tablename1.colunmname1 – The name of the column from the first table.
  • tablename2 – The name of the second table.
  • tablename2.colunmname2 – The name of the column from the second table.
  • JOIN – Keyword to join multiple tables.
  • ORDER BY expression [ASC|DESC] – Optional. This is used to sort the selected rows in ascending or descending order. ASC is used to sort in ascending order and DESC is used to sort in descending order.

SQL SELECT from Multiple Tables example

Let’s take three tables, two tables of customers name customer1 and customer2 and the third table is product table.

Customer1 Table:

Cust_id Name1
1 Jonas
2 Henry

Customer2 Table:

Cust_id Name2
c1 Richard
c2 Thomas

Product Table: 

P_id Cust_id P_name
1 1 Notebook
2 2 Pen
3 c1 Laptop
4 c2 Tablet

Example syntax to select from multiple tables.

SELECT p. p_id, p.cust_id, p.p_name, c1.name1, c2.name2 
FROM product AS p 
LEFT JOIN customer1 AS c1 
ON p.cust_id=c1.cust_id 
LEFT JOIN customer2 AS c2 
ON p.cust_id = c2.cust_id;

Output of the above SQL query is as follows:

P_id Cust_id P_name name1 name2
1 1 Notebook Jonas NULL
2 2 Pen Henry NULL
3 c1 Laptop NULL Richard
4 c2 Tablet NULL Thomas

Summary: In this tutorial, you have learned how to select data from the multiple tables in SQL using joins.