The most important, rather powerful feature of SQL Server is Joins, which helps you to combine 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.
SQL JOINS Example
This section explains how to write a SQL query to Join two or more tables with an example. The following are some of the frequently asked questions.
How Many Joins in SQL Server?
There are six types of join clause.
- Inner join or simply join is the default type most used one in real-time.
- Full Outer or Full.
- Left Outer or Left.
- Right Outer or Right.
- Cross
- Self
Why Joins are used for?
They are helpful in the combine or select columns data from more than one table because the database stores our data in separate tables using foreign key. Therefore, we must select data from at least two tables for any simple query result, even if you’re selecting customers records.
The visual representation of the SQL Server Inner, Full Outer, Left Outer, Right Outer, Self clause, and Cross joins are

For this SQL Joins statement example, We use two tables Employees and Department

Department

What is SQL Inner Join example?
Inner 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. This example shows how to write inner.
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

What is SQL Left outer Join?
The Left or Left Outer join returns all the rows present in the Left table and matching rows from the right table (if any). This example shows how to write a Left Outer or Left to return the result set. So, refer Left article in the SQL.
SELECT Emp.[EmpID] ,Emp.[FirstName] ,Emp.[LastName] ,Emp.[Education] ,Emp.[YearlyIncome] ,Emp.[Sales] ,Dept.[DepartmentName] FROM [Employees] AS Emp LEFT JOIN [Department] AS Dept ON Emp.DeptID = Dept.DeptID

What is SQL Right Join?
Right Outer join returns matching rows from the left table (if any), and all the rows present in the Right table.
Let us see how to write a Right Outer or Right join with condition. We had already explained how the Right outer work.
SELECT Emp.[EmpID] ,Emp.[FirstName] ,Emp.[LastName] ,Emp.[Education] ,Emp.[YearlyIncome] ,Emp.[Sales] ,Dept.[DepartmentName] FROM [Employees] AS Emp RIGHT JOIN [Department] AS Dept ON Emp.DeptID = Dept.DeptID

What is Full join in SQL Server?
Full Outer is also called the Full Join. It retrieve data from all the rows present in both the Left table and the right table. This example will show you how to write a Full Outer or Full.
SELECT Emp.[EmpID] ,Emp.[FirstName] ,Emp.[LastName] ,Emp.[Education] ,Emp.[YearlyIncome] ,Emp.[Sales] ,Dept.[DepartmentName] FROM [Employees] AS Emp FULL JOIN [Department] AS Dept ON Emp.DeptID = Dept.DeptID

What is Self Join in SQL Server?
SQL Self is used to combine the table with itself. We can use this technique to calculate Running Total etc. In this example, we show how to write a Self.
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

What is Cross Join in SQL Server?
The cross is used to return the Cartesian product of two tables. It means, Number of rows in Employees multiplied by Number of rows in Department. This example shows how to find a Cartesian Product using Cross.
SELECT Emp.[EmpID] ,Emp.[FirstName] ,Emp.[LastName] ,Emp.[Education] ,Emp.[YearlyIncome] ,Emp.[Sales] ,Dept.[DepartmentName] FROM [Employees] AS Emp CROSS JOIN [Department] AS Dept

Can we do Multiple Joins?
Yes. A single query can have multiple ones of the same type or different types. For instance, we can use two left outers on three tables or two inner ones. It all depends on the data that we need.
Data Present in the login

In this example, we use the SQL Multiple Joins in one SELECT Statement. We use the Inner and Left on above records.
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

Where Clause
You can also use Where Clause along with this to restrict the records returned by it. We are using where clause to display the Employees whose yearly income is greater than or equal to 60000. For this, we used Inner to connect the Employee & Department. Next, we used Where Clause with a condition
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

It is another example to show the SQL Joins in Where Clause. Here, we used the Left to connect the Emp and Department tables. Next, we used Where condition to display the employees whose Education is not Bachelors
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'

Where Clause and IS NOT NULL Example
We used the Right to combine the Employee and Department. Next, within the Where clause of the SQL Seerver Right Join, we used IS NOT NULL to display the records whose ID is not NULL.
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

Used Full to combine 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
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

We used Cross along with Where Clause to attach multiple tables along with condition.
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 this along with Top Clause and Order By. In this example, we used the Inner to connect Employee and Department. 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.
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 combine data from multiple tables.
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

Comments are closed.