SQL Joins - Join (Inner, Left, Right and Full Joins)

Q: 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: Q: What Is A Self Join In SQL Server?

Self Join : Two copies of the same table will be joined in the SQL query. This means that each row in a table is joined with itself.

Last updated on October 21st, 2022 at 04:01 pm

Different Types Of SQL Joins — Types Of SQL Joins

The SQL Join clause is used to join data or rows from two or more tables based on a common field.

The following are the various types of Joins:

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

SQL Join Fundamentals

In a SQL Join query, a condition describes how two database tables are related by:

Choosing which columns from each database table should be used in the SQL join. A simple SQL join condition specifies a foreign key from one table and its corresponding key in the other table.
Choosing a logical operator (for example, = or <, >,) to compare values from the columns

Different Types of SQL Joins

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

Sr.No.	SQL Joins	Description
1.	INNER JOIN:	Inner join returns records that have matching values in both tables.
2.	LEFT JOIN:	Left join returns all records from the left table as well as the records that match from the right table.
3.	RIGHT JOIN:	Right-join returns all records from the right table as well as the records that match from the left table.
4.	FULL JOIN:	Full join returns all records if there is a match in either the left or right table.
5.	CROSS JOIN:	A 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 that have separate alias names.

Different SQL Joins

Inner Join In SQL

INNER JOIN returns records/rows from both database tables that have the same value. The Inner Join returns rows from all tables that satisfy the join condition.

Syntax: SQL Inner Join

Inner join has the following syntax:

SELECT * FROM Table1 
INNER JOIN Table2
ON Table1.matching_column = Table2.matching_column;

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.

/*Author: Shekh Ali
  Example: SQL Joins
  Website: WWW.SHEKHALI.COM
*/
-- Create Employee Table (tblEmployee)

   CREATE TABLE tblEmployee
  (

	Id INT PRIMARY KEY IDENTITY,
	Name VARCHAR(100) NOT NULL,
	Gender VARCHAR(10),
	Salary MONEY,
	DepartmentId INT

  )

-- Create 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

SQL Inner Join Example:

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:

-- ****** Inner Join Example ******
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 6 rows out of 8 from the table tblEmployee, however, the other 2 records with the NULL value were not retrieved since the Inner Join in SQL Server simply ignores or eliminates non-matching records.

Left Outer Join In SQL

2. LEFT OUTER JOIN: The LEFT OUTER JOIN retrieves all records from the left table and the only rows from the right table that match the records in the left table. If no matching record is found in the right side table, it will return NULL values.

Syntax: SQL Left Outer Join

-- Syntax for LEFT (OUTER) JOIN
-- Note: 'OUTER' is optional
SELECT * FROM Table1 
LEFT JOIN Table2
ON Table1.matching_column = Table2.matching_column;

SQL Left Join Example:

-- ****** SQL Left Join Example ******
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;

As shown in the image below 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

Right Outer Join In SQL

3. RIGHT OUTER JOIN: The Right Join , also known as the right outer join, is the opposite of the left join.
The right join returns a result set that includes all of the rows from the right table and the matching rows from the left table. If none of the columns in the left table match, NULL values are returned.

Syntax: SQL Right Join

The right outer join syntax is as follows:

-- Syntax for RIGHT (OUTER) JOIN
SELECT * FROM Table1 
RIGHT JOIN Table2
ON Table1.matching_column = Table2.matching_column;

Right Join in SQL Server — Right Join in SQL

SQL Right Join Example:

/****** SQL Right Join Example ******/
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

Full Outer Join In SQL

4. FULL OUTER JOIN: A Full Join or full outer join in SQL combines the left and right outer joins. With this join, all rows from both tables are returned. If there are no columns in both tables that match, the missing side will be filled with NULL values.

Syntax: Full Outer Join

The following is the full outer join syntax:

-- Syntax for Full (Outer) Join
SELECT * FROM Table1 
FULL OUTER JOIN Table2
ON Table1.matching_column = Table2.matching_column;

Full Right Join Example:

/****** SQL Right Join Example ******/

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 table or right table. If neither table has any matching columns, the missing side will be filled with NULL values.

Cross Join In SQL

5. CROSS JOIN: In SQL Server, a Cross Join is also known as a CARTESIAN Join, which stands for the product of two tables. The cross-join produces a result set in which 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: Cross Join (Cartesian Product)

-- Syntax for Cross Join
SELECT * FROM Table1 
Cross JOIN Table2

Cross join in SQL Server — Cross joins in SQL Server

Cross Join Example:

A cross-join between the tblEmployee and tblDepartment tables is demonstrated in the following example:

/****** SQL Cross Join Example ******/
SELECT * FROM tblEmployee 
CROSS JOIN tblDepartment;

We have 8 records in the tblemployee table and 4 records 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

Self Join In SQL

6. SELF JOIN: In SQL Server, a Self Join is a regular join used to join a table to itself. In other words, a self-join is a join between two copies of the same table.
In the Self Join query, each row in the table joins itself and all other rows under certain conditions.
It uses separate alias names of the same table.

Syntax: Self Join

The following is the basic syntax for SELF JOIN:

-- Syntax for Self Join
SELECT t1.coulmn1 , t2.column2
FROM table1 t1, table1 t2
WHERE t1.common_field = t2.common_field;

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: What are the different types of SQL joins?

SQL Server supports different kinds of joins such as :
1. INNER JOIN
2. SELF JOIN
3. CROSS JOIN
4. OUTER JOIN.

The Outer Join is further divided into LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.

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

In SQL Server, the UNION and UNION ALL are used to 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?

Self Join: Two copies of the same table will be joined in the SQL query. This 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 results from the left and right outer join. In the final result set, only the records from both tables that match will be included. If no matches are found, the results will be displayed as NULL.

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.

SQL Joins – Join (Inner, Left, Right and Full Joins)