Welcome to the website of Davis Fiore

It's a SQL clause to join the results from two or more tables, usually binding them through primary key and foreign key.

An explicit join uses the keyword JOIN, an implicit doesn't.

Suppose to have two tables:

Select * from student;

STUDENT_ID	NAME		CLASS_ID
-----------------------------------------------------------------
1		"Ann"		1
2		"Ale"		2
3		"Mike"		2
4		"Bob"		NULL

Select * from class;

CLASS_ID		YEAR
---------------------------
1		1
2		2
3		3

We want to join the two tables to retrieve all the students currently in a class. There may be students not in a class and classes closed because without students and we don't want to retrieve them.

The solution is an INNER JOIN, that retrieves a row when a value is present in both the tables.

Explicit syntax:

select s.student_id, s.name, s.class_id, c.year from student s 
join class c on (s.class_id = c.class_id);

Implicit syntax:

select s.student_id, s.name, s.class_id, c.year from student s, class c  
where s.class_id = c.class_id;

STUDENT_ID	NAME		CLASS_ID	YEAR
---------------------------------------------------------------------------
1		"Ann"		1		1
2		"Ale"		2		2
3		"Mike"		2		2

After we want to retrieve all the students, doesn't matter if they are still enrolled in a class.

We use LEFT OUTER JOIN, that returns all the rows of the left table, even when there are no rows in the right table.

select s.student_id, s.name, s.class_id, c.year from student s 
left outer join class c on (s.class_id = c.class_id);

STUDENT_ID	NAME		CLASS_ID	YEAR
---------------------------------------------------------------------------
1		"Ann"		1		1
2		"Ale"		2		2
3		"Mike"		2		2
4		"Bob"		NULL		NULL

Now we want to retrieve all the students currently in a class and also get information about all the empty classes.

The solution is the RIGHT OUTER JOIN, that is like the left outer join, but in the opposite direction.

select s.student_id, s.name, c.class_id, c.year from student s 
right outer join class c on (s.class_id = c.class_id);

STUDENT_ID	NAME		CLASS_ID	YEAR
-------------------------------------------------------------------------
1		"Ann"		1		1
2		"Ale"		2		2
3		"Mike"		2		2
NULL		NULL		3		3

Finally we want to retrieve all the students and the classes, doesn't matter if a student is not currently in a class or if a class is empty.

The solution is a FULL OUTER JOIN, that retrieves all the classes and the students, even if the joined values is present only in one table.

select s.student_id, s.name, c.class_id, c.year from student s 
full outer join class c on (s.class_id = c.class_id);

STUDENT_ID	NAME		CLASS_ID	YEAR
-------------------------------------------------------------------------
1		"Ann"		1		1
2		"Ale"		2		2
3		"Mike"		2		2
4		"Bob"		NULL		NULL
NULL		NULL		3		3

There are other cases like the CROSS JOIN, that returns a lot of records, because it's the product between the two tables:

Explicit syntax:

select s.student_id, s.name, c.class_id, c.year from student s cross join class c;

Implicit syntax:

select s.student_id, s.name, c.class_id, c.year from student s, join class c;