Javatpoint Logo
Javatpoint Logo

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:

Types of SQL JOIN

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 abc@email.com
T202 Mrs Jonas jonas@email.com
T201 Mr Davis abc@email.com
T204 Mrs Lopez lopez@email.com
T205 Mrs Wiley wiley@email.com
T206 Mr Bean bean@email.com

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.

Types of SQL JOIN

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.

Types of SQL JOIN

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:

Types of SQL JOIN

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.

Types of SQL 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:

Types of SQL JOIN

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:

After executing the query, it produces the below table.

Types of SQL JOIN

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:

Types of SQL 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 FULL JOIN as follows:

After executing the query, it produces the below table.

Types of SQL JOIN

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.


Types of SQL JOIN

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

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:

After executing the query, it produces the below table.

Types of SQL JOIN

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

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:

After executing the query, it produces the below table.

Types of SQL JOIN
Next TopicSQL JOIN




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA