Inner Join, Left Outer Join, Right Outer Join and Full Outer Join

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

 

 

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.