Joins in MS SQL Server,Oracle & MY SQL

Joins are a way of connecting data from more than one table.We can also have joins on the same table by creating aliases. There are four basic joins 
1) Outer join
2) Left outer join
3) Right outer join
4) inner join.

In this post, we shall learn the syntax of joins on MS SQL Server, Oracle & MY SQL.

First, the tables:-

1) create table medicalcharlatans(CharlatanName varchar(100) primary key,CharlatanRank int,CharlatanDescrption varchar(200))

Then insert some data.

insert into medicalcharlatans values('Jesus Christ',1,'Used to treat diseases by driving out demons')
insert into medicalcharlatans values('Mother Teresa',2,'Cured cancer via Light Emitting Photo')
insert into medicalcharlatans values('Benny Hinn',3,'Cured diseases via Touch Therapy')

Here is a view of the table:-
Benny Hinn    3    Cured diseases via Touch Therapy
Jesus Christ    1    Used to treat diseases by driving out demons
Mother Teresa    2    Cured cancer via Light Emitting Photo

2) create table PedofileProtectors(ProtectorName varchar(100) primary key,ProtectorDescrption varchar(200))


insert into PedofileProtectors values('John Paul 2','Protected Pedofiles and promoted Pedofile protectors like Mother Teresa and Ratzinger')

insert into PedofileProtectors values('Ratzinger','Protected Pedofiles and promoted Pedofile protectors like Mother Teresa and John Paul 2')

insert into PedofileProtectors values('Mother Teresa','Protected Pedofiles and promoted Pedofile protectors like John Paul 2 and Ratzinger, wrote letters of support for recognized Pedofiles')

The table as it stands now :-


John Paul 2    Protected Pedofiles and promoted Pedofile protectors like Mother Teresa and Ratzinger

Mother Teresa    Protected Pedofiles and promoted Pedofile protectors like John Paul 2 and Ratzinger, wrote letters of support for recognized Pedofiles

Ratzinger    Protected Pedofiles and promoted Pedofile protectors like Mother Teresa and John Paul 2

The various Joins :-


Cross Join


The cross join is a cartesian product. Here is the output in MS SQL Server. Incidentally , it's the same in all three databases.


select * from medicalcharlatans cross join  PedofileProtectors
This is supported by all three databases

SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  full outer JOIN PedofileProtectors ON CharlatanName = ProtectorName
Not supported in my SQL. We shall learn in a subsequent post to simulate it using Union.

SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  right outer JOIN PedofileProtectors ON CharlatanName = ProtectorName
It's supported in Oracle.
In My SQL :- SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  right JOIN PedofileProtectors ON CharlatanName = ProtectorName

SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  left outer JOIN PedofileProtectors ON CharlatanName = ProtectorName

It's supported in Oracle.
In My SQL :- SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  left JOIN PedofileProtectors ON CharlatanName = ProtectorName

SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans  inner JOIN PedofileProtectors ON CharlatanName = ProtectorName
 SELECT     CharlatanName,ProtectorName FROM         medicalcharlatans JOIN PedofileProtectors ON CharlatanName = ProtectorName
It's supported in all three databases.

In a subsequent post, we shall discuss unions, intersections, minus, and the top and rownum