Save Big on Cyber Monday! Up to 40% Off
ends in   {{days}}
Days
{{timeFormat.hours}}
:
{{timeFormat.minutes}}
:
{{timeFormat.seconds}}

Types of SQL JOINs Explained With Examples

Relational databases like SQL Server store data across multiple tables that are linked by common key values. As a result, it's often necessary to retrieve records from two or more related tables based on specific conditions. To accomplish this, SQL Server uses the JOIN clause.

A JOIN is an SQL clause that allows you to query and retrieve data from multiple tables based on logical relationships between them. In simple terms, it tells SQL Server how to use data from one table to find matching rows in another.

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete
Free edition available

Basic SQL JOIN types

SQL Server supports several JOIN types, including INNER JOIN, SELF JOIN, CROSS JOIN, and OUTER JOIN.

OUTER JOINs can further be divided into LEFT OUTER JOINs, RIGHT OUTER JOINs, and FULL OUTER JOINs.

Each join type defines the way two tables are related in a query.

  • SQL INNER JOIN combines all rows that have matching values in two or more tables.
  • SQL LEFT OUTER JOIN combines all rows from the left table and only matching rows from the right table.
  • SQL RIGHT OUTER JOIN combines all records from the right table and only matching rows from the left table.
  • SQL SELF JOIN joins the table to itself for comparing rows within the same table.
  • SQL CROSS JOIN combines each row of the first table with each row of the second table.
Basic SQL JOIN types

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete
Free edition available

INNER JOIN

INNER JOIN returns only the records (or rows) that have matching values in both tables. It is used to retrieve data that exists in both related tables.

Inner SQL JOIN

We want to extract data from the Sales.SalesOrderDetail (aliased sod) and Production.Product (aliased p) tables. INNER JOIN matches records based on the related columns from both tables.

Working with Inner JOINs in SQL Complete

OUTER JOIN

OUTER JOINs return a result that includes both matching and unmatching rows from left and right tables. The FULL OUTER JOIN returns a result that includes all rows from both left and right tables. For unmatching rows, it returns NULLs.

SQL Server OUTER JOIN

If no matching rows exist for the row in the left table, the columns of the right table will have nulls. Correspondingly, the column of the left table will have nulls if there are no matching rows for the row in the right table.

How to work with Outer JOINs in SQL Complete

LEFT OUTER JOIN

LEFT OUTER JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches in the right table, the result will still include the left table's rows, with NULL values for the columns from the right table.

LEFT OUTER JOIN in SQL

Let us retrieve a list of all last names from the Person table along with the job titles for those who are employees. For that, we join the Person.Person and HumanResources.Employee tables using LEFT OUTER JOIN. The query returns all last names and displays the job titles for employees. If a person is not an employee, the JobTitle column contains NULL.

Create LEFT OUTER JOINs with the help of SQL Complete

RIGHT OUTER JOIN

RIGHT OUTER JOIN operates on the same principle as the LEFT OUTER JOIN, but in reverse. It returns all records from the right table and the matching records from the left table. If a row in the right table has no corresponding match in the left table, the result will still include the right table's row, with NULL values for the columns from the left table.

RIGHT OUTER JOIN in SQL Server
How to create RIGHT OUTER JOINs in SSMS

SELF JOIN

SELF JOIN allows a table to be joined with itself. In this type of join, each row is combined with other rows in the same table, as if the table were duplicated. However, the table isn't physically copied as SQL uses table aliases to create distinct references to the same table.

What is SELF JOIN in SQL

Suppose we want to retrieve a list of all sales territories along with the salespeople assigned to them from the Sales.SalesPerson table. SELF JOIN allows us to link each salesperson to their respective territory by comparing rows within the same table.

How to make SELF JOINs with SQL Complete

CROSS JOIN

CROSS JOIN in SQL, also known as a Cartesian join, combines each row from the first table with every row from the second table. The result is a set of all possible combinations between the two tables. Unlike other types of joins, a CROSS JOIN does not require a joining condition.

CROSS JOIN in SQL Server

For example, if you want to obtain all possible combinations of product sizes and colors, a CROSS JOIN is the ideal choice for this task.

Creating CROSS JOINs in SQL Complete for SSMS and VS

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete
Free edition available

SQL JOINs summary

JOIN Type Relationship Description Example Use Case
INNER JOIN Returns records with matching values in both tables Find customers who placed orders
LEFT JOIN Returns all records from the left table and matching records from the right table List all employees and their departments, even if some employees are not assigned
RIGHT JOIN Returns all records from the right table and matching records from the left table List all departments and their employees, even if some departments have no employees
FULL OUTER JOIN Returns all records when there is a match in either table Combine customer and supplier lists, showing all contacts
CROSS JOIN Returns the Cartesian product of both tables Generate all possible product and category combinations
SELF JOIN Joins a table to itself Find employees who report to the same manager

Ready to get started?

See how to use JOINs in the SELECT statements

What dbForge SQL Complete delivers

  • Recommended full joining conditions after the JOIN keyword
  • Recommended joining conditions to follow after the ON keyword
  • Context-based prompts for all potential table join combinations
  • Significant reduction in query development time
  • Enhanced code quality through optimal join construction
  • No need for memorizing table and column names
dbForge SQL Complete

dbForge SQL Complete

An advanced solution with excellent autocompletion capabilities for professional SQL database development, management, and administration. Available as an add-in for Visual Studio and SSMS.

Download a FREE two-week trial and see how it helps you save time in day-to-day SQL development and database management.

Download Buy now

Frequently Asked Questions

What is meant by JOINs in SQL?

Joining in SQL means retrieving data from two or more than two tables based on a common field. In other words, JOINs combine data from multiple tables in a result table based on a related column between those tables.

Why do we use JOINs in SQL?

The purpose of JOINs in SQL is to access data from multiple tables based on logical relationships between them. JOINs are used to fetch data from database tables and represent the result dataset as a separate table.

How many types of JOINs are there in SQL?

There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN. OUTER JOINs can be further divided into three subtypes: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

What are the 3 most popular types of JOINs in SQL explained with examples?

INNER JOIN
Suppose, we have two tables: Customers (with CustomerID, Name, and CountryID columns) and Orders (with OrderID, CustomerID, and OrderDate columns). Using the INNER JOIN we can create a result table containing the CustomerID, Name, OrderID, and OrderDate columns. In such a simple way, we will be able to see who and when created an order. The tables will be matched based on the common CustomerID column, and the unmatched row won't get into the result dataset.

LEFT OUTER JOIN
Suppose we have the Products and Orders tables that have a common ProductId column. With the help of the LEFT OUTER JOIN we can combine the two tables in a result table that will contain all the rows from the Products table and in case, no records match from the Orders table, the NULL values will be shown in the OrderID column. In such a simple way, we will be able to see which products have not been ordered yet.

RIGHT OUTER JOIN
The RIGHT OUTER JOIN works similarly to the LEFT OUTER JOIN but in the opposite direction. Let's consider the Vendors and Orders tables. Using the RIGHT OUTER JOIN we can retrieve records for all Vendors showing their Purchase Orders. In case a vendor hasn't placed any orders yet, NULLs will be displayed in the PurchaseOrderId column.

What is the difference between UNION and JOIN in SQL Server?

Both JOIN and UNION serve to combine data; however, their distinction lies in the manner in which they execute this operation. You can read the full explanation on this page.

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete
Free edition available