Types of SQL JOIN

SQL JOIN

A SQL Join is used to fetch or combine data (rows or columns) from two or more tables based on the defined conditions.

Table 1: Order

OrderID	CustomerID	OrderName	ProductName
12025	101	Peter	ABC
12030	105	Robert	XYX
12032	110	James	XYZ
12034	115	Andrew	PQR
12035	120	Mathew	AAA

Table 2: Customer

CustomerID	CustomerName	Country
100	Messy	Maxico
101	Prince	Taiwan
103	Maria Fernandez	Turkey
105	Jasmine	Paris
110	Faf Weasel	Indonesia
120	Romen Rocket	Russia

Now, we have two tables Order and the Customer. There is a CustomerID column common in both tables. So, write the SQL query to define the general relationship to select the matches’ records from both tables.

Select Order.OrderID, Customer.CustomerName, Customer.Country, Order.ProductName from Order INNER JOIN Customer ON Order.CustomerID = Customer.CustomerID;  

After executing the above SQL queries, it produces the following output:

OrderID	CustomerName	Country	ProductName
12025	Prince	Taiwan	ABC
12030	Jasmine	Paris	XYX
12032	Faf Weasel	Indonesia	XYZ
12035	Romen Rocket	Russia	AAA

Types of SQL Join

There are different types of joins used in SQL:

1. Inner Join / Simple Join
2. Left Outer Join / Left Join
3. Right Outer Join / Right Join
4. Full Outer Join
5. Cross Join
6. Self Join

Inner Join

The inner join is used to select all matching rows or columns in both tables or as long as the defined condition is valid in SQL.

Syntax:

Select column_1, column_2, column_3 FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;  

We can represent the inner join through the Venn diagram, as follows:

Table 1: Students

Student_ID	StudentName	Subject	TeacherID
101	Alexandra	Computer Science	T201
102	Charles	Economics	T202
103	Tom Cruise	Computer Science	T201
104	Aron Finch	Electronics	T203
105	Siemen Bajoff	Web designing	T204
106	Christopher	English Literature	T205
107	Denim	Fashion Designer	T206

Table 2: Teachers

TeacherID	TeacherName	TeacherEmail
T201	Mr Davis	[email protected]
T202	Mrs Jonas	[email protected]
T201	Mr Davis	[email protected]
T204	Mrs Lopez	[email protected]
T205	Mrs Wiley	[email protected]
T206	Mr Bean	[email protected]

We have two tables: Students and the Teachers Tables. Let’s write the SQL Queries to join the table using the INNER JOIN as follows:

Select Student_ID, StudentName, TeacherName, TeacherEmail FROM Students INNER JOIN Teachers ON Students.TeacherID = Teachers.TeacherID;   

After executing the query, it produces the below table.

Natural Join

It is a type of inner type that joins two or more tables based on the same column name and has the same data type present on both tables.

Syntax:

Select * from tablename1 Natural JOIN tablename_2;

We have two tables: Students and the Teachers Tables. Let’s write the SQL Queries to join the table using the Natural JOIN as follows:

Select * from Students Natural JOIN Teachers;  

After executing the above query, it produces the following table.

LEFT JOIN

The LEFT JOIN is used to retrieve all records from the left table (table1) and the matched rows or columns from the right table (table2). If both tables do not contain any matched rows or columns, it returns the NULL.

Syntax:

Select column_1, column_2, column(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;  

We can also represent the left join through the Venn diagram, as follows:

Note: In some databases, LEFT JOIN is also known as LEFT OUTER JOIN.

Table 1: Product_Details

ProductID	ProductName	Amount
Pro101	Laptop	56000
Pro102	Mobile	38000
Pro103	Headphones	5000
Pro104	Television	25000
Pro105	iPad	60000

Table 2: Customer_Details

CustomerName	CustomerAddress	CustomerAge	ProductID
Martin Guptill	San Francisco, USA	26	Pro101
James	Australia	29	Pro103
Ambati Williamson	New Zealand	27	Pro102
Jofra Archer	South Africa	24	Pro105
Kate Wiley	Australia	20	Pro103

We have two tables: Product_Details and the Customer_Details Tables. Let’s write the SQL Queries to join the table using the LEFT JOIN as follows

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details LEFT JOIN Customer_Details ON Product_Details.ID = Customer_Details.ProductID;  

After executing the query, it produces the following table.

RIGHT JOIN or RIGHT Outer JOIN:

The RIGHT JOIN is used to retrieve all records from the right table (table2) and the matched rows or columns from the left table (table1). If both tables do not contain any matched rows or columns, it returns the NULL.

Syntax:

Select column_1, column_2, column(s) FROM table_1 RIGHT JOIN table_2 ON table_1.column_name = table_2.column_name;  

We can also represent the right join through the Venn diagram, as follows:

Note: In some databases, the RIGHT JOIN is also known as the RIGHT OUTER JOIN.

Table 1: Product_Details

ID	ProductName	Amount
Pro101	Laptop	56000
Pro102	Mobile	38000
Pro103	Headphones	5000
Pro104	Television	25000
Pro105	iPad	60000

Table 2: Customer_Details

CustomerName	CustomerAddress	CustomerAge	ProductID
Martin Guptill	San Francisco, USA	26	Pro101
James	Australia	29	Pro103
Ambati Williamson	New Zealand	27	Pro102
Jofra Archer	South Africa	24	Pro105
Omen	England	29	Pro107
Morgan	England	20	Pro108

We have two tables: Product_Details and the Customer_Details Tables. Let’s write the SQL Queries to join the table using the RIGHT JOIN as follows:

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details LEFT JOIN Customer_Details ON Product_Details.ID = Customer_Details.ProductID;  

After executing the query, it produces the below table.

FULL JOIN or FULL Outer JOIN:

It is a combination result set of both LEFT JOIN and RIGHT JOIN. The joined tables return all records from both the tables and if no matches are found in the table, it places NULL. It is also called a FULL OUTER JOIN.

Syntax:

Select column_1, column_2, column(s) FROM table_1 FULL JOIN table_2 ON table_1.column_name = table_2.column_name;  

Or, FULL OUTER JOIN

Select column_1, column_2, column(s) FROM table_1 FULL OUTER JOIN table_2 ON table_1.column_name = table_2.column_name;  

We can also represent the full outer join through the Venn diagram, as follows:

Table 1: Product_Details

ID	ProductName	Amount
Pro101	Laptop	56000
Pro102	Mobile	38000
Pro103	Headphones	5000
Pro104	Television	25000
Pro105	iPad	60000

Table 2: Customer_Details

CustomerName	CustomerAddress	CustomerAge	ProductID
Martin Guptill	San Francisco, USA	26	Pro101
James	Australia	29	Pro103
Ambati Williamson	New Zealand	27	Pro102
Jofra Archer	South Africa	24	Pro105
Omen	England	29	Pro107
Morgan	England	20	Pro108

We have two tables: Product_Details and the Customer_Details Tables. Let’s write the SQL Queries to join the table using the FULL JOIN as follows:

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details FULL JOIN Customer_Details ON Product_Details.ID = Customer_Details.ProductID;  

After executing the query, it produces the below table.

Note: MySQL does not support FULL JOIN concepts, so we can use UNION ALL clause to combine both tables.

Here is the Syntax for UNION ALL Clause to combine the tables.

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details LEFT JOIN Customer_Details ON Product_Details.ID = Customer_Details.ProductID  

UNION ALL  

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details RIGHT JOIN Customer_Details ON Product_Details.ID = Customer_Details.ProductID 

CROSS JOIN

It is also known as CARTESIAN JOIN, which returns the Cartesian product of two or more joined tables. The CROSS JOIN produces a table that merges each row from the first table with each second table row. It is not required to include any condition in CROSS JOIN.

Syntax:

Select * from table_1 cross join table_2;  

Or,

Select column1, column2, column3 FROM table_1, table_2;  

Table 1: Product_Details

ID	ProductName	Amount
Pro101	Laptop	56000
Pro102	Mobile	38000
Pro103	Headphones	5000
Pro104	Television	25000
Pro105	iPad	60000

Table 2: Customer_Details

CustomerName	CustomerAddress	CustomerAge	ProductID
Martin Guptill	San Francisco, USA	26	Pro101
James	Australia	29	Pro103
Ambati Williamson	New Zealand	27	Pro102
Jofra Archer	South Africa	24	Pro105
Omen	England	29	Pro107
Morgan	England	20	Pro108

We have two tables: Product_Details and the Customer_Details Tables. Let’s write the SQL Queries to join the table using the FULL JOIN as follows:

Select ID, ProductName, CustomerName, CustomerAddress, Amount FROM Product_Details, Customer_Details;  

After executing the query, it produces the below table.

SELF JOIN

It is a SELF JOIN used to create a table by joining itself as there were two tables. It makes temporary naming of at least one table in an SQL statement.

Syntax:

Select column1, column2, column(s) FROM table_1 Tbl1, table_2 Tbl2 WHERE condition;  

Tbl1 and Tbl2 are two different table aliases for the same table.

Table 1: Product_Details

ID	ProductName	Amount
Pro101	Laptop	56000
Pro102	Mobile	38000
Pro103	Headphones	5000
Pro104	Television	25000
Pro105	iPad	60000

Let’s write the SQL Queries to join the table using the SELF JOIN as follows:

Select TB.ID, TB.ProductName FROM Product_Details TB, Product_Details TB2  

WHERE TB.AMOUNT < TB2.AMOUNT; 

After executing the query, it produces the below table.