This article will provide an overview of the SQL Join and cover all of the SQL join types including inner, self, cross and outer. For inner joins we’ll be discussing Equi and Theta joins.
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 a 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 SQL join types are as follows
-
SQL inner join
- Equi join
- Non-equi join (Theta join)
-
SQL outer join
- SQL left join or left outer join
- SQL right join or right outer join
- SQL full join or full outer join
- SQL cross join
- 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 join types
SQL inner join
The simplest and most common form of a join is the SQL inner join the default of the SQL join types used in most 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:
|
1 2 3 |
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.
|
1 2 3 4 |
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 a Theta join used to specify operators or conditions (the ON clause in SQL). In practice, this is a rarely used SQL join types. In most cases, the join will use a non-equality condition e.g. >
|
1 2 3 4 |
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
|
1 2 3 4 |
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.
|
1 2 3 |
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:
|
1 2 3 4 |
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.
|
1 2 3 4 5 |
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:
|
1 2 3 4 |
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.
|
1 2 3 4 |
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:
|
1 2 3 |
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.
|
1 2 3 4 5 |
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 and covered a variety of SQL join types. 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 Join types. Feel free ask any questions in the comments below.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021