SQL Joins

Coming from an Oracle environment, I have a problem remember the joins.  This document will help me remember.  In the sample below, the SQL query with the word join is ANSI SQL compliant and works also with other databases like postgres, MySQL, SQLServer etc.

Sample Data

I will use two tables for the examples.

SampTab1 - data is as follow:

NAME	ROLE
John	Mascot
Alley	Guard

SampTab2 - data is as follow:

NAME	GRADE
John	90
Tish	60

Inner Join, also known as equi join

The following are the two versions of the query:

Old Oracle Query
select *
  from samptab1 tabA, samptab2 tabB
  where tabA.name = tabB.name;

Ansi Join Query
select *
  from samptab1
  inner join samptab2 on samptab2.name = samptab1.name;

The data output is as follow:

NAME	ROLE	NAME_1	GRADE
John	Mascot	John	90

Left Outer Join

Old Oracle SQL Query
select *
  from samptab1 tabA, samptab2 tabB
  where tabA.name = tabB.name (+);





Ansi join Query
select *
  from samptab1 left outer join samptab2
  on samptab1.name = samptab2.name;

Output:

NAME	ROLE	NAME_1	GRADE
John	Mascot	John	90
Alley	Guard

Right Outer Join

Old Oracle Query:

select *
  from samptab1 tabA, samptab2 tabB
  where tabA.name (+) = tabB.name;

Ansi join Query:

select *
  from samptab1 right outer join samptab2
  on samptab1.name = samptab2.name;  

Output:

NAME	ROLE	NAME_1	GRADE
John	Mascot	John	90
		Tish	60

Full Outer Join

Note that the order of output for the two queries below is a bit different.

Old Oracle Query:
select *
  from samptab1 tabA, samptab2 tabB
  where tabA.name (+) = tabB.name
union
select *
  from samptab1 tabA, samptab2 tabB
  where tabA.name = tabB.name (+);


Ansi Join Query:
select *
  from samptab1 full outer join samptab2
  on samptab1.name = samptab2.name; 

Output:

This is the output from the Old Oracle Query:

NAME	ROLE	NAME_1	GRADE
Alley	Guard
John	Mascot	John	90
		Tish	60

 

This is the output for the Ansi Join Query:

NAME	ROLE	NAME_1	GRADE
John	Mascot	John	90
		Tish	60
Alley	Guard

Cross Join

Old Oracle Query:
select *
  from samptab1 tabA, samptab2 tabB;

Ansi Join Query:

select *
  from samptab1 cross join samptab2; 

Output:

NAME	ROLE	NAME_1	GRADE
John	Mascot	John	90
John	Mascot	Tish	60
Alley	Guard	John	90
Alley	Guard	Tish	60

All the above statements are mad in Oracle.   One reference that might be useful in comparing the different SQL implementation is found in the following url.