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

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
Types Of SQL Joins

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.

Sr.No.SQL JoinsDescription
1.Inner join:SQL Inner join returns records that have matching values in both tables.
2.Left join:SQL Left join returns all records from the left table and the records that match from the right table.
3.Right Join:SQL Right-join returns all records from the right table and those that match from the left table.
4.Full Join:SQL Full join returns all records if a match is in the left or right table.
5.Cross Join:A SQL Cross join query returns the number of rows from the left table multiplied by the number of rows from the right table.
6.Self Join:A Self-join is a join between two copies of the same table with separate alias names.
Different SQL Joins

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:

Inner Join in SQL
Inner Join in SQL

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
Create Tables for SQL Joins examples
Create Tables to demonstrate SQL Joins

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.

SQL inner join query result
SQL inner join query result

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)

Left Join in SQL
Left Join in SQL

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).

left join query result
Result: left Join query

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)

Right Join in SQL Server
Right Join in SQL

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).

SQL Right Join result
Result: SQL Right Outer Join

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)

Full outer Join in SQL
Full outer Join in SQL

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.

Full outer Join in SQL query result
Result: Full outer Join

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)

Cross join in SQL Server
Cross joins in SQL Server

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.

Cross join in SQL Server query result
Result: Cross-join in SQL Server

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 Server JOINS Example

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:

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

Shekh Ali
3 2 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments