Different types of Joins in SQL Server

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 tables contain 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 related 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

• 1.SQL inner join
• • Equi join
  • Non-equi join (Theta join)
• 2.SQL outer join
• • SQL left join or left outer join
  • SQL right join or right outer join
  • 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 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:

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.

Theta join (Non-equi join):

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

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

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.

Learn Experts Curated SQL Server Training & Become JOB READY

• Instructor-led Sessions
• Real-life Case Studies
• Assignments

Explore Curriculum

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:

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.

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:

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.

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:

The following example returns the name of the product name and 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.

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 Adventure works 2016 database and how those tables actually get that relationship through the use of those keys using SQL joins.