Implicit Join vs. Explicit Join

If we use “Inner Join”, “Left Outer Join”, “Right Outer Join”, “Full Outer Join” keywords in the SQL statement to join the tables then it’s an explicit join (basically we are specifying join condition explicitly) for more info click here, if we do not specify join condition but just separate the tables with “,” and specify join condition in where clause then they are doing implicit joins as shown below

 

SQL Server and Oracle

Implicit inner join is performed in the same way in both SQL Server and Oracle

 

Example:  

 

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"

                   )

 

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.

 

SQL Server: Implicit Left Join (Deprecated)

Example:

  /*add above CTE code Here to see the data */

Select *

from TableA

,TableB

where TableA.col1*= TableB.col3

order by col1

 

Oracle: Implicit Left Join

Example:

  /*add above CTE code Here to see the data */

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

 

SQL Server: Implicit Right Join (Deprecated)

Example:

  /*add above CTE code Here to see the data */

Select *

from TableA

,TableB

where TableA.col1= TableB.col3*

order by col1

 

Oracle: Implicit Right Join

Example:

  /*add above CTE code Here to see the data */

Select *

from TableA

,TableB

where TableA.col1(+)= TableB.col3

order by col1;

 

For Full outer join setting the optional join operator “(+)” on both the sides does not work we have to do Left Join union all right join with right column null

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.