A Complete Guide to Different Types of Joins in SQL

A tea-shop story to begin

Picture a small tea shop near a railway station. The owner keeps two notebooks.

The first notebook is the Customers notebook. It lists every regular customer and their phone number.

The second notebook is the Orders notebook. It lists every cup of tea sold, and next to each order it writes down which customer bought it.

One evening the owner wants a single combined list: each customer next to the tea they ordered. To make that list, the owner reads a customer from the first notebook, then flips to the second notebook to find that customer's orders, and writes them side by side.

That act of matching two notebooks by a shared piece of information (the customer) is exactly what a JOIN does in SQL. The only thing that changes from join to join is one simple question: what do we do with the rows that have no match?

• Keep only matched rows? That is an INNER JOIN.
• Keep every customer, even those who never ordered? That is a LEFT JOIN.
• Keep every order, even ones with a missing customer? That is a RIGHT JOIN.
• Keep everything from both sides? That is a FULL OUTER JOIN.
• Pair every customer with every tea on the menu? That is a CROSS JOIN.

Let us learn each one with the same little tables, step by step.

Our two sample tables

We will use two tiny tables for the whole article. Keeping them small makes the results easy to check by hand.

Students table:

Marks table:

Look closely, because two details matter for every example below.

• Kabir (3) and Meera (4) have no marks yet. They exist in Students but not in Marks.
• Mark 104 belongs to StudentId 5, but there is no student 5 in the Students table. It is an orphan row.

These two mismatches are what make joins interesting. Different joins treat them differently.

You can create and fill these tables yourself:

CREATE TABLE Students (
    StudentId INT PRIMARY KEY,
    Name      NVARCHAR(50)
);
 
CREATE TABLE Marks (
    MarkId    INT PRIMARY KEY,
    StudentId INT,
    Subject   NVARCHAR(50),
    Score     INT
);
 
INSERT INTO Students VALUES (1,'Aarav'),(2,'Diya'),(3,'Kabir'),(4,'Meera');
INSERT INTO Marks VALUES
    (101,1,'Maths',88),(102,1,'Science',75),
    (103,2,'Maths',91),(104,5,'Maths',60);

The big picture of join types

Before the details, here is a map of the joins we will cover and what each one keeps.

A handy way to remember the difference:

INNER JOIN: only the matches

An INNER JOIN returns only the rows where the matching value exists in both tables. If a row on either side has no partner, it is dropped.

SELECT s.Name, m.Subject, m.Score
FROM Students s
INNER JOIN Marks m
    ON s.StudentId = m.StudentId;

Result:

Notice what disappeared. Kabir and Meera are gone because they have no marks. Mark 104 is also gone because student 5 does not exist. INNER JOIN only keeps rows that match on both sides.

INNER JOIN is the most common join. The word INNER is optional, so FROM Students s JOIN Marks m ON ... means the same thing. Reach for it when you only care about records that have a partner on the other side.

LEFT JOIN: keep everyone on the left

A LEFT OUTER JOIN (you can write just LEFT JOIN) keeps every row from the left table. Where the right table has no match, SQL fills those columns with NULL.

SELECT s.Name, m.Subject, m.Score
FROM Students s
LEFT JOIN Marks m
    ON s.StudentId = m.StudentId;

Result:

Now Kabir and Meera appear, even though they have no marks. Their subject and score are NULL because there is nothing to fill them with. This is the join you use to answer questions like "show me every student, including those who have not been graded yet."

A very common trick is to find the rows with no match. Just add a filter for the NULL:

SELECT s.Name
FROM Students s
LEFT JOIN Marks m
    ON s.StudentId = m.StudentId
WHERE m.StudentId IS NULL;

This returns just Kabir and Meera, the students with no marks at all. It is a clean way to find "orphans" on the left side.

RIGHT JOIN: keep everyone on the right

A RIGHT OUTER JOIN is the mirror image of LEFT JOIN. It keeps every row from the right table, and fills the left side with NULL when there is no match.

SELECT s.Name, m.Subject, m.Score
FROM Students s
RIGHT JOIN Marks m
    ON s.StudentId = m.StudentId;

Result:

This time the orphan mark 104 (student 5) shows up, with a NULL name. But Kabir and Meera vanish, because RIGHT JOIN does not care about unmatched left rows.

Here is a friendly secret: a RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table order. The two queries below give the same rows:

-- RIGHT JOIN version
SELECT s.Name, m.Subject FROM Students s RIGHT JOIN Marks m ON s.StudentId = m.StudentId;
 
-- Same result, written as a LEFT JOIN by swapping the tables
SELECT s.Name, m.Subject FROM Marks m LEFT JOIN Students s ON s.StudentId = m.StudentId;

Because of this, many teams agree to "always use LEFT JOIN" so the code reads in one consistent direction. RIGHT JOIN is perfectly valid SQL; it is just used less often.

FULL OUTER JOIN: keep everything

A FULL OUTER JOIN keeps all rows from both tables. Where there is a match, the two sides line up. Where there is no match on a side, that side becomes NULL.

SELECT s.Name, m.Subject, m.Score
FROM Students s
FULL OUTER JOIN Marks m
    ON s.StudentId = m.StudentId;

Result:

This is LEFT and RIGHT combined. You see the unmatched students (Kabir, Meera) and the unmatched mark (student 5). FULL OUTER JOIN is great when you want a complete picture and need to spot mismatches on either side, such as comparing two lists to find what is missing from each.

Note: some databases like older MySQL versions do not support FULL OUTER JOIN directly. There you can imitate it by running a LEFT JOIN and a RIGHT JOIN and combining them with UNION. SQL Server, PostgreSQL, and Oracle support it natively.

CROSS JOIN: every possible pair

A CROSS JOIN has no ON condition. It pairs every row of the first table with every row of the second. The result count is the two row counts multiplied together. This is called the Cartesian product.

Imagine a tiny menu with sizes and a list of flavours:

SELECT sz.Size, fl.Flavour
FROM Sizes sz
CROSS JOIN Flavours fl;

If Sizes has 3 rows (Small, Medium, Large) and Flavours has 4 rows, the result has 3 × 4 = 12 rows, one for every combination. That is perfect for building a full product grid.

But be careful. If you accidentally cross join two big tables, you can get millions of rows in a flash. Use CROSS JOIN only when the multiplication is truly what you want.

SELF JOIN: a table joined to itself

A SELF JOIN is not a new keyword. It is just a normal join where a table is joined to itself, using two different aliases. It is handy when rows in one table point to other rows in the same table.

A classic case is an Employees table where each employee has a ManagerId that points to another employee's EmployeeId.

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

Here e is the employee and m is the same table playing the role of "manager." We used a LEFT JOIN so that the top boss (who has no manager) still appears, with a NULL manager. Self joins are the standard way to walk simple hierarchies like manager chains, categories with parent categories, or comments with parent comments.

How joins relate to LINQ and EF Core

If you write C# with Entity Framework Core, you usually do not type SQL joins by hand. EF Core builds them for you from your model and your LINQ.

When you use a navigation property with Include, EF Core typically generates a LEFT JOIN so that a parent with no children still comes back:

var students = await context.Students
    .Include(s => s.Marks)   // EF Core emits a LEFT JOIN
    .ToListAsync();
 
foreach (var student in students)
{
    Console.WriteLine($"{student.Name} has {student.Marks.Count} marks");
    // Kabir and Meera show up here with 0 marks, thanks to the LEFT JOIN
}

You can also write an explicit join in LINQ. A LINQ join ... on ... equals ... maps to an INNER JOIN:

var rows = await (
    from s in context.Students
    join m in context.Marks
        on s.StudentId equals m.StudentId   // becomes INNER JOIN in SQL
    select new { s.Name, m.Subject, m.Score }
).ToListAsync();

To get LEFT JOIN behaviour in query syntax, you add into and DefaultIfEmpty, which is the LINQ pattern that tells EF Core to keep the unmatched left rows:

var rows = await (
    from s in context.Students
    join m in context.Marks
        on s.StudentId equals m.StudentId into studentMarks
    from m in studentMarks.DefaultIfEmpty()   // LEFT JOIN
    select new
    {
        s.Name,
        Subject = m != null ? m.Subject : null,
        Score   = m != null ? (int?)m.Score : null
    }
).ToListAsync();

Knowing the raw SQL joins helps you read the SQL that EF Core logs, and understand why a query returns extra NULL rows or drops rows you expected to see.

Quick comparison of all the joins

A few friendly tips

• Always write the ON condition for INNER, LEFT, RIGHT, and FULL joins. Forgetting it can turn your query into an accidental cross join.
• Join on indexed columns (usually primary and foreign keys). This keeps joins fast even on large tables.
• Mind the WHERE versus ON placement on outer joins. Putting a condition about the right table in the WHERE clause can quietly turn a LEFT JOIN back into an INNER JOIN, because NULL rows fail the filter. Put right-table conditions in the ON clause instead.
• Use clear table aliases like s and m. Short, meaningful aliases make multi-table queries much easier to read.
• Prefer LEFT JOIN over RIGHT JOIN for consistency, since any RIGHT JOIN can be flipped into a LEFT JOIN.

Quick recap

• A JOIN combines rows from two tables using a shared column. The join type decides what happens to unmatched rows.
• INNER JOIN keeps only rows that match on both sides.
• LEFT JOIN keeps every left row and fills missing right columns with NULL.
• RIGHT JOIN keeps every right row; it is the mirror of LEFT JOIN and can be rewritten as one.
• FULL OUTER JOIN keeps everything from both tables, matched where possible.
• CROSS JOIN has no ON and pairs every row with every row, so it multiplies the row count.
• SELF JOIN is a table joined to itself, perfect for hierarchies like managers and employees.
• In EF Core, Include usually emits a LEFT JOIN, while a LINQ join emits an INNER JOIN; DefaultIfEmpty gives you LEFT JOIN behaviour.

References and further reading

• Joins (SQL Server) — Microsoft Learn
• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL) — Microsoft Learn
• SQL Joins (Inner, Left, Right and Full Join) — GeeksforGeeks
• SQL Server Join Types with Examples — MSSQLTips