LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 2624 min read

Joins

Learn how to combine rows from multiple tables in SQL Server using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Introduction

Relational databases split data across multiple related tables to avoid duplication — customer information lives in one table, their orders live in another. Joins are how you bring that related data back together in a single query. SQL Server supports several join types, each with different rules for which rows are included when a match is not found on both sides.

What You Will Learn
  • How INNER JOIN returns only matching rows.
  • How LEFT JOIN keeps all rows from the left table.
  • How RIGHT JOIN keeps all rows from the right table.
  • How FULL OUTER JOIN keeps all rows from both tables.

Sample Tables

The examples below use a Customers table and an Orders table, related by CustomerID. Notice that "Anil Stores" has no orders yet, and one order has a CustomerID that does not match any customer — this lets us clearly see how each join type behaves.

Sample Data
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100)
);


CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderTotal DECIMAL(10,2)
);


INSERT INTO Customers (CustomerName) VALUES
    ('Ravi Traders'), ('Meera Retail'), ('Anil Stores');


INSERT INTO Orders (CustomerID, OrderTotal) VALUES
    (1, 4599.00),
    (1, 3200.00),
    (2, 6100.00),
    (99, 1500.00);  -- no matching customer

INNER JOIN

INNER JOIN returns only the rows where a match exists in both tables. Customers with no orders, and orders with no matching customer, are excluded entirely.

INNER JOIN
SELECT c.CustomerName, o.OrderID, o.OrderTotal
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID;
Result

Click Run to see what this code prints.

LEFT JOIN

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, plus matching rows from the right table. When no match exists on the right, the right table's columns are returned as NULL. This is useful for finding customers with no orders.

LEFT JOIN
SELECT c.CustomerName, o.OrderID, o.OrderTotal
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID;
Result

Click Run to see what this code prints.

RIGHT JOIN

RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of LEFT JOIN: it returns all rows from the right table, plus matching rows from the left table, with NULLs where no match exists on the left. This reveals the order with CustomerID 99, which does not match any customer.

RIGHT JOIN
SELECT c.CustomerName, o.OrderID, o.OrderTotal
FROM Customers AS c
RIGHT JOIN Orders AS o ON c.CustomerID = o.CustomerID;
Result

Click Run to see what this code prints.

RIGHT JOIN is Rarely Needed

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and most style guides prefer LEFT JOIN for consistency since it reads left-to-right in the order the tables are listed.

FULL OUTER JOIN

FULL OUTER JOIN combines the behavior of LEFT and RIGHT JOIN: it returns all rows from both tables, matching where possible, and filling in NULLs on whichever side has no match. This is useful for finding all mismatches in both directions at once.

FULL OUTER JOIN
SELECT c.CustomerName, o.OrderID, o.OrderTotal
FROM Customers AS c
FULL OUTER JOIN Orders AS o ON c.CustomerID = o.CustomerID;
Result

Click Run to see what this code prints.

Common Mistakes

Avoid These Mistakes
  • Forgetting the ON clause, which produces a cross join (every row combined with every row) instead of a meaningful join.
  • Using WHERE to filter a LEFT JOIN's right-table columns, which can accidentally turn it back into an INNER JOIN by discarding NULL rows.
  • Confusing LEFT and RIGHT — remember LEFT keeps all rows from the table listed first (or leftmost) in the FROM clause.
  • Not aliasing tables in multi-table joins, making column references ambiguous or verbose.

Best Practices

  • Always use explicit JOIN syntax with ON, rather than old-style comma joins with conditions in WHERE.
  • Prefer LEFT JOIN over RIGHT JOIN for consistency and readability.
  • Alias every table involved in a join for clarity.
  • Filter right-table-specific conditions in the ON clause (not WHERE) when using LEFT JOIN, to preserve unmatched left rows.
  • Use FULL OUTER JOIN sparingly — it is most useful for reconciliation and data-quality checks.

Frequently Asked Questions

They are the same thing — JOIN defaults to INNER JOIN in T-SQL. Writing INNER JOIN explicitly is considered clearer style.

Yes. You can chain multiple JOIN clauses together in a single query, each with its own ON condition, to combine data from any number of related tables.

Use a LEFT JOIN from Customers to Orders, then add WHERE o.OrderID IS NULL to keep only the customers that had no matching order row.

Key Takeaways

  • INNER JOIN returns only rows with matches in both tables.
  • LEFT JOIN keeps all rows from the left table, filling unmatched right-side columns with NULL.
  • RIGHT JOIN keeps all rows from the right table, filling unmatched left-side columns with NULL.
  • FULL OUTER JOIN keeps all rows from both tables regardless of matches.
  • Always join using explicit JOIN ... ON syntax and table aliases.

Summary

Joins are the backbone of relational querying, letting you combine related data spread across multiple tables. Next, you will learn how to nest one query inside another using subqueries.

Next Lesson →

Subqueries