Types of SQL JOINs Explained With Examples

Master JOINs with dbForge SQL Complete

As you progress as a database user, you'll continually need to fetch and combine data from more than one table using SQL Server JOINs. At this point, dbForge SQL Complete comes to the aid. Its code completion works excellently even for complex JOIN statements.

dbForge SQL Complete suggests a full JOIN clause based on foreign keys or conditions based on column names. These suggestions are available after the JOIN and ON keywords.

Moreover, dbForge SQL Complete can prompt a complete SQL JOIN statement when you combine tables based on foreign keys. Additionally, you can select a JOIN statement from the prompt list manually for a specific JOIN operation.

Let's have a look at various JOIN types created using dbForge SQL Complete.

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 the matching rows from the right table.
• SQL RIGHT OUTER JOIN combines all records from the right table and only the 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.

INNER JOIN

An 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.

The simplest query example is:

SELECT
  c.Name
 ,o.Product
FROM Customers c
INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID;

• When to use: Use SQL INNER JOIN only when you need rows that exist in both tables.
• Pitfalls: The query excludes unmatched rows, which can cause "missing data" issues. If you need to preserve unmatched rows from one table, use LEFT JOIN instead.
• Example scenario: Compile the list of customers who placed orders, including customer names and product names.

SELECT 
    p.FirstName + ' ' + p.LastName AS CustomerName,
    pr.Name AS Product
FROM Sales.Customer c
INNER JOIN Person.Person p
    ON c.PersonID = p.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
INNER JOIN Sales.SalesOrderDetail sod
    ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product pr
    ON sod.ProductID = pr.ProductID;

OUTER JOIN

An OUTER JOIN returns a result that includes both matching and unmatching rows from the left and right tables. If no matching rows exist for the row in the left table, the columns of the right table will contain NULLs. Correspondingly, the columns of the left table will contain NULLs if there are no matching rows for the row in the right table.

The simplest query example is:

SELECT c.Name, o.Product 
FROM Customers c 
FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;

• When to use: Use the FULL OUTER JOIN when you need a complete overview of all records, matched or not.
• Pitfalls: Large result sets filled with NULLs can make analysis harder and cause low performance. To avoid this, use the FULL JOIN query only when absolutely needed; otherwise, stick to LEFT JOIN or INNER JOIN for efficiency.
• Example scenario: Retrieve the list of all products in orders even if the customer information is missing.

SELECT 
    p.FirstName + ' ' + p.LastName AS CustomerName,
    pr.Name AS Product
FROM Sales.Customer c
FULL OUTER JOIN Person.Person p
    ON c.PersonID = p.BusinessEntityID
FULL OUTER JOIN Sales.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
FULL OUTER JOIN Sales.SalesOrderDetail sod
    ON soh.SalesOrderID = sod.SalesOrderID
FULL OUTER JOIN Production.Product pr
    ON sod.ProductID = pr.ProductID;

LEFT OUTER JOIN

A 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.

The simplest query example is:

SELECT c.Name, o.Product 
FROM Customers c 
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

• When to use: Use the SQL LEFT OUTER JOIN when you want to include all rows from the left table, even without matches.
• Pitfalls: This query may produce NULL values for missing matches. Reports or calculations may break if NULLs aren't handled. To avoid this, use COALESCE() or ISNULL() to replace NULLs with default values, e.g., COALESCE(o.Product, 'No Orders').
• Example scenario: Get the list of all customers, even if they have not placed any orders (in that case, the Product column for the customer will contain NULL).

SELECT 
    p.FirstName + ' ' + p.LastName AS CustomerName,
    pr.Name AS Product
FROM Sales.Customer c
LEFT JOIN Person.Person p
    ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
LEFT JOIN Sales.SalesOrderDetail sod
    ON soh.SalesOrderID = sod.SalesOrderID
LEFT JOIN Production.Product pr
    ON sod.ProductID = pr.ProductID;

RIGHT OUTER JOIN

A 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 that row, but with NULL values.

The simplest query example is:

SELECT c.Name, o.Product 
FROM Customers c 
RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID;

• When to use: SQL RIGHT OUTER JOINS are helpful in scenarios where you want to include all rows from the right table, even if there's no match.
• Pitfalls: This query is less intuitive than LEFT JOIN. Developers may confuse the two. It is advisable to standardize on LEFT JOIN in your team and reorder tables when needed—it makes queries easier to read.
• Example scenario: Return the list of all products that appear in orders. If the person information is missing, CustomerName will be NULL.

SELECT 
    p.FirstName + ' ' + p.LastName AS CustomerName,
    pr.Name AS Product
FROM Person.Person p
RIGHT JOIN Sales.Customer c
    ON p.BusinessEntityID = c.PersonID
RIGHT JOIN Sales.SalesOrderHeader soh
    ON c.CustomerID = soh.CustomerID
RIGHT JOIN Sales.SalesOrderDetail sod
    ON soh.SalesOrderID = sod.SalesOrderID
RIGHT JOIN Production.Product pr
    ON sod.ProductID = pr.ProductID;

SELF JOIN

A SELF JOIN allows joining a table 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, it does not copy the table physically because SQL uses table aliases to create distinct references to the same table.

The simplest query example is:

SELECT e.Name AS Employee, m.Name AS Manager 
FROM Employees e 
LEFT JOIN Employees m ON e.ManagerID = m.EmpID;

• When to use: Use the SQL SELF JOIN when you want to compare or link rows in the same table.
• Pitfalls: With this JOIN type, it is easy to lose track of aliases (e vs m) and NULL rows may complicate reporting. To avoid this, always use clear aliases (Employee, Manager) and handle NULLs explicitly.
• Example scenario: Match each employee with the manager. If the employee has no manager (belongs to the top management), the column will contain NULLs.

SELECT 
    epp.FirstName + ' ' + epp.LastName AS Employee,
    mpp.FirstName + ' ' + mpp.LastName AS Manager
FROM HumanResources.Employee e
LEFT JOIN HumanResources.Employee m
    ON e.OrganizationNode.GetAncestor(1) = m.OrganizationNode
LEFT JOIN Person.Person epp
    ON e.BusinessEntityID = epp.BusinessEntityID
LEFT JOIN Person.Person mpp
    ON m.BusinessEntityID = mpp.BusinessEntityID;

CROSS JOIN

A 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, a CROSS JOIN does not require a joining condition.

The simplest query example is:

SELECT c.Color, s.Size 
FROM Colors c 
CROSS JOIN Sizes s;

• When to use: Use the SQL CROSS JOIN when you want to generate combinations of all values between two tables.
• Pitfalls: Result size grows quickly (rows = table1 × table2), which can crash queries if tables are large. Therefore, use it only for small lookup tables or with filters (WHERE clause) to control output size.
• Example scenario: Generate all possible combinations of color and size with no additional JOIN conditions.

SELECT 
    c.Color,
    s.Size
FROM 
    (SELECT DISTINCT Color 
     FROM Production.Product 
     WHERE Color IS NOT NULL) AS c
CROSS JOIN 
    (SELECT DISTINCT Size 
     FROM Production.Product 
     WHERE Size IS NOT NULL) AS s;

SQL JOINs summary

SELECT *
FROM Customers, Orders;

SELECT c.Name, o.Product
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

SELECT *
FROM Customers c
JOIN Orders o ON c.CustomerID = o.OrderID;

SELECT c.Name, o.Product
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

SELECT CustomerID, Name, Product
FROM Customers
JOIN Orders ON CustomerID = CustomerID;

SELECT c.CustomerID, c.Name, o.Product
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

SELECT c.Name, o.Product
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;

CREATE INDEX idx_orders_customerid 
ON Orders(CustomerID);