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.