When
we need to get data from multiple table in a single
query we need to use joins, if a join is not done properly then it leads
to Cartesian product.
Before
going in depth into different join lets start with
basic mathematics ways of saying joins.
we all
have read f(A ∪
B)=f(A)+f(B)+f(A ∩ B), if we consider A as tableA,
B as tableB, and the they are joined using a joint
condition then
·
Inner Join is f(A ∩ B) i.e. the
output will only contain records were the join condition is met in both
the tables .
·
Outer Join or Full Outer Join is f(A ∪
B) i.e. the output will have records from both the tables irrespective
of the join condition values is met in both the tables, if not joined
properly then it will lead to Cartesian product
·
Left Outer Join is f(A)+f(A ∩ B) assuming tableA is
the left table (or first referenced table in query) i.e. the output will
have records were the join condition is met and records from tableA which do not have corresponding join condition
values in B table.
·
Right Outer Join is f(B)+f(A ∩ B) assuming tableA is the left table (or first referenced table in
query) i.e. the output will have records were the join condition is
met and records from tableB which do not
have corresponding join condition values in B table.
Now
let’s take some examples to explain the above statements, I will be using CTE
(common table expressions) to create temporary tables on the fly so let’s get
started.
Oracle
and SQL Server:
TableA
|
Col1 |
Col2 |
|
x1 |
x |
|
x2 |
y |
|
x3 |
z |
|
X2 |
p |
|
x9 |
q
|
TableB
|
col3 |
col4 |
|
x1 |
a |
|
x2 |
b |
|
x3 |
c |
|
X2 |
d |
|
x3 |
f |
Script
for creating above tables on the fly:
/*CTE code for tables*/
;with
TableA as ( -- remove “;” for oracle
select
'x1' as col1,'x' as col2 union all -- for oracle add "from
dual" before union all
select
'x2' as col1,'y' as col2 union all -- for oracle add "from
dual" before union all
select
'x3' as col1,'z' as col2 union all -- for oracle add "from
dual" before union all
select
'x2' as col1,'p' as col2 union all -- for oracle add "from
dual" before union all
select
'x9' as col1,'q' as col2 -- for oracle add "from dual"
),TableB
as (
select
'x1' as col3,'a' as col4 union all -- for oracle add "from
dual" before union all
select
'x2' as col3,'b' as col4 union all -- for oracle add "from
dual" before union all
select
'x3' as col3,'c' as col4 union all -- for oracle add "from
dual" before union all
select
'x2' as col3,'d' as col4 union all -- for oracle add "from
dual" before union all
select
'x3' as col3,'f' as col4 -- for oracle add "from dual"
)
Inner
Join
Inner
join outputs all the records where the join condition is met in both the
tables as explained below
Example:
/*add above CTE code here to see the data */
Select *
from
TableA
inner
join TableB
on
TableA.col1= TableB.col3 -- “on” is used to specify join condition ,there can
be multiple join conditions.
So here the
tables are joined on TableA.col1= TableB.col3 i.e. if the value in col1 is
equal to col3 value then there will be an output record, as we can see below
there is “x1” value in both col1 and col3, we have an output record (black
record), similarly there is record for “x2” value in both col1 and col3 we
should have a record but look at the output there are 4 records the reason for
that is there are two records in col1 and 2 records in col3 so every record
joins to every record in the other table i.e. 2*2=4 output records and the
values in other column repeat as shown below (blue records), similarly for “x3”
there is one record in tableA but 2 records in tableB so outputting 2 records (red records).
Output:
|
col1 |
col2 |
col3 |
col4 |
|
x1 |
x |
x1 |
a |
|
x2 |
y |
x2 |
d |
|
x2 |
p |
x2 |
d |
|
x2 |
y |
x2 |
b |
|
x2 |
p |
x2 |
b |
|
x3 |
z |
x3 |
f |
|
x3 |
z |
x3 |
c |
Left Outer
Join
As per the definition
the left join output’s all inner join records (blue records) plus records which
only exist in tableA (left table or first table in
join condition), as shown below for the records that exists only in tableA the values in tableB will
be set to null(red records)
Example:
/*add above CTE code here to see the data */
Select *
from
TableA
left
outer join TableB
on TableA.col1= TableB.col3
Output:
|
col1 |
col2 |
col3 |
col4 |
|
x1 |
x |
x1 |
a |
|
x2 |
p |
x2 |
b |
|
x2 |
p |
x2 |
d |
|
x2 |
y |
x2 |
b |
|
x2 |
y |
x2 |
d |
|
x3 |
z |
x3 |
c |
|
x3 |
z |
x3 |
f |
|
x9 |
q |
NULL |
NULL
|
Right Outer Join
As per the
definition the right join output’s all inner join records (blue records) plus
records which only exist in tableB (right table or
second table in join condition), for the records that exists only in tableB the values in tableA will
be set to null but here we do not have any such records, so in this scenario
even though we did right outer join, as all the tableB
records exists in tableA our output is same as inner
join output.
Example:
/*add above CTE code here to see the data */
Select *
from
TableA
right
outer join TableB
on TableA.col1= TableB.col3
Output:
|
col1 |
col2 |
col3 |
col4 |
|
x1 |
x |
x1 |
a |
|
x2 |
y |
x2 |
b |
|
x2 |
p |
x2 |
b |
|
x2 |
y |
x2 |
d |
|
x2 |
p |
x2 |
d |
|
x3 |
z |
x3 |
f |
|
x3 |
z |
x3 |
c |
Full Outer Join
As per the
definition the full outer join output’s all inner join records (blue records)
plus records which only exist in tableA (left table
or first table in join condition) plus records which only exist in tableB (right table or second table in join condition), for
the records that exists only in tableB the values in tableA will be set to null vice
versa
Example:
/*add above CTE code here to see the data */
Select *
from
TableA
right
outer join TableB
on TableA.col1= TableB.col3
Output:
|
col1 |
col2 |
col3 |
col4 |
|
x1 |
X |
x1 |
a |
|
x2 |
Y |
x2 |
b |
|
x2 |
P |
x2 |
b |
|
x2 |
Y |
x2 |
d |
|
x2 |
P |
x2 |
d |
|
x3 |
Z |
x3 |
f |
|
x3 |
Z |
x3 |
c |
|
x9 |
Q |
NULL |
NULL |
Cross Join
Cross
join is a join without join condition it is mostly used if we need to join each
record in one table with all the records in another table, if there are n records in tableA and m records
in tableB then the output will be m*n records, the
most commonly used cross join is to join a day table and an hour table to
create a record for every hour in every day.
Example:
/*add above CTE code Here to see the data */
Select *
from
TableA
cross
join TableB
--we can just use “,” instead of “cross join” statement to achieve the
same
Output
|
col1 |
col2 |
col3 |
col4 |
|
x1 |
x |
x1 |
a |
|
x1 |
x |
x2 |
b |
|
x1 |
x |
x3 |
c |
|
x1 |
x |
x2 |
d |
|
x1 |
x |
x3 |
f |
|
x2 |
p |
x1 |
a |
|
x2 |
p |
x2 |
b |
|
x2 |
p |
x3 |
c |
|
x2 |
p |
x2 |
d |
|
x2 |
p |
x3 |
f |
|
x2 |
y |
x1 |
a |
|
x2 |
y |
x2 |
b |
|
x2 |
y |
x3 |
c |
|
x2 |
y |
x2 |
d |
|
x2 |
y |
x3 |
f |
|
x3 |
z |
x1 |
a |
|
x3 |
z |
x2 |
b |
|
x3 |
z |
x3 |
c |
|
x3 |
z |
x2 |
d |
|
x3 |
z |
x3 |
f |
|
x9 |
q |
x1 |
a |
|
x9 |
q |
x2 |
b |
|
x9 |
q |
x3 |
c |
|
x9 |
q |
x2 |
d |
|
x9 |
q |
x3 |
f |
Implicit Join vs. Explicit Join
All
the above join statements are explicit join (basically we are specifying join
condition explicitly), if we do not specify join condition but just separate
the tables with “,” and specify join condition in where clause then they are
implicit joins as shown below
Implicit Inner Join
Example:
/*add above CTE code Here to see the data */
Select *
from
TableA
,TableB
where TableA.col1= TableB.col3
As shown in
the above example to achieve the same
Implicit Outer Join
Implicit join
conditions are deprecated in SQL Server but we can still use in oracle but I
prefer not to use them, Oracle uses the optional join operator “(+)” to specify
which records are optional, SQL Server uses “*” operator to say which table
outputs all the records.
Implicit Left Join SQL Server (Deprecated)
Example:
Select *
from TableA
,TableB
where TableA.col1*= TableB.col3
order by col1
Implicit Left Join Oracle
Example:
Select *
from TableA
,TableB
where TableA.col1= TableB.col3(+)
order by col1
Note: “*” is
set for the left table column for SQL Server whereas “(+)” is set for the right
table column for Oracle
To do the Right outer join switch the
side of the optional join operator “(+)” i.e. where TableA.col1(+)= TableB.col3, for
Full outer join set the optional join operator “(+)” on both the sides i.e. where TableA.col1(+)= TableB.col3(+)
Thank you for reading this
article, check out my other SQL Server vs. Oracle posts.