Oracle SQL | SQL JOIN Reference Guide
  1. Overview and Sample Data Setup
  2. INNER JOIN - Matching Records Only
  3. LEFT OUTER JOIN - All Left Table Records
  4. LEFT OUTER JOIN (excluding INNER JOIN) - Left Table Only Records
  5. RIGHT OUTER JOIN - All Right Table Records
  6. RIGHT OUTER JOIN (excluding INNER JOIN) - Right Table Only Records
  7. FULL OUTER JOIN - All Records from Both Tables
  8. FULL OUTER JOIN (excluding INNER JOIN) - Non-Matching Records Only
  9. CROSS JOIN - Cartesian Product
  10. SEMI JOIN - Existence Check
  11. ANTI JOIN - Non-Existence Check
  12. UNION - Combine Distinct Records
  13. INTERSECT - Common Records
  14. SQL JOIN Quick Reference Guide
  1. Overview and Sample Data Setup
    This tutorial explains how to use different types of SQL JOIN operations to combine records from two tables. JOINs are fundamental operations that allow you to retrieve related data from multiple tables in a single query.

    Joining two tables requires that the related columns used in JOIN conditions are properly indexed for optimal performance. The related columns are typically primary keys and foreign keys of the joined tables, which establish referential integrity between tables.

    Oracle Database supports all standard SQL JOIN types and provides additional set operations. The execution plan and performance characteristics may vary based on table size, available indexes, and optimizer statistics.

    For detailed explanation of SQL JOIN, see JOIN Operations

    These tables and sample data are used in the examples below:
    • create table table1 (rownum1 int, c1 char, primary key (rownum1));
    • create table table2 (rownum2 int, c2 char, primary key (rownum2));
    • insert into table1(rownum1,c1) values(1,'a');
insert into table1(rownum1,c1) values(2,'a');
insert into table1(rownum1,c1) values(3,'b');
insert into table1(rownum1,c1) values(4,'c');
insert into table1(rownum1,c1) values(5,'c');
insert into table1(rownum1,c1) values(6,'d');

COMMIT;
    • insert into table2(rownum2,c2) values(1,'a');
insert into table2(rownum2,c2) values(2,'b');
insert into table2(rownum2,c2) values(3,'b');
insert into table2(rownum2,c2) values(4,'e');
insert into table2(rownum2,c2) values(5,'e');
insert into table2(rownum2,c2) values(6,'f');

COMMIT;
    • Table1 
      "a"
"a"
"b"
"c"
"c"
"d"
      		   Table2 
      "a"
"b"
"b"
"e"
"e"
"f"
  2. INNER JOIN - Matching Records Only
    INNER JOIN returns only the rows that have matching values in both tables based on the JOIN condition. This is the most restrictive type of JOIN and is often the default choice when you need data that exists in both tables. Oracle's cost-based optimizer typically chooses the most efficient join method (nested loop, hash join, or sort-merge join) based on table statistics and available indexes.

    The keyword INNER is optional; writing just JOIN defaults to an INNER JOIN in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1
inner join table2 t2 on t1.c1=t2.c2
order by t1.rownum1,t2.rownum2
    • Pseudo-code:
      for (rownum1,c1) in table1
  for (rownum2,c2) in table2
    if c1==c2
      print rownum1,c1,rownum2,c2
    • Query output:
      rownum1c1rownum2c2
      1"a"1"a"
      2"a"1"a"
      3"b"2"b"
      3"b"3"b"
  3. LEFT OUTER JOIN - All Left Table Records
    LEFT OUTER JOIN returns all rows from the left table and matching rows from the right table. When no match is found in the right table, NULL values are returned for all columns from the right table. This is useful when you want to preserve all records from the primary table while optionally including related data from a secondary table.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1
left outer join table2 t2 on t1.c1=t2.c2
order by t1.rownum1,t2.rownum2
    • Pseudo-code:
      for (rownum1,c1) in table1
  flag=not-found
  for (rownum2,c2) in table2
    if c1==c2 {
      print rownum1,c1,rownum2,c2
      flag=found
    }
  if flag==not-found
    print rownum1,c1,(null),(null)
    • Query output:
      rownum1c1rownum2c2
      1"a"1"a"
      2"a"1"a"
      3"b"2"b"
      3"b"3"b"
      4"c"(null)(null)
      5"c"(null)(null)
      6"d"(null)(null)
  4. LEFT OUTER JOIN (excluding INNER JOIN) - Left Table Only Records
    This variation of LEFT OUTER JOIN filters out the matching records, returning only records from the left table that have no corresponding match in the right table. This is accomplished by adding a WHERE clause that checks for NULL values in the right table's primary key or any non-nullable column.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1,t1.c1
from table1 t1
left outer join table2 t2 on t1.c1=t2.c2
where t2.rownum2 is null
order by t1.rownum1
    • Pseudo-code:
      for (rownum1,c1) in table1
  flag=not-found
  for c2 in table2
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print rownum1,c1
    • Query output:
      rownum1c1
      4"c"
      5"c"
      6"d"
  5. RIGHT OUTER JOIN - All Right Table Records
    RIGHT OUTER JOIN returns all rows from the right table and matching rows from the left table. When no match is found in the left table, NULL values are returned for all columns from the left table. This is the mirror image of LEFT OUTER JOIN and is less commonly used in practice, as most developers prefer to restructure the query to use LEFT OUTER JOIN for better readability.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1
right outer join table2 t2 on t1.c1=t2.c2
order by t1.rownum1,t2.rownum2
    • Pseudo-code:
      for (rownum2,c2) in table2
  flag=not-found
  for (rownum1,c1) in table1
    if c1==c2 {
      print rownum1,c1,rownum2,c2
      flag=found
    }
  if flag==not-found
    print (null),(null),rownum2,c2
    • Query output:
      rownum1c1rownum2c2
      1"a"1"a"
      2"a"1"a"
      3"b"2"b"
      3"b"3"b"
      (null)(null)4"e"
      (null)(null)5"e"
      (null)(null)6"f"
  6. RIGHT OUTER JOIN (excluding INNER JOIN) - Right Table Only Records
    This variation of RIGHT OUTER JOIN filters out the matching records, returning only records from the right table that have no corresponding match in the left table. Similar to the LEFT OUTER JOIN exclusion pattern, this uses a WHERE clause to check for NULL values in the left table's primary key.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t2.rownum2,t2.c2
from table1 t1
right outer join table2 t2 on t1.c1=t2.c2
where t1.rownum1 is null
order by t2.rownum2
    • Pseudo-code:
      for (rownum2,c2) in table2
  flag=not-found
  for c1 in table1
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print rownum2,c2
    • Query output:
      rownum2c2
      4"e"
      5"e"
      6"f"
  7. FULL OUTER JOIN - All Records from Both Tables
    FULL OUTER JOIN returns all rows when there is a match in either table. It combines the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN. This operation can be resource-intensive for large tables and may require temporary storage for sorting and merging operations in Oracle Database.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query (1):
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1
full outer join table2 t2 on t1.c1=t2.c2
order by t1.rownum1,t2.rownum2
    • Sample Query (2):
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2
union
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2
    • Pseudo-code:
      for (rownum1,c1) in table1
  flag=not-found
  for (rownum2,c2) in table2
    if c1==c2 {
      print rownum1,c1,rownum2,c2
      flag=found
    }
  if flag==not-found
    print rownum1,c1,(null),(null)

for (rownum2,c2) in table2
  flag=not-found
  for c1 in table1
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print (null),(null),rownum2,c2
    • Query output:
      rownum1c1rownum2c2
      1"a"1"a"
      2"a"1"a"
      3"b"2"b"
      3"b"3"b"
      4"c"(null)(null)
      5"c"(null)(null)
      6"d"(null)(null)
      (null)(null)4"e"
      (null)(null)5"e"
      (null)(null)6"f"
  8. FULL OUTER JOIN (excluding INNER JOIN) - Non-Matching Records Only
    This variation of FULL OUTER JOIN returns only the records that do not have matches in the opposite table. It effectively combines LEFT OUTER JOIN exclusion and RIGHT OUTER JOIN exclusion into a single result set, showing orphaned records from both tables.

    The keyword OUTER is optional in Oracle SQL.
    • Table1 Table1 / Table2 Table2
    • Sample Query (1):
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1
full outer join table2 t2 on t1.c1=t2.c2
where t1.rownum1 is null or t2.rownum2 is null
order by t1.rownum1,t2.rownum2
    • Sample Query (2):
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 left outer join table2 t2 on t1.c1=t2.c2 where t2.rownum2 is null
union
select t1.rownum1,t1.c1,t2.rownum2,t2.c2 from table1 t1 right outer join table2 t2 on t1.c1=t2.c2 where t1.rownum1 is null
    • Pseudo-code:
      for (rownum1,c1) in table1
  flag=not-found
  for c2 in table2
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print rownum1,c1,(null),(null)

for (rownum2,c2) in table2
  flag=not-found
  for c1 in table1
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print (null),(null),rownum2,c2
    • Query output:
      rownum1c1rownum2c2
      4"c"(null)(null)
      5"c"(null)(null)
      6"d"(null)(null)
      (null)(null)4"e"
      (null)(null)5"e"
      (null)(null)6"f"
  9. CROSS JOIN - Cartesian Product
    CROSS JOIN produces the Cartesian product of two tables, returning every possible combination of rows from both tables. This operation should be used with caution as it can produce very large result sets. CROSS JOIN is equivalent to the traditional comma-separated table syntax in the FROM clause without a WHERE condition.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1,t1.c1,t2.rownum2,t2.c2
from table1 t1,table2 t2
order by t1.rownum1,t2.rownum2
    • Pseudo-code:
      for (rownum1,c1) in table1
  for (rownum2,c2) in table2
    print rownum1,c1,rownum2,c2
    • Query output:
      rownum1c1rownum2c2
      1"a"1"a"
      1"a"2"b"
      1"a"3"b"
      1"a"4"e"
      1"a"5"e"
      1"a"6"f"
      2"a"1"a"
      2"a"2"b"
      2"a"3"b"
      2"a"4"e"
      2"a"5"e"
      2"a"6"f"
      3"b"1"a"
      3"b"2"b"
      3"b"3"b"
      3"b"4"e"
      3"b"5"e"
      3"b"6"f"
      4"c"1"a"
      4"c"2"b"
      4"c"3"b"
      4"c"4"e"
      4"c"5"e"
      4"c"6"f"
      5"c"1"a"
      5"c"2"b"
      5"c"3"b"
      5"c"4"e"
      5"c"5"e"
      5"c"6"f"
      6"d"1"a"
      6"d"2"b"
      6"d"3"b"
      6"d"4"e"
      6"d"5"e"
      6"d"6"f"
  10. SEMI JOIN - Existence Check
    Semi Join returns rows from the first table where one or more matches are found in the second table, but only returns columns from the first table. This is typically implemented using EXISTS or IN clauses. Oracle's optimizer often converts IN subqueries to semi joins automatically for better performance.
    • Table1 Table1 / Table2 Table2
    • Sample Query (1):
      select t1.rownum1,t1.c1
from table1 t1
where t1.c1 in
(
  select t2.c2
  from table2 t2
)
order by t1.rownum1
    • Sample Query (2):
      select t1.rownum1,t1.c1
from table1 t1
where exists
(
  select 1
  from table2 t2
  where t2.c2=t1.c1
)
order by t1.rownum1
    • Pseudo-code:
      for (rownum1,c1) in table1
  for c2 in table2
    if c1==c2 {
      print rownum1,c1
      break
    }
    • Query output:
      rownum1c1
      1"a"
      2"a"
      3"b"
  11. ANTI JOIN - Non-Existence Check
    Anti Join returns rows from the first table where no matches are found in the second table. This is the opposite of Semi Join and is typically implemented using NOT EXISTS or NOT IN clauses. When using NOT IN, be careful with NULL values as they can produce unexpected results.
    • Table1 Table1 / Table2 Table2
    • Sample Query (1):
      select t1.rownum1,t1.c1
from table1 t1
where t1.c1 not in
(
  select t2.c2
  from table2 t2
)
order by t1.rownum1
    • Sample Query (2):
      select t1.rownum1,t1.c1
from table1 t1
where not exists
(
  select 1
  from table2 t2
  where t2.c2=t1.c1
)
order by t1.rownum1
    • Pseudo-code:
      for (rownum1,c1) in table1
  flag=not-found
  for c2 in table2
    if c1==c2 {
      flag=found
      break
    }
  if flag==not-found
    print rownum1,c1
    • Query output:
      rownum1c1
      4"c"
      5"c"
      6"d"
  12. UNION - Combine Distinct Records
    UNION combines the result sets of two or more SELECT statements, automatically removing duplicate rows. The SELECT statements must have the same number of columns with compatible data types. Oracle performs an implicit DISTINCT operation, which requires sorting and can be resource-intensive for large result sets.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1
union
select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
    • Pseudo-code:
      for (rownum1,c1) in table1
  for (rownum2,c2) in table2
    if rownum1==rownum2 && c1==c2 {
      put (rownum1,c1) in set-without-duplicates
      break
    }


for (rownum1,c1) in set-without-duplicates
  print rownum1,c1

for (rownum1,c1) in table1
  if (rownum1,c1) not in set-without-duplicates
    print rownum1,c1

for (rownum2,c2) in table2
  if (rownum2,c2) not in set-without-duplicates
    print rownum2,c2
    • Query output:
      rownumc
      1"a"
      2"a"
      2"b"
      3"b"
      4"c"
      4"e"
      5"c"
      5"e"
      6"d"
      6"f"
  13. INTERSECT - Common Records
    INTERSECT returns only the rows that appear in both result sets, effectively finding the common records between two SELECT statements. Like UNION, the SELECT statements must have the same number of columns with compatible data types. This operation is useful for finding shared data between tables.
    • Table1 Table1 / Table2 Table2
    • Sample Query:
      select t1.rownum1 as "rownum",t1.c1 as "c" from table1 t1
intersect
select t2.rownum2 as "rownum",t2.c2 as "c" from table2 t2
    • Pseudo-code:
      for (rownum1,c1) in table1
  for (rownum2,c2) in table2
    if rownum1==rownum2 && c1==c2 {
      put (rownum1,c1) in set-without-duplicates
      break
    }


for (rownum1,c1) in set-without-duplicates
  print rownum1,c1
    • Query output:
      rownumc
      1"a"
      3"b"
  14. SQL JOIN Quick Reference Guide
    INNER JOIN
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
inner join table2 t2 on t1.c1=t2.c2
    LEFT OUTER JOIN
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
left outer join table2 t2 on t1.c1=t2.c2
    LEFT OUTER JOIN (excluding INNER JOIN)
    Table1 Table1 / Table2 Table2 
    select t1.rownum1,t1.c1
from table1 t1
left outer join table2 t2 on t1.c1=t2.c2
where t2.rownum2 is null
    RIGHT OUTER JOIN
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
right outer join table2 t2 on t1.c1=t2.c2
    RIGHT OUTER JOIN (excluding INNER JOIN)
    Table1 Table1 / Table2 Table2 
    select t2.rownum2,t2.c2
from table1 t1
right outer join table2 t2 on t1.c1=t2.c2
where t1.rownum1 is null
    FULL OUTER JOIN
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
full outer join table2 t2 on t1.c1=t2.c2
    FULL OUTER JOIN (excluding INNER JOIN)
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
full outer join table2 t2 on t1.c1=t2.c2
where t1.rownum1 is null or t2.rownum2 is null
    CROSS JOIN
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1,table2 t2
    Semi join
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
where exists
(
  select 1
  from table2 t2
  where t2.c2=t1.c1
)
    Anti join
    Table1 Table1 / Table2 Table2 
    select *
from table1 t1
where not exists
(
  select 1
  from table2 t2
  where t2.c2=t1.c1
)