Why We Need Joins
In a well-designed database, information is split across multiple tables to avoid repetition. A Books table stores book details, and a separate Authors table stores author details, linked by a foreign key. When we want to see both book and author information in one result, we use a join.
Our Sample Tables
We'll use these two small tables throughout the article. Please set them up so you can run every query yourself.
CREATE TABLE Authors (
AuthorId INT PRIMARY KEY,
Name VARCHAR(60),
Country VARCHAR(30)
);
CREATE TABLE Books (
BookId INT PRIMARY KEY,
Title VARCHAR(80),
Price DECIMAL(8, 2),
AuthorId INT NULL -- links to Authors.AuthorId
);
INSERT INTO Authors VALUES
(1, 'Arjun Kapoor', 'India'),
(2, 'Emily Carter', 'USA'),
(3, 'Hiro Tanaka', 'Japan'),
(4, 'Sofia Martinez', 'Spain'); -- no books yet
INSERT INTO Books VALUES
(101, 'Code of the Hills', 499.00, 1),
(102, 'Silent Orbit', 650.00, 2),
(103, 'Kyoto Letters', 420.00, 3),
(104, 'The Second Monsoon', 550.00, 1),
(105, 'Unbound Pages', 299.00, NULL); -- no author assigned
Important observations to keep in mind:
These two "orphans" are the key to understanding the difference between every join type.
The Five Types of Joins
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
1. Inner Join — Only the Matches
An Inner Join returns only those rows where the join condition is satisfied in both tables. If a book has no author or an author has no book, those rows are silently dropped.
SELECT b.Title, b.Price, a.Name AS AuthorName, a.Country
FROM Books b
INNER JOIN Authors a
ON b.AuthorId = a.AuthorId;
Result — 4 rows:
| Title | Price | AuthorName | Country |
|---|---|---|---|
| Code of the Hills | 499.00 | Arjun Kapoor | India |
| Silent Orbit | 650.00 | Emily Carter | USA |
| Kyoto Letters | 420.00 | Hiro Tanaka | Japan |
| The Second Monsoon | 550.00 | Arjun Kapoor | India |
JOIN defaults to INNER JOIN, but always write it explicitly for readability.
2. Left Outer Join — Keep Everything on the Left
A Left Join (or Left Outer Join) returns every row from the left table, plus matching rows from the right. If there's no match on the right, the right-side columns come back as NULL.
Use it when the question is: "Show me all books, even the ones without an author."
SELECT b.Title, b.Price, a.Name AS AuthorName, a.Country
FROM Books b
LEFT JOIN Authors a
ON b.AuthorId = a.AuthorId;
Result — 5 rows (all 4 matches + the orphan book):
| Title | Price | AuthorName | Country |
|---|---|---|---|
| Code of the Hills | 499.00 | Arjun Kapoor | India |
| Silent Orbit | 650.00 | Emily Carter | USA |
| Kyoto Letters | 420.00 | Hiro Tanaka | Japan |
| The Second Monsoon | 550.00 | Arjun Kapoor | India |
| Unbound Pages | 299.00 | NULL | NULL |
The orphan book Unbound Pages now appears, with NULL in the author columns. The keyword OUTER is optional — LEFT JOIN and LEFT OUTER JOIN mean the same thing.
3. Right Outer Join — Keep Everything on the Right
A Right Join is the mirror image of a left join. It returns every row from the right table, plus matching rows from the left. Unmatched left-side columns come back as NULL.
Use it when the question is: "Show me all authors, even the ones with no book in the system."
SELECT b.Title, b.Price, a.Name AS AuthorName, a.Country
FROM Books b
RIGHT JOIN Authors a
ON b.AuthorId = a.AuthorId;
Result — 5 rows (4 matches + the orphan author):
| Title | Price | AuthorName | Country |
|---|---|---|---|
| Code of the Hills | 499.00 | Arjun Kapoor | India |
| Silent Orbit | 650.00 | Emily Carter | USA |
| Kyoto Letters | 420.00 | Hiro Tanaka | Japan |
| The Second Monsoon | 550.00 | Arjun Kapoor | India |
| NULL | NULL | Sofia Martinez | Spain |
RIGHT JOIN can be rewritten as a LEFT JOIN just by swapping the tables. Most teams prefer LEFT JOIN everywhere for consistency. Use whichever reads most naturally for your case.
4. Full Outer Join — Keep Everything, Both Sides
A Full Outer Join combines the behaviour of left and right joins. It returns every row from both tables. Where a row in one side has no match in the other, the missing side's columns are NULL.
SELECT b.Title, b.Price, a.Name AS AuthorName, a.Country
FROM Books b
FULL OUTER JOIN Authors a
ON b.AuthorId = a.AuthorId;
Result — 6 rows (4 matches + 1 orphan book + 1 orphan author):
| Title | Price | AuthorName | Country |
|---|---|---|---|
| Code of the Hills | 499.00 | Arjun Kapoor | India |
| Silent Orbit | 650.00 | Emily Carter | USA |
| Kyoto Letters | 420.00 | Hiro Tanaka | Japan |
| The Second Monsoon | 550.00 | Arjun Kapoor | India |
| Unbound Pages | 299.00 | NULL | NULL |
| NULL | NULL | Sofia Martinez | Spain |
Full Outer is the most inclusive join. It is perfect for data-quality reports — "find me every row that doesn't have a matching partner on the other side."
5. Cross Join — Every Combination
A Cross Join produces the Cartesian product of the two tables: every row on the left is paired with every row on the right. It does not use an ON clause.
In our example, 5 books × 4 authors = 20 rows.
SELECT b.Title, a.Name AS AuthorName
FROM Books b
CROSS JOIN Authors a;
The output is meaningless as a "book-author" list — it pairs every book with every author regardless of reality. So when would you use it?
Generic Join Syntax
All joins (except cross join) follow the same template. Learn this once and you can write any join.
SELECT <column list>
FROM <left_table> [alias]
<join_type> <right_table> [alias]
ON <join_condition>;
-- join_type is one of:
-- INNER JOIN
-- LEFT [OUTER] JOIN
-- RIGHT [OUTER] JOIN
-- FULL [OUTER] JOIN
-- CROSS JOIN (no ON clause)
Row Counts at a Glance
Using our sample data (5 books, 4 authors, 4 matches), here is exactly what each join returns:
| Join Type | What Returns | Row Count |
|---|---|---|
| Inner Join | Only matching rows | 4 |
| Left Join (Books left) | Matches + orphan book | 5 |
| Right Join (Authors right) | Matches + orphan author | 5 |
| Full Outer Join | Matches + both orphans | 6 |
| Cross Join | Every book × every author | 20 |
How to Pick the Right Join
Need only matches?
Need all left rows?
Need all right rows?
Need everything, both sides?
Need every combination?
Common Mistakes
1. Forgetting the ON clause. Except for cross join, every join needs an ON condition. Miss it and you get either a syntax error or an accidental Cartesian product.
2. Wrong column in the join condition. Always link primary key to foreign key. Joining on a non-key column often produces unexpected duplicate rows.
3. Putting filters in the wrong place. Conditions on the outer-joined table belong in the ON clause; conditions on the main table belong in WHERE. Otherwise, a LEFT JOIN silently behaves like an INNER JOIN.
4. Using Inner Join when Left is needed. If you want "all customers, and their orders if any", a left join is what you want — an inner join would hide customers with no orders.
5. Running a cross join by accident. If you write two tables in the FROM clause without any join, SQL Server does a cross join. On big tables this can crash a query window.
The Interview Answer
"SQL Server has five join types. Inner Join returns only matching rows from both tables. Left Join returns all rows from the left table plus matches from the right, with NULLs where no match exists. Right Join is the mirror — all rows from the right plus matches from the left. Full Outer Join returns every row from both tables with NULLs on either side for unmatched rows. Cross Join returns the Cartesian product — every row from the left paired with every row from the right, with no ON clause."
Summary
Joins are how relational databases put split data back together. Inner Join gives you only the intersection. Left Join keeps everything on the left, Right Join keeps everything on the right, and Full Outer Join keeps everything on both sides. Cross Join is the outlier — no condition, just every possible pairing.
Always specify the join type explicitly, always write the ON clause carefully using primary and foreign keys, and always stop to think whether unmatched rows belong in your answer or not. Get that habit right, and joins will never confuse you again.
| Concept | Key Takeaway |
|---|---|
| Inner Join | Only rows present in both tables |
| Left Join | All left rows, NULL for missing right |
| Right Join | All right rows, NULL for missing left |
| Full Outer Join | All rows from both, NULLs on unmatched sides |
| Cross Join | Every row × every row, no ON clause |
| OUTER keyword | Optional (LEFT JOIN = LEFT OUTER JOIN) |
| Default JOIN | A plain JOIN means INNER JOIN |
| Most restrictive | Inner Join |
| Most inclusive | Full Outer Join |
| Join key | Use primary key ↔ foreign key |