MTI TEK
  • Home
  • About
  • LLMs
  • Docker
  • Kubernetes
  • Java
  • All Resources
Oracle SQL | SQL JOIN Reference Guide
  1. Notes and Prerequisites
  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. Notes and Prerequisites
    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

    The following sample tables and their 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
    )
© 2025 mtitek