Understanding SQL Joins

A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each. ANSI-standard SQL specifies four types of JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN as shown in the figure bleow.

An inner join returns all rows from multiple tables where the join condition is met. An outer join is used to include rows that exist in one table (but not the other). Outer joins subdivide further into left outer joins (left join), right outer joins (right join), and full outer joins, depending on which table's rows are included (left, right, or both).

Suppose you have two Tables: TableA and TableB, both tables have a single key column each named Id, and data as follows:

TableA(Id) TableB(Id)
-      	   -

1          3

2          4

3          5

4          6

Note that (1,2) are unique to TableA, (3,4) are common, and (5,6) are unique to TableB.

In Oracle, the following PL/SQl statements will create two tables and populate the data:

create table TableA(Id number(10));

create table TableB(Id number(10));


insert all

into TableA values(1)

into TableA values(2)

into TableA values(3)

into TableA values(4)

into TableB values(3)

into TableB values(4)

into TableB values(5)

into TableB values(6)

select * from dual;


commit;

The following posts demonstrates you how all above joins work when joining TableA and TableB on the key column Id.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select A.Id, B.Id
from TableA A 
 INNER JOIN TableB B 
 on A.Id = B.Id;

The result will be

A.Id | B.Id

-----+----

   3 | 3

   4 | 4

Right Join

A right join will give all rows in B, plus any common rows in A.

select A.Id, B.Id
from TableA A 
  RIGHT JOIN TableB B
  on A.Id = B.Id;

The result will be

A.Id |  B.Id 

-----+-----

   3 |    3

   3 |    4

null |    5

null |    6

If you just want rows from B only,

select A.Id, B.Id
from TableA A 
  RIGHT JOIN TableB B
  on A.Id = B.Id 
where
  A.Id is null;

The result will be

A.Id |  B.Id

-----+-----

null |    5

null |    6