Different SQL JOIN | INNER JOIN | OUTER JOIN | CROSS JOIN

In the SQL Interview, some questions are based on JOINs as well. Sometimes the interviewer asks to write the SQL JOIN Query, to retrieve the required data from the different tables. In this article, we will walk through all the different kinds of JOINs available in SQL.

Please visit our Youtube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Introduction

I have created the following two tables which we will use for all the examples in this article.

Here the EmployeeRole table contains the employee role records and the Employee table contains the employee records along with the role. For the demonstration of SQL JOINs, I have kept both the table very simple.

SQL JOIN

In SQL Joins are used to retrieve the data from multiple tables. Generally, there are some relationships between two tables or between the columns of the same table (in SELF JOIN) when we apply any JOIN except the CROSS JOIN.

There are mainly following three categories of JOINs in SQL:

A. INNER JOIN

B. OUTER JOIN

C. CROSS JOIN

Apart from these three main categories, there are some other Join Terms as well for example ANSI JOIN, NON-ANSI JOIN, EQUI JOIN, NON-EQUI JOIN, SELF JOIN, NATURAL JOIN as well. But they are not any separate kind of join at all. We will see them as well in this article.

When we join two tables, the first table is always known as Left Table and the second table is known as the Right Table.

Please watch the JOIN vs SubQuery video here for more details.

NON-ANSI JOIN

A NON-ANSI JOIN is a way to retrieve the information from multiple tables based on the "WHERE" clause condition only. The NON-ANSI JOIN does not use an ON clause to specify the Join Condition.

The NON-ANSI joins use only Standard SQL Syntaxes. The NON-ANSI joins were used prior to SQL 2005 version. These Joins are executed only through Standard SQL Operators.

Please watch the NON-ANSI JOIN video here for more details.

ANSI JOIN

An ANSI JOIN is a way to retrieve the information from multiple tables based on the "ON" clause condition. The ANSI JOIN uses an ON clause to specify the Join Condition.

ANSI Format JOIN has been introduced in SQL 2005 version. In ANSI JOIN, the joining condition is specified through the "ON" Clause instead of the "WHERE" clause.

Please watch the ANSI JOIN video here for more details. You can also watch the ON Clause vs WHERE Clause video here.

EQUI JOIN

An EQUI JOIN is a NON-ANSI JOIN to retrieve the information from multiple tables only by using the Equality Operator (=). The result of the EQUI JOIN is the same as the INNER JOIN of SQL.

Please watch the EQUI JOIN video here for more details.

NON-EQUI JOIN

A NON- EQUI JOIN is also a NON-ANSI JOIN to retrieve the information from multiple tables by using any operator excluding the Equality Operator (=).

So it supports all the SQL Standard Operators like Relational Operators ( >, <), Logical Operators( AND, OR, NOT) SET Operators( ANY, IN, ALL), etc except the Equality Comparision Operator (=).

Please watch the NON-EQUI JOIN video here for more details.

INNER JOIN

An INNER JOIN is the default ANSI JOIN. The INNER JOIN is used to retrieve only the matching records from both tables. INNER JOIN discards all the non-matching rows from both tables.

Since the INNER JOIN is the DEFAULT JOIN, if you do not provide any join name, it will be treated as INNER JOIN by default. It is also known as EQUI JOIN as it uses only the Equality Operator.

In the INNER JOIN, the order of the table does not matter. So if you change the order of the table the result will still be the same.

Please watch the INNER JOIN video here for more details. You can also watch the INNER JOIN vs IN Operator video here.

OUTER JOIN

The OUTER JOIN is an extension of the INNER JOIN. As the INNR JOIN returns only the matching row but using the OUTER JOIN we can retrieve both matching and non-matching records from both tables.

OUTER JOIN is further classified into three categories:

1. LEFT OUTER JOIN or LEFT JOIN

2. RIGHT OUTER JOIN or RIGHT JOIN

3. FULL OUTER JOIN or FULL JOIN

In all the above OUTER JOIN the "OUTER" keyword is optional. So you can use only LEFT, RIGHT, and FULL keywords instead of full names. But in all of the examples here, I have used the full names to avoid confusion.

LEFT OUTER JOIN or LEFT JOIN

The LEFT OUTER JOIN returns all the records from the first table (Left Table) and only the matching records from the second table (Right Table).

So the LEFT JOIN basically does the following three things:

Returns the matching records from both tables.
Returns the non-matching records from the first table (Left Table).
Returns (or Set) the NULL values for the corresponding non-matching rows of the second table (Right Table).

One very important thing which you have to keep in mind is that the order of the table matters in the case of LEFT JOIN. So if you change the order of the tables, the result will also be changed. Therefore you have to decide first, which table you have to use as a base table (Left Table or First Table).

If you see the result of the above LEFT JOIN is the same as the RIGHT JOIN, which we will see in a moment.

One more thing which you have to keep in mind is that a WHERE clause in the LEFT JOIN turned the LEFT JOIN into the INNER JOIN. This question is asked in most of the interviews.

In all of the above LEFT JOIN Query, we can omit the "OUTER" keyword. So we can use the "LEFT JOIN" only instead of the full name "LEFT OUTER JOIN". There will not be any change in the result.

Please watch the LEFT JOIN video here for more details. You can also watch the INNER JOIN vs LEFT JOIN video here.

RIGHT OUTER JOIN or RIGHT JOIN

The RIGHT OUTER JOIN returns all the records from the second table (Right Table) and only the matching records from the first table (Left Table). In short, the RIGHT JOIN is just the reverse of the LEFT JOIN.

So the RIGHT JOIN basically does the following three things:

Returns the matching records from both tables.
Returns the non-matching records from the second table (Right Table).
Returns (or Set) the NULL values for the corresponding non-matching rows of the first table (Left Table).

If you see the result of the above RIGHT JOIN, it is the same as the LEFT JOIN of the previous example when we reversed the table order. That's why we generally do not use the RIGHT JOIN most frequently because we can achieve the same result using the LEFT JOIN itself.

The order of the table matters in the case of RIGHT JOIN as well same as the LEFT JOIN. So if you change the order of the table, the result will also be changed.

In both the above RIGHT JOIN Query, we can omit the "OUTER" keyword. So we can use the "RIGHT JOIN" only instead of the full name "RIGHT OUTER JOIN". There will not be any change in the result.

Please watch the RIGHT JOIN video here for more details.

FULL OUTER JOIN or FULL JOIN

The FULL OUTER JOIN is the combination of both LEFT JOIN and RIGHT JOIN. So FULL OUTER JOIN returns both matching and non-matching data from both the tables.

So a FULL JOIN basically does the following three things:

Returns the matching records from both tables.
Returns the non-matching records from the first table (Left Table). And also it returns (or Set) the NULL values for the corresponding non-matching rows of the second table (Right Table).
Returns the non-matching records from the second table (Right Table). And also it returns (or Set) the NULL values for the corresponding non-matching rows of the first table (Left Table).

The order of the table does not matter in the case of FULL JOIN, unlike the LEFT JOIN and RIGHT JOIN. But the order of records may be changed when you change the table order.

Please watch the FULL JOIN video here for more details.

As I have said that the FULL OUTER JOIN is nothing but the combination of LEFT JOIN and RIGHT JOIN. So let me prove that:

You can clearly see that the result of the UNION of both the LEFT JOIN and RIGHT JOIN is the same as the FULL JOIN.

Please watch the UNION vs UNION ALL video here for more details.

CROSS JOIN or CARTESIAN JOIN

The CROSS JOIN returns the Cartesian Product of the table records. The CROSS JOIN does not have an ON clause to specify the JOIN Condition. If you try to do so, you will get a syntax error.

In CROSS JOIN, each record of the first table is joined with each record of the second table. So the result size of a CROSS JOIN is the number of rows in the first table multiplied by the number of rows in the second table.

The order of the table does not matter here as well. If you change the order of tables, the total number of records still will be the same. But the order of the records may change when you change the table order.

We can achieve the CROSS JOIN result using the NON-ANSI JOIN as well. If you remember from the previous NON-EQUI JOIN, the syntax is pretty much similar (except the WHERE clause).

Please watch the CROSS JOIN video here for more details. You can also watch the CROSS JOIN vs FULL JOIN video here.

SELF JOIN

A SELF JOIN is not any separate kind of join. SELF JOIN is just like any other join except that two instances of the same table will be joined in the query. There is no keyword like "SELF" in SQL.

Joining a table with itself is called a SELF JOIN. When we have some relation between the columns within the same table then we use the SELF JOIN.

When we implement a SELF JOIN, we create the alias for the table and we can create any number of aliases for a single table. Without alias names, we cannot implement SELF JOIN.

One of the common scenarios of SELF JOIN is the Employee Table which contains Employee and Manager mapping in the same table. So to fetch the employee and their manager we have to use the SELF JOIN.

Similarly, one more example for SELF JOIN is the Country-State or State-Capital table. In order to get the country and their state or state and their capital, we have to use the SELF JOIN.

Please watch the SELF JOIN video here for more details.

SQL JOIN Query Scripts

  
 

-- Create EmployeeRole Table
CREATE TABLE [dbo].[EmployeeRole](
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,	
[RoleName] [VARCHAR](50) NOT NULL 
);
	
-- Create Employee Table
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL,
[Email] [varchar](50) NULL,
[MobileNumber] [nchar](15) NULL,
[EmployeeRoleId] [int] 
)

-- Inserting EmployeeRole Table Records
INSERT INTO EmployeeRole(RoleName)
VALUES ('SuperAdmin'),
   ('Admin'),		
   ('Director'),
   ('Manager'),
   ('Anonymous');

 -- Inserting Employee Table Records
INSERT INTO Employee(Name, Email, MobileNumber, EmployeeRoleId)
VALUES ('Alex', 'alex@gmail.com', '5224578930', 1),
   ('Thomas', 'thomas@gmail.com', '5229848931', 2),		
   ('Peter', 'peter@gmail.com', '5224578831', 0),
   ('Anton', 'anton@gmail.com', '5224579937', NULL),
   ('Maria', 'maria@gmail.com', '5224571965', 5),
   ('Brien', 'brien@gmail.com', '5224571979', 4);
   
-- NON ANSI FORMAT JOIN (EQUI JOIN)
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E, EmployeeRole R
WHERE E.EmployeeRoleId = R.Id
   
-- ANSI FORMAT JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
INNER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id

-- EQUI JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E, EmployeeRole R
WHERE E.EmployeeRoleId = R.Id	
   
-- NON-EQUI JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E, EmployeeRole R
WHERE E.EmployeeRoleId > R.Id

-- INNER JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
INNER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id

When you reverse the table order:

-- INNER JOIN or JOIN 
-- Reversed Table Order
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM EmployeeRole R 
INNER JOIN Employee E ON E.EmployeeRoleId = R.Id

-- LEFT OUTER JOIN or LEFT JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
LEFT OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id


-- LEFT OUTER JOIN or LEFT JOIN 
-- Reversed Table Order
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM  EmployeeRole R
LEFT OUTER JOIN Employee E ON E.EmployeeRoleId = R.Id


-- LEFT OUTER JOIN or LEFT JOIN
-- With WHERE Clause
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
LEFT OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id
WHERE R.Id IS NOT NULL


-- RIGHT OUTER JOIN or RIGHT JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
RIGHT OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id

-- RIGHT OUTER JOIN or RIGHT JOIN 
-- Reversed Table Order
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM EmployeeRole R 
RIGHT OUTER JOIN Employee E ON E.EmployeeRoleId = R.Id

-- FULL OUTER JOIN or FULL JOIN
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
FULL OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id

-- FULL OUTER JOIN or FULL JOIN
-- Reversed Table Order
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM EmployeeRole R
FULL OUTER JOIN Employee E  ON E.EmployeeRoleId = R.Id

-- FULL JOIN = LEFT JOIN + RIGHT JOIN
SELECT Name, Email, MobileNumber, RoleName
FROM Employee E
LEFT OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id

UNION

SELECT Name, Email, MobileNumber, RoleName
FROM Employee E
RIGHT OUTER JOIN EmployeeRole R ON E.EmployeeRoleId = R.Id


-- CROSS JOIN (ANSI FORMAT)
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM Employee E
CROSS JOIN EmployeeRole R

Here total 30 Rows are returned:

-- CROSS JOIN (ANSI FORMAT)
-- Reversed Table Order
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM EmployeeRole R 
CROSS JOIN Employee E

-- CROSS JOIN (NON ANSI FORMAT)
SELECT 
		Name,
		Email,
		MobileNumber,
		RoleName
FROM EmployeeRole R, Employee E
   
 -- SELF JOIN 
SELECT 
		E1.Name,
		E1.Email,
		E1.MobileNumber,
		E1.Id,
		E2.EmployeeRoleId
FROM Employee E1 
JOIN Employee E2 ON E1.Id = E2.EmployeeRoleId

Sunday, February 20, 2022

Different SQL JOIN | INNER JOIN | OUTER JOIN | CROSS JOIN