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

Table 2: Customer

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:

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

Table 2: Teachers

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

Table 2: Customer_Details

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

Table 2: Customer_Details

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:

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:

Or, FULL OUTER JOIN

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

Table 1: Product_Details

Table 2: Customer_Details

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:

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.

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:

Or,

Table 1: Product_Details

Table 2: Customer_Details

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:

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:

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

Table 1: Product_Details

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

After executing the query, it produces the below table.