SQL Joins
Purpose:
Join is the most important concept in SQL. Join is used for getting data from two or more tables however it is observed that people do not understand the right concept of the join and they end up using the wrong join method which might result in the wrong dataset.
Use of Join:
A SQL Join statement is used to combine data from two or more tables based on a common field between them. When we have a large web application and data are huge in multiple tables then we need to combine the data of two or more tables. In such a case, we need to use join queries.
Types of Join:
- Left Join
- Right Join
- Full Outer Join
- Inner Join
- Cross Join
- Self Join
Here, we are using below two tables for Join examples.
Examples Tables:
Customers:
| ID | Name | Age | State |
| 1 | Ramesh | 20 | MP |
| 2 | Mahesh | 23 | UP |
| 3 | Raj | 23 | GJ |
| 4 | Jay | 24 | AP |
| 5 | Rohan | 25 | GJ |
| 6 | Vijay | NULL | NULL |
Orders:
| ID | Date | Customer ID |
| 1 | 10-02-2021 | 3 |
| 2 | 11-02-2021 | 3 |
| 3 | 11-04-2021 | 2 |
| 4 | 11-07-2021 | 4 |
| 5 | 11-09-2021 | NULL |
Left Join:
This join returns all the values which are located in the left table and matched values that are located in the right table.
select cs.id, cs.name, ord.date from customers cs left join orders ord on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 10-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
| 5 | Rohan | NULL |
| 6 | Vijay | NULL |
| 1 | Ramesh | NULL |
Description:
The above query returns all records from the customer’s tables and matched records from the orders table.
select cs.id, cs.name, ord.date from orders ord left join customers cs on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 11-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
| NULL | NULL | 11-09-2021 |
Description:
The above query returns all records from orders tables and matched records from the customers table.
Right Join:
This join returns all the values which are located in the right table and unmatched values which are located in the left table
select cs.id, cs.name, ord.date from customers cs right join orders ord on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 11-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
| NULL | NULL | 11-09-2021 |
Description:
The above query returns all records from orders tables and unmatched records from customers table.
select cs.id, cs.name, ord.date from orders ord right join customers cs on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 11-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
| 5 | Rohan | NULL |
| 6 | Vijay | NULL |
| 1 | Ramesh | NULL |
Description:
The above query returns all records from customers table and unmatched records from orders table.
Inner Join:
It is used to return only those rows from the tables that matched the specified condition. it returns the common rows from two tables.
select cs.id, cs.name, ord.date from customers cs inner join orders ord on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 11-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
Description:
The above query return matched records from customers and orders table.
Full Outer Join:
This query return all records from left and right table.
select cs.id, cs.name, ord.date from customers cs full outer join orders ord on cs.id = ord.customer_id
| ID | Name | Date |
| 3 | Raj | 10-02-2021 |
| 3 | Raj | 11-02-2021 |
| 2 | Mahesh | 11-04-2021 |
| 4 | Jay | 11-07-2021 |
| NULL | NULL | 11-09-2021 |
| 5 | Rohan | NULL |
| 6 | Vijay | NULL |
| 1 | Ramesh | NULL |
Description:
The above query return all data from customer table and orders table.
select cs.id, cs.name, ord.date from orders ord right join customers cs on cs.id = ord.customer_id
Cross Join:
It is used to combine all possibilities of the two or more tables and return the result that contains every row from all contributing tables. It is also known as cartesian join, which means that provides the cartesian product of all associated tables.
select name, date, customer_id from customers cross join orders
| Name | Date | CustomerID |
| Ramesh | 10-02-2021 | 3 |
| Mahesh | 10-02-2021 | 3 |
| Raj | 10-02-2021 | 3 |
| Jay | 10-02-2021 | 3 |
| Rohan | 10-02-2021 | 3 |
| Vijay | 10-02-2021 | 3 |
| Ramesh | 11-02-2021 | 3 |
| Mahesh | 11-02-2021 | 3 |
| Raj | 11-02-2021 | 3 |
| Jay | 11-02-2021 | 3 |
| Rohan | 11-02-2021 | 3 |
| Vijay | 11-02-2021 | 3 |
| Ramesh | 11-04-2021 | 2 |
| Mahesh | 11-04-2021 | 2 |
| Raj | 11-04-2021 | 2 |
| Jay | 11-04-2021 | 2 |
| Rohan | 11-04-2021 | 2 |
| Vijay | 11-04-2021 | 2 |
| Ramesh | 11-07-2021 | 4 |
| Mahesh | 11-07-2021 | 4 |
| Raj | 11-07-2021 | 4 |
| Jay | 11-07-2021 | 4 |
| Rohan | 11-07-2021 | 4 |
| Vijay | 11-07-2021 | 4 |
| Ramesh | 11-09-2021 | NULL |
| Mahesh | 11-09-2021 | NULL |
| Raj | 11-09-2021 | NULL |
| Jay | 11-09-2021 | NULL |
| Rohan | 11-09-2021 | NULL |
| Vijay | 11-09-2021 | NULL |
Description:
Above query returns all records from the customers table that contains every row from orders tables.
Self Join:
In this join, a table joins with itself
select o1.id, o1.date, o1.customer_id from orders o1, orders o2 where o1.customer_id = o2.customer_id and o1.date != o2.date;
| ID | Date | Customer ID |
| 1 | 10-02-2021 | 3 |
| 2 | 11-02-2021 | 3 |
References:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
