SQL Joins
SQLJoins means combining data from two or more tables, based on a related column. A table join creates a temporary table showing the data from the joined tables.
For example, we have two tables that follow, the table named tblcustomers stores the information about the customer
id | Name | Address | RegDate |
1 | John Doe | New Delhi | 2024-01-02 |
2 | Anuj kumar | Noida UP | 2024-04-01 |
3 | Amit Singh | Bangalore Karnataka | 2024-05-01 |
4 | Alex | Chennai Tamilnadu | 2024-06-09 |
The tbloders table store the information about the individual orders with their corresponding amount:
id | Customer_Id | Product_Name | Amount |
1 | 1 | iPhone | 920000 |
2 | 2 | Sony TV | 45623 |
3 | 2 | Laptop | 85641 |
4 | 3 | Book | 456 |
5 | 4 | Toys | 1452 |
Rather than storing the customer name in both tables, the tblorders table contains a reference to the customer id that appears in tblcustomers table. We will use join to select the corresponding data from both tables.
Syntax:
1 2 3 4 5 |
SELECT <ColumnName1>,<ColumnName2>,<ColumnNameN> From <TableName1> Join <TableName2> on <TableName1.ColumnName1>=<TableName2.ColumnName2> Where <Condition> Order By <ColumnName1>,<ColumnName2>,<ColumnNameN>; |
In the Above Syntax:
- ColumnName1 in TableName1 is usually that table’s Primary key.
- ColumnName2 in TableName2 is a Foreign Key in that table.
- ColumnName1 and ColumnName2 must have the same Data Type and for certain data types, the same size.
Example using the above two tables tblcustomers and tblorders
1 2 3 4 |
SELECT tblcustomers.id, tblcustomers.Name, tblcustomers.Address, tblorders.Product_Name, tblorders.Amount FROM tblcustomers join tblorders on tblorders.Customer_id=tblcustomers.id ORDER BY tblcustomers.id; |
Output Will be:
id | Name | Address | Product_Name | Amount |
1 | John Doe | New Delhi | iPhone | 920000 |
2 | Anuj kumar | Noida UP | Sony TV | 45623 |
2 | Anuj kumar | Noida UP | Laptop | 85641 |
3 | Amit Singh | Bangalore Karnataka | Book | 456 |
4 | Alex | Chennai Tamilnadu | Toys | 1452 |