Master SQL Joins: Inner, Outer, and Cross Join Explained with Examples

SQL Join Types Overview

SQL joins can be divided into inner join, outer join, and cross join.

1. Inner Join

1.1 Equi Join

An equi join uses the equality operator (=) in the join condition to compare column values, returning all columns from the joined tables, including duplicates.

1.2 Non‑Equi Join

A non‑equi join uses comparison operators other than = (such as >, >=, <, <=, !=, <>) in the join condition.

1.3 Natural Join

A natural join also uses = but automatically removes duplicate columns from the result set.

Inner join returns rows that satisfy the join condition by comparing column values.

select * from book as a, stu as b where a.sutid = b.stuid;
select * from book as a inner join stu as b on a.sutid = b.stuid;

The INNER keyword can be omitted in the first syntax.

2. Outer Join

2.1 Left Join

A left join uses the left table as the base, matching rows where a.stuid = b.stuid and filling columns from the right table with NULL when there is no matching row.

select * from book as a left join stu as b on a.sutid = b.stuid;

2.2 Right Join

A right join uses the right table as the base, matching rows where a.stuid = b.stuid and filling columns from the left table with NULL when there is no matching row.

select * from book as a right join stu as b on a.sutid = b.stuid;

2.3 Full Outer Join

A full outer join returns all rows from both tables; when a row has no match in the other table, the missing side’s columns are NULL.

select * from book as a full outer join stu as b on a.sutid = b.stuid;

3. Cross Join

A cross join returns the Cartesian product of the two tables, pairing each row of the left table with every row of the right table.

select * from book as a cross join stu as b order by a.id;