Introduction to JOINs in SQL Server: INNER, OUTER and CROSS JOIN

This introduction to JOINs started with a request. When we launched the Gandhinagar SQL Server User Group, I was the key speaker, and the room was full of people eager to learn. Some members asked me to write a simple article on JOINs and their types. Here it is, with a real result for every JOIN.

What a JOIN Decides

A JOIN puts rows from two tables together by comparing a column in each. The rows that match are the easy part. Each type of JOIN is a different answer to one question: what happens to the rows with no partner?

The Two Sample Tables

Every example here uses the same two small tables. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Table1 (ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

CREATE TABLE Table2 (ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO

INNER JOIN

This join returns rows when there is at least one match in both the tables. Anything without a match stays out of the result.

SELECT t1.*, t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID

LEFT OUTER JOIN

There are three different outer join methods, and this is the first. It returns all the rows from the left table along with the matching rows from the right table. If there’s no match in the right table, it returns NULL values.

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID

RIGHT OUTER JOIN

This one mirrors the left join. It returns all the rows from the right table along with the matching rows from the left table. If there’s no match in the left table, it returns NULL values.

SELECT t1.*, t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

FULL OUTER JOIN

This join combines the left outer join and the right outer join. It returns the rows from both tables, and it returns NULL values wherever there’s no match.

SELECT t1.*, t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

CROSS JOIN

This is a Cartesian join, and it doesn’t need any condition to join. The number of rows in the result is the row count of one table multiplied by the row count of the other. With 5 rows and 6 rows, we get 30.

SELECT t1.*, t2.*
FROM Table1 t1
CROSS JOIN Table2 t2

Finding the Rows With No Match

Outer joins are useful for more than keeping rows. You’ll see many developers write a query like this to find rows that are missing from another table:

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)

An outer join with a WHERE clause gives the same result here, and it’s the form I recommend. You’ll see why in the first trap below.

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

You can write the same idea with a right outer join:

SELECT t1.*, t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL

Remember, the term Not Inner Join doesn’t exist in database terminology. But a full outer join with a WHERE condition gives the exact opposite of an inner join. It returns every row that an inner join would leave out.

SELECT t1.*, t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL

Three Traps Worth Knowing

These three come up again and again, and readers have asked about them in the comments here. I ran each one on the same tables.

Trap 1: NOT IN and a single NULL. Add one row with a NULL ID to Table2, and NOT IN returns nothing at all, not even IDs 4 and 5. The left join form still returns both rows. NOT EXISTS also still returns both.

INSERT INTO Table2 (ID, Value) VALUES (NULL, 'Unknown')

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

DELETE FROM Table2 WHERE ID IS NULL

Trap 2: A WHERE filter can undo your LEFT JOIN. Put a filter on the right table in the ON clause, and you keep all 5 left rows. Move the same filter to WHERE, and you get 1 row, because NULL fails the test. I go deeper in A WHERE Filter That Turns Your LEFT JOIN Into an INNER JOIN.

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Value = 'First'

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Value = 'First'

Trap 3: Duplicates multiply. A reader asked what happens when both tables hold the value 1 three times. Each row matches all three on the other side, so the inner join returns 9 rows, and so do the left and right joins. As another reader noted, Venn diagrams show which rows qualify, not how many come back.

CREATE TABLE Dup1 (ID INT)
CREATE TABLE Dup2 (ID INT)
INSERT INTO Dup1 VALUES (1),(1),(1)
INSERT INTO Dup2 VALUES (1),(1),(1)

SELECT a.ID, b.ID
FROM Dup1 a
INNER JOIN Dup2 b ON a.ID = b.ID

DROP TABLE Dup1
DROP TABLE Dup2

Which JOIN Do I Need?

Start from the rows you want back, and the choice gets easy. Here’s the short version.

Clean Up

When you finish, remove the two sample tables:

DROP TABLE Table1
DROP TABLE Table2

Where to Go Next

That’s the end of this introduction to JOINs. These posts go further:

• INNER JOIN vs JOIN in SQL Server: Is There Any Difference?
• Differences Between Left Join and Left Outer Join
• FULL JOIN: Preserve Unmatched Rows on Both Sides
• CROSS JOIN: Duplicate Inputs Multiply the Result
• LEFT JOIN ON Clause: Conditions Decide Matches, Not Rows
• Joining Three Tables: Following the Keys Step by Step

I hope this article fulfills its purpose. Please tell me in the comments where you’d like me to take it next.

A JOIN is not a way to combine tables, it is a decision about the rows with no partner.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.