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