SQL JOINS

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 Server Joins, there is no concept of data normalization because we have to store the entire data in one table.

How Many Joins in SQL Server?

There are six types of join clauses.

  • 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 SQL Joins are used for?

SQL Server Joins are helpful in the combine or selecting columns of data from more than one table because the database stores our data in separate tables using a foreign key. Therefore, we must select data from at least two tables for any simple query result, even if you’re selecting customer records.

Visual Representation of Joins

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

SQL JOINS Diagrammatic Representation

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

Left Table 1

Department

Right Table 2

SQL JOINS Example

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

What is the SQL Server Inner Join example?

The Inner Join is also called the Join. It returns the rows present in both the left and right tables 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
SQL Server Inner Join Example 3

What is Left Outer Join in SQL Server?

The Left or Left Outer join returns all the rows present in the Left table and matches 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
Left Outer Example 4

What is SQL Right Join?

The 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 with conditions. 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
SQL Server Right Outer Join Example 5

What is Full join?

The SQL Server Full Outer is also called the Full Join. It retrieves 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 join 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
Full Outer Example 6

What is Self Join?

The SQL Server Self join 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
SQL Server Self Join Example 7

What is Cross Join in SQL Server?

The cross join is useful to return the Cartesian product of two tables. It means the Number of rows in Employees is multiplied by the number of rows in the 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
Cross Example 16

Can we do Multiple Joins?

Yes. A single SQL query can have multiple joins 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

Third Table Data

In this example, we use the SQL Server Multiple Joins in one SELECT statement. We use the Inner and Left on the 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
SQL Multiple Joins Example 9

Join and Where Clause

You can also use the Where Clause along with the SQL Server Joins to restrict the records returned by it. For example, we use the 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 the 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
SQL JOIN WHERE Clause Example 17

It is another example to show the Joins in the Where Clause. Here, we used the Left to connect the Emp and Department tables. Next, we used the Where condition to display the employees whose Education is not a bachelor’s.

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'
Left Outer Where Clause Example 10

Join 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 Server 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
SQL JOIN Where IS Not Null Example 11

Used SQL Server Full Join 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 are 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
Full Where Clause and IS NOT NULL Example 12

We used SQL Server Cross join along with the Where Clause to attach multiple tables along with conditions.

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 Server Cross Join with Where Clause Example 13

SQL Server Joins with 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, the 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
SQL Server JOIN Order By Top Clause Example 14

This is another example of experiencing the top and order by along with combining 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
Order By Clause

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.