Analytics


Google

Tuesday, July 24, 2012

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.


No comments: