SQL JOINS

The most important 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.

In a relational database management system, we store the standard information in a master table and its transactions in child tables. The parent and child tables are connected using primary and foreign keys (normalization). One fundamental task in an RDMS is to combine data from multiple tables and retrieve records to get complete information or to extract meaningful insights. SQL Joins will do this work for you!

In this blog post, we will explain the concept of SQL joins, syntax, how many types of joins are available, joining multiple tables, and the difference between Join and inner Join.

Understanding the basics of SQL Joins

SQL Server Joins are helpful in the combine or selecting columns of data from two or more tables 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. You must specify the SQL join conditions using a common column to establish the relationship between tables.

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

How Many Joins in SQL Server?

SQL provides six types of joins to handle various scenarios. Each join type is distinct from the others and offers unique functionality. Here’s a brief overview of common SQL join types:

1. Inner join or join is the default type most used in real-time. It retrieves matching or common records from both tables.
2. Full Outer Join or Full Join retrieves all matching and non-matching rows from both tables. If no matching values exist, NULL values will return.
3. Left Outer Join (Left Join) retrieves all records from the left or first table and the matching records from the second or right table. It returns NULL values if no matching values exist in the right table.
4. Right Join (Right Outer Join) retrieves all records from the right table and the matching records from the left table. It returns NULL values if no matching values exist in the left table.
5. Cross Join returns the Cartesian product of both tables, combining each row from the first table with every row from the second table.
6. Self Join connects to itself to perform row-wise calculations.

SQL Joins Syntax

The syntax of the common SQL joins is as follows. For the Self Join, Table1 and Table2 will be the same; you must use an alias name. You can use either of the shown joins to get the records. For instance, remove the remaining names to get the cross join result set.

SELECT Table1.Column(s), Table2.Column(s), .....
FROM Table1
INNER JOIN | LEFT JOIN | RIGHT JOIN | CROSS JOIN | SELF JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

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

Department

Different Types of SQL JOINS Examples

In order to perform any SQL Join, both tables should have a common column. Otherwise, we can’t perform joins. We use the common column as the condition to join tables. If you observe the above two tables, there is a DeptID integer column in the Employees and Department tables, and we use this column to perform joins.

This SQL 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 a fundamental join type, and it is also called the Join. It returns the matching rows present in both the left and right tables. Otherwise, it returns zero records. Generally, we use SQL inner Join to retrieve the common records in multiple tables. This example shows how to write inner Join.

This query will join the “Employees” and “Department” tables based on the “DeptID” column and returns the First Name, Last Name, Education, Department Name, Yearly Income, and Sales. Although there are 15 rows in the employee table, the SQL inner Join has written only 11 records because the DeptID of EmpID 10, 11, 14, and 15 are NULLS.

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 Left Outer Join in SQL Server?

The SQL Left join, also known as a Left Outer join, returns all the Left-hand side table rows and matches rows from the right table (if any). If there are no matching values in the right table, fill the right table columns with NULL values.

This example shows how to write a Left Outer in the SQL Server to get all the Employees from the Employee table and their corresponding (matching) Department names. There are no related Department Names for EmpID 10, 11, 14, and 15, so these rows fill with NULL values.

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?

The SQL Server Right Join, also known as a Right Outer join, returns all the rows in the Right-hand side table and the matching rows from the left table (if any). If there are no matching records in the left table, fill the left table columns with NULL values.

It ensures that all the right table rows should include in the final result set, regardless of whether there are matching records in the left table. Let us see how to write a Right Outer Join with conditions. 

This query returns all the department names and matching employees. There are no corresponding employees for Module Lead and Team Lead department names, so they fill with NULLS. There is a complete NULL row at the bottom (14th record) because there is a NULL department name for DeptID 9, and it has no matching record in the employee.

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 SQL Server Full Join?

The SQL Server Full Outer Join is also called the Full Join. It retrieves data from all the rows in both the Left and right tables and does not consider whether there are matching values. It fills with NULL values for all the not matching records in both tables. 

Full Join is helpful when we need a complete view of the data from both tables without considering the matches. This example will show you how to write a Full Outer Join.

This query returns all the records from the “Employees” and “Department” tables. It means Employees without Department Names and Department Ids without Employees. As you can see in the result set, it has produced 18 rows. There are 15 rows in the employee table and three extra rows in the department table. They are Module Lead, Team Lead, and NULL. 

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 SQL Self Join?

The SQL Server Self join is used to combine the table with itself. It is helpful when you want to form a relationship between records within the same table. To work with Self Join, you must use aliases name for tables to differentiate and consider the same table as two different entities. For instance, we can use the Self Join technique to calculate Running Total, etc. 

The condition in Self Join decides how the rows of the same table should match. In this example, we used the Self Join to connect the Employees table to itself. For this, we used the alias names Emp and NewEmp to make the same table as two distinct entities.

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 Join helps return the Cartesian product of two tables. It combines each row in the first table with every record in the second. You don’t need any condition to work with one, as it generates all combinations. 

This SQL Cross Join example shows how to find a Cartesian Product. It combines both Employees and Department tables and returns every employee paired with each department name. The result set will be the number of rows in Employees multiplied by the number of rows in the Department. 

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept

The Ambitious column is one of the most common errors you might get while working with SQL Joins. Select the column name using the Alias table names to avoid the error. By this, the Server identifies which table the column belongs to.

Advanced SQL Join Concepts

Non-Equi Join, Semi-joins, and anti-joins are the advanced concepts you should practice to empower your skills. Regular Joins allow you to combine data from different tables, whereas these advanced ones provide powerful filtering capabilities.

Non-Equi Join

The SQL Non-equi join type uses comparison operators, such as <, >, <=, >=, <>, !=, !<, !> in the join condition. Along with the Join type and the common column to establish the relationship, it uses the above-mentioned comparison operators. Non-Equi Joins help filter the result set further.

The below query Joins Employees, Department tables, and the Non-Equi Join directs the Server to display the Employees whose sales are greater than or equal to 500.

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 AND Emp.[Sales] >= 500

Semi-Join

Semi-join type returns the records from one table only. These records should have a matching record in the second table. SQL Semi-joins are helpful when you want to display the left table records whose values match with the right table. It performs a simple comparison and acts as a filter to display a particular set of rows.

The below Semi-join query displays all the Employees with the matching Department name, which must be Software Engineer. To implement this Semi-join example, we used the EXISTS operator.

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
FROM [Employees] AS Emp
WHERE EXISTS (
    SELECT 1 FROM
    [Department] AS Dept
    WHERE Emp.DeptID = Dept.DeptID
    AND Dept.[DepartmentName] = 'Software Developer'
)

Anti-Join

Anti-join is the exact opposite of a semi-join. It means the SQL Server Anti-Join type returns the rows from the left table that do not have matching rows in the right table. To implement this Anti-join example, we used the NOT EXISTS operator.

The below Anti-join query returns all the Employees whose Department name is not Software Engineer.

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
FROM [Employees] AS Emp
WHERE NOT EXISTS (
    SELECT 1 FROM
    [Department] AS Dept
    WHERE Emp.DeptID = Dept.DeptID
    AND Dept.[DepartmentName] = 'Software Developer'
)

Can we do Multiple Joins in SQL?

Yes. A single SQL query can have multiple joins of the same or different types. For instance, we can use two left outer joins on three tables or two inner ones. It all depends on the data that we need.

In real-time, joining multiple tables to retrieve the desired information is standard. For example, we need the Adventure Works DW database product information. In that case, we must join the Product Category, Product SubCategory, and DimProduct table. If you need Internet Sales of those products, you must join the Fact Internet Sales Table. 

Joining multiple SQL tables allows you to combine data from different tables using a common column to see the complete information. In the above case, you can use ProductSubcategoryKey to join DimProduct and DimProductSubcategory. Similarly, use ProductCategoryKey to connect DimProductSubcategory and DimProductCategory. To add FactInternetSales, use ProductKey from FactInternetSales and DimProduct.

Here are the steps to join multiple tables in SQL

1. Identify the Tables that contain the required information.
2. Check the relationship between those tables.
3. Check the primary key and foreign key columns.
4. Identify the common columns in between those tables. There should be a common column.
5. Identify the required columns to include in the SELECT Statement.
6. Based on the requirement, you must decide which Join type is suitable for connecting any two tables. For example, you may use an inner join to connect DimProductCategory, DimProductSubcategory, and DimProduct. You may consider left or inner join in establishing a connection between DimProduct and FactInternetSales.
7. You can also use ORDER BY, WHERE, and TOP Clauses in a combination of joins to filter and sort the data.

Data Present in the login

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

Joining Five Tables in SQL

In SQL Server, you can join five tables to select data from five different tables for your insights or reports. Before writing the query, you must identify the relationships between the tables, common columns, and the join type. Apart from this, you must follow all the steps mentioned in the join multiple tables section.

The below SQL query will join the five tables from the Adventure Works DW database to see the customer product information with the sales report. They are DimProductCategory, DimProductSubcategory, DimProduct, DimCustomer, and FactInternetSales. Here, we used the inner Join to connect tables. However, you can use any join as per the business needs.

SELECT ProdCat.EnglishProductCategoryName, 
       ProdSubCat.EnglishProductSubcategoryName, 
       Prod.EnglishProductName, 
       Prod.Color, 
       Cust.FirstName, 
       Cust.LastName, 
       Cust.Gender, 
       Cust.YearlyIncome, 
       Fact.OrderQuantity, 
       Fact.TotalProductCost, 
       Fact.SalesAmount
FROM DimProductSubcategory AS ProdSubCat
INNER JOIN DimProduct AS Prod ON 
	ProdSubCat.ProductSubcategoryKey = Prod.ProductSubcategoryKey 
INNER JOIN DimProductCategory  AS ProdCat 
	ON ProdSubCat.ProductCategoryKey = ProdCat.ProductCategoryKey 
INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey 
INNER JOIN DimCustomer AS Cust ON Fact.CustomerKey = Cust.CustomerKey

Can we use the WHERE clause with JOIN?

Yes, you can also use the Where Clause along with the SQL Server Joins to restrict the records returned by it. The Join type decides how many records have to return, and the WHERE clause allows you to apply extra filtering.

In this example, we used Inner Join to connect the Employee and Department table. So, it returns the common rows from both. Then, the WHERE clause will filter the result set to display the Employees whose yearly income is greater than or equal to 60000. 

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 and WHERE Example 2

It is another example to show the SQL 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'

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

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

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 Joins with Order by and Top Clauses

You can also use this along with Top Clause and Order By. If you use the ORDER BY clause in conjunction with Join, the combined data will sort in Ascending or Descending. If you use TOP Clause and any Join type combination, the query will return the top 10 or 50 records based on the given value.

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

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

SQL JOIN FAQs