Different Types Of SQL Joins

Q: Q.1 Why is SQL join needed?

SQL joins helps 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.5 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.

Different Types Of SQL Joins — Types Of SQL Joins

SQL Joins: A SQL Join clause is use to retrieves data from two or more tables that have a common column. In short, the database tables are linked together by keys (primary key, foreign key), and SQL Joins make use of this key relationship to combine rows from two or more tables. There are mainly 4 different types of SQL joins, including inner join, outer join ( left outer join, right outer join, full outer join), self Join, and cross 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

The following is a list of the different types of SQL joins supported in SQL server.

- (INNER) JOIN
- LEFT (OUTER) JOIN
- RIGHT (OUTER) JOIN
- FULL (OUTER) JOIN
- CROSS JOIN (CARTESIAN PRODUCT)
- SELF JOIN

Inner Join In SQL

1. INNER JOIN: 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.1 Why is SQL join needed?

SQL joins helps 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.2 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.3 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.4 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.5 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.6 What is a full outer join?

A full outer join in SQL combines the results from 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.

Hope you like this article. If you find something incorrect or wish to offer more information regarding the topic addressed here, please leave a comment.

Different Types Of SQL Joins | SQL Join (Inner, Left, Right and Full Joins)

SQL Join Fundamentals