Mastering SQL Joins: Exploring Inner, Left, Right, and Full Joins in SQL

Last updated on February 22nd, 2023 at 03:23 am

The article provides a detailed tutorial on combining data from multiple tables in a database using SQL joins. It starts by introducing the concept of joins and the types of joins available, such as inner join, left join, right join, and full outer join. 

The article offers a comprehensive guide to using SQL joins, suitable for beginners and experienced developers.

SQL joins

SQL joins are used to combine data from two or more tables in a relational database. They allow you to retrieve data from multiple tables by linking them based on common fields or keys.

This article will explore the different types of SQL joins, including inner, left, right, and full joins, with syntax and examples in SQL Server.

Types of SQL Joins

The following are the various types of SQL Joins:

• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL INNER JOIN
• SQL FULL JOIN
• SQL CROSS JOIN (Cartesian Product)
• SQL SELF JOIN

The following is a list of SQL joins supported by SQL Server.

1. SQL INNER JOIN (simple join)

The SQL Inner Join, also known as a simple join, returns only the rows with matching values in both tables. It compares the values in the specified columns of each table and returns only the rows where the values match.

Syntax: SQL Inner Join

Inner join has the following syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Visual Illustration (SQL INNER JOIN)

INNER JOIN returns the matching records from table1 and table2 as shown in the diagram below:

It’s important to note that we can also write JOIN instead of INNER JOIN. JOIN and INNER JOIN are the same thing.

To demonstrate SQL joins, let’s create some sample tables.

-- Creating Employee Table (tblEmployee)
   CREATE TABLE tblEmployee
  (
	Id INT PRIMARY KEY IDENTITY,
	Name VARCHAR(100) NOT NULL,
	Gender VARCHAR(10),
	Salary MONEY,
	DepartmentId INT
  )
-- Creating Department Table (tblDepartment)
   CREATE TABLE tblDepartment
  (
	 Id INT PRIMARY KEY,
	 Department VARCHAR(50) NOT NULL,
  )
	
-- Insert Records into the Employee table

   INSERT INTO tblEmployee VALUES('Shekh Ali','Male',50000,1),
                                 ('Ayesha','Female',27000,3),
								 ('Rohit','Male',30000,1),
								 ('Johnson','Male',40000,2),
								 ('Anderson','Male',45000,2),
								 ('Sarah','Female',36000,3),
								 ('John','Male',25000,NULL),
								 ('Emma','Female',28000,NULL);

-- Insert Records into the Department table

 INSERT INTO tblDepartment VALUES(1,'IT'),
                                 (2,'Payroll'),
								 (3,'HR'),
								 (4,'Marketing');
--  Display records from the both employee and department tables
SELECT * FROM tblDepartment;

SELECT * FROM tblEmployee

Example: SQL Inner Join

The following example uses the Inner join clause to extract the rows from the tblEmployee table that have the corresponding rows with the same values in the tblDepartment table:

SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Department
FROM tblEmployee as emp
INNER JOIN 
tblDepartment as  dept
ON emp.DepartmentId = dept.Id;

The following is the result of the SQL inner join query:

We retrieved six rows out of eight from the table tblEmployee. However, We did not retrieve the other two records with the NULL value since the Inner Join in SQL Server simply ignores or eliminates non-matching records.

2. SQL LEFT OUTER JOIN

The left outer join returns all rows from the left table and matching rows from the right table. If there is no match in the right table, it returns NULL for the right table’s columns.

Syntax: SQL Left Outer Join

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Visual Illustration (SQL Left Outer Join)

Example: SQL Left Join

SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Department
From tblEmployee as emp
LEFT JOIN tblDepartment as dept
ON emp.DepartmentId = dept.Id;

The image below shows that the LEFT OUTER JOIN returns all records from the left table (tblEmployee) and the matched records from the right table (tblDepartment).

3. SQL RIGHT OUTER JOIN

The SQL right outer join returns all rows from the right table and matching rows from the left table. If there is no match in the left table, it returns NULL for the left table’s columns.

Syntax: SQL Right Join

The right outer join syntax is as follows:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Visual Illustration (SQL Right Outer Join)

Example: SQL Right Join

SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, dept.Id, dept.Department
From tblEmployee as emp
RIGHT JOIN tblDepartment as dept
on emp.DepartmentId = dept.Id;

As shown in the image below, the Right OUTER JOIN returns all records from the Right table (tblEmployee) and the matched records from the left table (tblDepartment).

4. SQL FULL OUTER JOIN

The Full Outer Join returns all rows from both tables, matching rows from both tables, and NULL values where there is no match.

Syntax: Full Outer Join

The following is the Syntax of the SQL full outer join:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Visual Illustration (SQL Right Outer Join)

Example: Full Right Join

SELECT emp.Id, emp.Name, emp.Gender, emp.Salary, emp.DepartmentId, dept.Id, dept.Department
From tblEmployee as emp
FULL JOIN tblDepartment as dept
on emp.DepartmentId = dept.Id;

As shown in the image below, the Full Outer Join query returns all rows when there is a match in either the left or right table. If neither table has matching columns, It will fill the missing side with NULL values.

5. SQL CROSS JOIN

A cross join, also known as a Cartesian product, returns all possible combinations of the rows in two or more tables. Unlike the other types of joins, it does not require matching columns.

The cross-join produces a result set where each row from the first table is connected to each row from the second table. The number of rows from the left table multiplied by the number of rows from the right table is returned by a cross-join query.

Syntax: SQL Cross Join (Cartesian Product)

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Visual Illustration (SQL Cross Join)

Example: Cross Join

A SQL Cross Join between the tblEmployee and tblDepartment tables is demonstrated in the following example:

SELECT * FROM tblEmployee 
CROSS JOIN tblDepartment;

We have eight records in the tblemployee table and four in the tbldepartment table. As a result of the cross-join between two tables, 32 records will be produced. The ON clause in a cross-join is not allowed.

6. SQL SELF JOIN

A SQL self-join is a regular join, but the table is joined with itself. In other words, it is a way to combine rows from the same table. It uses separate alias names of the same table.

Syntax: Self Join

The following is the syntax for SQL Self Join:

SELECT t1.column_name(s), t2.column_name(s)
FROM table_name t1, table_name t2
WHERE t1.column_name = t2.column_name;

Note: In SQL Server, a Self-join is similar to any other join except that it joins two instances of the same table in the same query.

FAQs

Q: Why is SQL join needed?

SQL joins help to combine data from two or more tables based on a common field between them. SQL Joins are primarily used to filter records and help to reduce duplicate records in the combined result set from relational database tables.

Q: Differences between Union and Union All in MS SQL Server

In SQL Server, the UNION and UNION ALL combine the results of two or more select queries into a single result set.
The main difference between Union and Union All is that Union extracts only the rows provided in the query, whereas Union All returns all rows from both queries, including duplicates (repeated entries).

Q: Difference between JOIN and UNION in SQL Server

In SQL Server, UNION is used to combine the results of two or more SELECT statements into a single result set, whereas JOIN is used to combine data from multiple tables based on a matching condition.

Q: What Is A Self Join In SQL Server?

In SQL Self Join, the SQL query will join two copies of the same table. That means that each row in a table is joined with itself.

Q: What is a full outer join?

A full outer join in SQL combines the left and right outer join results. Only the records from both tables that match will be included in the final result set. If no matches are found, the results will be displayed as NULL.

Q: What is a SQL join?

A SQL join is a way to combine data from two or more tables in a relational database based on a common column. Joins can help us retrieve more meaningful data by linking related information together.

Q: What are the different types of SQL joins?

There are four main types of SQL joins: inner join, left outer join, right outer join, and full outer join. Each type of join has its specific syntax and use case.

References: SQL Server Join Example, Joins-MSDN

Conclusion

In this article, you learned about different types of SQL Server joins, including INNER JOIN, SELF JOIN, CROSS JOIN, and OUTER JOIN, which can be used to combine data from two or more tables.

Recommended articles:

• Views In SQL | Types Of Views In SQL Server
• Stored Procedure in SQL Server With Examples
• SQL Delete Cascade – SQL query to delete parent-child referenced rows
• CREATE, ALTER, AND DROP DATABASE IN SQL SERVER
• Primary Key Constraint In SQL Server
• SQL Comparison Operators
• SQL Server Trigger Update, Insert, Delete Examples

I hope you enjoyed reading this article about “SQL JOINS”. If you find it useful, please share it with others. Also, if you have any additional knowledge about the topic covered here, please leave a comment. Thanks

• Author
• Recent Posts

Shekh Ali

Meet Shekh Ali, a skilled Software engineer and passionate blogger with an MCA degree and 8+ years of experience in the field. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Shekh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Understanding the SOLID Principle: Single Responsibility Principle in C# - June 7, 2023
• Difference between var and dynamic in C# - May 26, 2023
• Understanding the Chain of Responsibility Design Pattern in C# - May 11, 2023