SQL JOINS

The most important, rather powerful feature of SQL Server is Joins, which helps you to join two or more tables to perform various operations. Without SQL Joins, there is no concept of data normalization because we have to store the entire data in one table.

This section explains how to write an SQL query to Join two or more tables using the SQL Joins with an example. The following are some of the frequently asked questions regarding Joins.

How Many Joins in SQL?

There are six types of SQL Joins.

Which SQL Join is most Used?

Inner Join or Join is the most used one in real-time.

Why Joins are used for?

Joins are helpful in the combine or fetch data from more than one table because the database stores our data in separate tables. Therefore, we must select data from at least two tables for any simple query result, even if you’re retrieving employee records.

What are the Joins supported by Sql Server?

1. Inner Join
2. Full Outer Join or Full Join.
3. Left Outer join or Left Join.
4. Right Outer Join or Right Join.
5. Self Join
6. Cross Join

What is the default SQL Join?

Inner Join or simply Join.

What is Inner Join in SQL?

Inner join is also called the Join. It returns the rows present in both the Left table and right table only if there is a match. Otherwise, it returns zero records.

What is Full join in SQL?

Full Outer Join is also called the Full Join. It returns all the rows present in both the Left table and the right table.

What is Left Join in SQL?

Left Outer join or Left Join returns all the rows present in the Left table and matching rows from the right table (if any).

What is Right Join in SQL?

Right Outer Join or Right Join returns matching rows from the left table (if any), and all the rows present in the Right table.

What is Self Join in SQL?

Self Join is used to Join the table with itself. We can use this technique to calculate Running Total etc.

What is Cross Join in SQL?

Cross Join is used to return the Cartesian product of two tables. It means, Number of rows in Employees multiplied by Number of rows in Department table.

Can we do Multiple Joins in SQL?

Yes. A single query can have multiple Joins of the same type or different types. For instance, we can use two left joins on three tables or two inner ones. It all depends on the data that we need.

For this SQL Joins query example, We use two tables Employees Table = Table Data 2 and Department Table = Table Data 3

SQL JOIN Example

The visual representation of the SQL Server Inner Join, Full Outer Join, Left Outer Join, Right Outer Join, Self Join, and Cross Join are

SQL Inner Joins Example

This example shows how to write an Inner Join.

-- SQL Server Joins - Inner Join Example
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

SQL Left Join Example

This example shows how to write a Left Outer Join or Left Join. So, refer Left article in the SQL.

--SQL Server JOINS :- Example for SQL LEFT JOIN, or SQL LEFT OUTER JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT JOIN [Department] AS Dept
  -- LEFT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

SQL Right Joins Example

Let us see how to write a Right Outer Join or Right Join. We had already explained the Right Join.

--SQL Server JOINS :- Example for RIGHT JOIN, or RIGHT OUTER JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT JOIN [Department] AS Dept
  -- RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

SQL Full Joins Example

This example will show you how to write a Full Outer Join or Full Join.

--SQL Server JOINS :- Example for FULL JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL JOIN [Department] AS Dept
  -- FULL OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

SQL Self Joins Example

In this example, we show how to write a SQL Server Self.

--SQL Server JOINS :- Example for SQL SELF JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,NewEmp.[YearlyIncome] + 25000 AS [New Income]
  FROM [Employees] AS Emp,
	[Employees] AS NewEmp
  WHERE Emp.EmpID = NewEmp.EmpID

SQL Cross Join Statement Example

This example shows how to find a Cartesian Product using Joins or simply Cross Join.

--SQL Server JOINS :- Example for SQL CROSS JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept

Multiple Joins Example

Data Present in the login table

In this example, we use the SQL Multiple Joins in one SELECT Statement. We use the Inner Join and Left Join on above tables.

--SQL Server JOINS :- Example for Sql Multiple Joins 
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
	  ,logg.USERNAME
	  ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  LEFT JOIN [LogInTable] AS logg
  ON Emp.EmpID = logg.LogID

SQL Join Where Clause

You can also use Where Clause along with the Joins Max. We are using where clause to display the Employees whose yearly income is greater than or equal to 60000. For this, we used Inner Join to Join the Employee & Department table. Next, we used Where Clause with a condition

-- SQL Server Joins Example
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE YearlyIncome >= 60000

Join Where Clause Example 2

It is another join example to show the Where Clause. Here, we used the Left Join to Join the Emp and Department table. Next, we used Where condition to display the employees whose Education is not Bachelors

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE Education <> 'Bachelors'

SQL Join Where Clause and IS NOT NULL Example

We used the Right Join to Join the Employee and Department table. Next, within the Where clause we used IS NOT NULL to display the records whose ID is not NULL

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL

Joins and Where Example 4

Used Full Join to Join tables. Next, within the Where clause

we used IS NOT NULL to display the records whose ID is not NULL
AND Operator to add another condition
Sales > 1000 display employees whose sales is greater than 1000

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL AND Sales > 1000

Joins and Where Example 5

We used Cross Join along with Where Clause

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  WHERE EmpID IS NOT NULL AND Sales > 3000

SQL Joins Order by and Top Clauses

You can also use the Joins along with Top Clause and Order By. In this example, we used the Inner Join to join Employee and Department tables. Next, we used Order By Clause to sort the records in Descending order using yearly income. Finally, Top Clause will select the first 8 records from the query result.

--SQL Server JOINS Example 
SELECT TOP 8 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  ORDER BY YearlyIncome DESC

This is another Sql Server join example to experience the top and order by along with Joins.

--SQL Server JOINS Example
SELECT TOP 10 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  ORDER BY DepartmentName DESC