83

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.

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:

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:

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:

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:

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:

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:

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:

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