There are six types of SQL Joins, and they are:
- 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.
- Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table, and right table.
- 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).
- 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
In this article we will show you, How to write a SQL Query to Join two or more tables using the SQL Joins with 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:
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 had already explained the Inner Join in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--SQL Server JOINS :- Example for INNER JOIN 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 Example – Left Join
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 shown above. We had already explained the Left Join in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--SQL Server JOINS :- Example for LEFT JOIN, or LEFT OUTER JOIN USE [SQLTEST] GO 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 shown above. We had already explained the Right Join in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--SQL Server JOINS :- Example for RIGHT JOIN, or RIGHT OUTER JOIN USE [SQLTEST] GO 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. For this, We are going to use the tables that we shown above. We had already explained the Full Join in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--SQL Server JOINS :- Example for FULL JOIN USE [SQLTEST] GO 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 in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--SQL Server JOINS :- Example for SELF JOIN USE [SQLTEST] GO 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 Joins 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. For this, We are going to use the tables that we shown above. We had already explained the Cross Join in our previous article so, I suggest you to refer them for better understanding
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--SQL Server JOINS :- Example for CROSS JOIN USE [SQLTEST] GO 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 Joins Example – Multiple Joins
In this example we will show you, How to use the Multiple Joins in one SELECT Statement. For this, We are going to use the Inner Join, and Left Join on tables that we shown above.
Data Present in the LogInTable is as shown below
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--SQL Server JOINS :- Example for Multiple JOIN 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
Thank You for Visiting Our Blog
Hi Suresh,
Very good article about Joins.
Please post an article in Dynamic SQL and derived tables in SQL Server if possible.
Regards,
Venkat.
Thank You Venkat
We will do as soon as we can