Database Management

SQL Joins

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:

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:

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:

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:

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:

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