Overview of SQL Joins in SQL Server

The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.

The aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.

What Is a SQL Join?

A SQL Join is a special form of generating meaningful data by combining multiple tables relate to each other using a “Key.” Typically, relational tables must be designed with a unique column and this column is used to create relationships with one or more other tables. When you need a result-set that includes related rows from multiple tables, you’ll need to use SQL join on this column

The various types of joins are as follows

1. SQL inner join
   1. Equi join
   2. Non-equi join (Theta join)
2. SQL outer join
   1. SQL left join or left outer join
   2. SQL right join or right outer join
   3. SQL full join or full outer join
3. SQL cross join
4. SQL self join

Note: The keyword outer is optional. It means you can specify the keyword “outer” or not makes no difference to the query execution.

For example,

SQL Inner Join

The simplest and most common form of a join is the SQL inner join the default join-types of most of the database management systems. It’s the default SQL join you get when you use the join keyword by itself.

The result of the SQL inner join includes rows from both the tables where the join conditions are met.

Syntax:

SELECT ColumnList from LeftTable L
INNER join  RightTable R
ON L.Column=R.Column

Note: It is very easy to visualize a join query as a Venn diagram, where each of the tables is represented by intersecting shapes. The intersection of the shapes, where the tables overlap, are the rows where a condition is met. Unique columns (ID) are often used for this purpose, where the condition to be met is matching the ids of rows.

Equi Join:

An equi join is the most common form of SQL inner join used in practice. If the join contains an equality operator e.g. =, then it’s an equi-join.

The following example returns all matching state names and stateProvinceIDs.

SELECT DISTINCT A.StateProvinceID,S.Name
FROM Person.Address A
inner join Person.StateProvince S
On A.StateProvinceID=S.StateProvinceID

Theta Join (Non-equi join):

In general, this Theta join is used to specify operators or conditions (the ON clause in SQL). In practice, this is a rarely used join type. In most cases, the join will use a non-equality condition e.g. >

SELECT p1.FirstName, p2. FirstName             
FROM PErson.Person p1                                          
INNER join PErson.Person p2                                     
ON len(p1.FirstName) > len(p2.FirstName);

SQL Self Join

A SQL Self join is a mechanism of joining a table to itself. You would use a self join when you wanted to create a result set joining records in the table with some other records from the same table.

For a SQL self join example, consider an Employee table where managers are listed because they are also employees, and we would like to take a look at a result set that returns all of the employees and indicating who their managers are

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno

SQL Cross Join

A CROSS join returns all rows for all possible combinations of two tables. It generates all the rows from the left table, which is then combined with all the rows from the right table. This type of join is also known as a Cartesian product(A*B).

For example, if the left table has 100 rows and the right table has 100 then the cross join result will yield 10,000 rows.

SELECT e.BusinessEntityID, d.Name AS Department  
FROM HumanResources.Employee AS e  
CROSS join HumanResources.Department AS d

SQL Outer Join

On joining tables with a SQL inner join, the output returns only matching rows from both the tables. When using a SQL outer join, not only it will list the matching rows, it will also list the unmatched rows from the other tables.

A SQL left outer join will return all the records from the left table in the join clause, regardless of matching records in the right table. The left SQL outer join includes rows where the condition is met plus all the rows from the table on the left where the condition is not met. Fields from the right table with no match will be displayed as null values.

Syntax:

SELECT ColumnList from LeftTable L
LEFT join  RightTable R
ON L.Column=R.Column
Where R.Column is NULL

The following example joins two tablesProduct and SalesOrderDetail on ProductID and preserves the unmatched rows from the left table. The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and not ordered, appear in the result set.

SELECT p.Name, so.SalesOrderID  
FROM Production.Product  p  
LEFT OUTER join Sales.SalesOrderDetail so 
ON p.ProductID = so.ProductID  
ORDER BY p.Name ;

A right outer join will return all the records in the right table in the join clause, regardless of matching records in the left table. Using the right SQL outer join includes all the rows from the table on the right. The right SQL outer join is considered a special case and many databases don’t support right joins. Generally, a SQL right join can be rewritten as a SQL left join by simply changing the order of the tables in the query. In this instance, fields from the left table with no match will display null values

Syntax:

SELECT ColumnList from LeftTable L
RIGHT join  RightTable R
ON L.Column=R.Column
Where L.Column is NULL

The following example joins two tables on TerritoryID(SalesTerritory) and preserves the unmatched rows from the right table(SalesPerson). The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each table. All salespersons appear in the result set, whether or not they are assigned a territory.

SELECT s.Name AS Territory, p.BusinessEntityID  
FROM Sales.SalesTerritory  s   
RIGHT OUTER join Sales.SalesPerson p  
ON s.TerritoryID = p.TerritoryID ;

A SQL outer join, as you might expect by now, will return all the rows in both tables. When rows don’t have a match in one of the tables, the field will display a null value. A full SQL outer join combines the effects of the SQL left joins and SQL right joins. Many databases do not support the implementation of full SQL outer joins

Syntax:

SELECT ColumnList from LeftTable L
FULL OUTER join  RightTable R
ON L.Column=R.Column

The following example returns the name of the product name any corresponding sales orders in the SalesOrderDetail table from the AdventureWorks2014 database. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.

SELECT p.Name, s.SalesOrderID  
FROM Production.Product p
FULL OUTER join Sales.SalesOrderDetail s  
ON p.ProductID = s.ProductID  
ORDER BY p.Name ;

Summary

In this article, we’ve discussed most of the important aspects of SQL Joins. We’ve also demonstrated a few quick examples and samples of how we can pull data from related tables from the Adventureworks2016 database and how those tables actually get that relationship through the use of those keys using SQL joins.

That’s all for now. I hope you enjoyed this article on SQL Joins. Feel free ask any questions in the comments below, and happy joining!