SQL JOINS

There are six types of SQL Joins, and they are:

1. Inner Join: Also called as 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.
2. Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table and the right table.
3. Left Outer join: Or simply called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).
4. Right Outer Join: Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.
5. Self Join: It is used to Join the table with itself. We can use this technique to calculate Running Total etc.
6. Cross Join: It 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

In this article, we will show you, How to write a SQL Query to Join two or more tables using the SQL Joins with an example.

This is one of the SQL Server Frequently Asked Question. For this example, We are going to use the below-shown data

Data present in the Department Table is:

SQL JOIN Example

Before we get into the practical example, Let us see the visual representation of the Inner Join, Full Outer Join, Left Outer join, Right Outer Join, Self Join, and Cross Join for better understanding.

SQL Joins Example – Inner Join

In this example, we will show you, How to write an Inner Join in SQL Server. For this, We are going to use the Employee and Department tables that we shown above.

We have already explained the Inner Join in our previous article. So, I suggest you refer them for better understanding

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

OUTPUT

SQL Joins – Left Join Example

In this example, we will show you, How to write a Left Outer Join or Left Join in SQL Server. For this, We are going to use the tables that we have shown above. We had already explained the Left Join in our previous article. So, I suggest you refer them for better understanding

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

OUTPUT

SQL Joins Example – Right Join

In this example, we will show you, How to write a Right Outer Join or Right Join in SQL Server. For this, We are going to use the tables that we have shown above. We had already explained the Right Join in our previous article so, please refer the same

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

OUTPUT

SQL Joins Example – Full Join

In this example, we will show you, How to write a Full Outer Join or Full Join in SQL Server. We had already explained the Full Join in our previous article so, I suggest you refer it

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

OUTPUT

SQL Joins Example – Self Join

In this example, we will show you, How to write a Self Join in SQL Server. We had already explained the Self Join article

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

OUTPUT

SQL Join Statement Example – Cross Join

In this example, we will show you, How to find a Cartesian Product using Joins, or simply Cross Join in SQL Server. We had already explained the Cross Join in the previous article so, check out that tutorial

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

OUTPUT

SQL Multiple Joins Example

Data Present in the login table is as shown below

In this example, we will show you, How to use the SQL Multiple Joins in one SELECT Statement. For this, We are going to use the Inner Join, and Left Join on tables that we have shown above.

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

OUTPUT

SQL Join Where Clause

You can also use Where Clause along with the JoinsMax. In this example, 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 and 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

OUTPUT

SQL Join Where Clause Example 2

This is another example to show the Where Clause. Here, we used Left Join to Join the Employee 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'

OUTPUT

SQL Join Statement with Where Clause and IS NOT NULL Example

In this 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

OUTPUT

SQL Joins Where Example 4

In this example, we used the 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

OUTPUT

SQL Joins and Where Example 5

In this example, 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

OUTPUT

SQL Joins Order by and Top Clauses

In SQL Server, you can use the Joins along with Top Clause and Order By Clause.

In this example, we used 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

OUTPUT

SQL Join Top and Order By Clause Example 2

This is another Sql Server join example to experience 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

OUTPUT