Joins

Inner join

• Join matching records

Equi-join

• Join records with identical values

-- Explicit
select * from table1 inner join table2 on table1.myid = table2.myid;

-- Implicit
select * from table1, table2 where table1.myid = table2.myid;

Natural join

• Join based on identical column names

-- Explicit
select * from table1 natural join table2;

Cross join

• Cartesian product / cartesian join
• Every combination of records from the two tables

-- Explicit
select * from table1 cross join table2;

-- Implicit
select * from table1, table2;

Outer join

• Join records even if there's no match

Left outer join

• Left join
• All records from the left table, plus matching records from the right table, filling in NULLS for missing matches

-- Explicit
select * from table1 left outer join table2 on table1.myid = table2.myid;

Right outer join

• Right join
• All records from the right table, plus matching records from the left table, filling in NULLS for missing matches

-- Explicit
select * from table1 right outer join table2 on table1.myid = table2.myid;

Full outer join

• All records from the left and right tables, filling in NULLS for missing matches

-- Explicit
select * from table1 full outer join table2 on table1.myid = table2.myid;