This section explains how to write an SQL query to Join two or more tables using the SQL Joins with an example. There are six types of SQL Joins, and they are:
- Inner Join: Also called 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.
- Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table and the right table.
- Left Outer join: Or called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).
- 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.
- Self Join: It is used to Join the table with itself. We can use this technique to calculate Running Total etc.
- 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
It is one of the Frequently Asked Question. For this SQL Joins query example, We use the below data
Data present in the Department Table is:
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