When working with databases, you often need to retrieve data from multiple tables. In SQL (Structured Query Language), a JOIN is used to combine rows from two or more tables based on a related column between them. This related column is usually a primary key in one table and a foreign key in another.
Why Use SQL JOIN?
- To fetch data spread across multiple tables.
- To avoid data duplication in a single table.
- To create meaningful results by combining related data.
Basic Syntax of SQL JOIN
SELECT columns
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;
Types of SQL JOIN
SQL JOINs are mainly classified into INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Let’s explore each.
1. INNER JOIN
Definition:
Returns only the rows that have matching values in both tables. Rows without a match are excluded.
Example:
We have two tables:
Customers
| CustomerID | Name |
|---|---|
| 1 | Kirti |
| 2 | Riya |
| 3 | Rohit |
Orders
| OrderID | CustomerID | Product |
|---|---|---|
| 101 | 1 | Book |
| 102 | 2 | Pen |
Query:
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Result:
| Name | Product |
|---|---|
| Kirti | Book |
| Riya | Pen |
2. LEFT JOIN (or LEFT OUTER JOIN)
Definition:
Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL is returned for right table columns.
Example Query:
SELECT Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Result:
| Name | Product |
|---|---|
| Kirti | Book |
| Riya | Pen |
| Rohit | NULL |
3. RIGHT JOIN (or RIGHT OUTER JOIN)
Definition:
Returns all rows from the right table and the matched rows from the left table. If there is no match, NULL is returned for left table columns.
Example Query:
SELECT Customers.Name, Orders.Product
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Result:
| Name | Product |
|---|---|
| Kirti | Book |
| Riya | Pen |
(If an order exists without a customer, it will also appear with NULL in Name.)
4. FULL JOIN (or FULL OUTER JOIN)
Definition:
Returns all rows when there is a match in either left or right table. Rows without a match in either table will have NULL in the columns of the other table.
Note: Some databases like MySQL do not support
FULL JOINdirectly. You can achieve it usingUNIONof LEFT JOIN and RIGHT JOIN.
Example Query:
SELECT Customers.Name, Orders.Product
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Quick Comparison Table
| JOIN Type | Returns |
|---|---|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN | All rows from left table + matching from right |
| RIGHT JOIN | All rows from right table + matching from left |
| FULL JOIN | All rows from both tables |
Summary
- JOIN in SQL helps to fetch data from multiple tables using related columns.
- Choose INNER JOIN when you need only matching data.
- Choose LEFT JOIN when you need all data from the left table.
- Choose RIGHT JOIN when you need all data from the right table.
- Choose FULL JOIN when you need all data from both tables, whether matched or not.
By understanding these types, you can create more powerful and meaningful SQL queries for your projects.