Oracle SQL Certification 1Z0-071 Course | Section 11: Displaying Data from Multiple Tables – Joins

Joins are heavily tested in the Oracle SQL 1Z0-071 exam and appear constantly in real production queries. Understanding how each join works, when to use it, and how it affects result sets is critical for both certification success and practical SQL work.

In real world databases, useful information is rarely stored in a single table. Relational databases are designed to split data across multiple related tables, which makes joins one of the most important SQL skills to master. This section focuses on how to retrieve meaningful data by combining rows from multiple tables using different types of joins.

Types of Joins

A join combines rows from two or more tables based on a related column between them, usually a primary key and a foreign key.

At a high level, joins fall into these categories:

  • Inner joins
  • Outer joins
  • Natural joins
  • Joins using the USING clause
  • Self joins
  • Non equijoins
  • Oracle legacy joins

Each type serves a specific purpose and produces different results depending on whether matching rows exist.

Inner Joins

Inner joins are the most commonly used join type. They return only rows where matching values exist in both tables.

Key characteristics of inner joins:

  • Rows without matches are excluded
  • Typically join primary keys to foreign keys
  • Use the JOIN … ON syntax
  • Require clear join conditions to avoid Cartesian results

Inner joins are ideal when you only want records that exist in both tables. For example, employees who belong to a valid department.

Exam tip: If no matching row exists in either table, the row does not appear in the result.

Outer Joins

Outer joins return rows even when matching data does not exist in one of the tables.

Types of outer joins:

  • LEFT OUTER JOIN returns all rows from the left table
  • RIGHT OUTER JOIN returns all rows from the right table
  • FULL OUTER JOIN returns all rows from both tables

When a match does not exist, Oracle fills the missing side with NULL values.

Outer joins are useful when you want to find missing or unmatched data, such as departments without employees or employees without managers.

Exam tip: NULL values appearing in results are a strong indicator that an outer join is being used.

Natural Joins

A natural join automatically joins tables based on columns with the same name and compatible data types.

Important behaviors:

  • Oracle determines the join condition automatically
  • Default behavior is an inner join
  • Can also be combined with LEFT, RIGHT, or FULL joins
  • Duplicate join columns appear only once in the output

Natural joins can make queries shorter, but they can also introduce risk if column names change or multiple matching columns exist.

Exam tip: Natural joins rely entirely on column names. Be cautious when reading questions that mention automatic join conditions.

Joins Using the USING Clause

The USING keyword is a cleaner and safer alternative to natural joins when column names match.

Key points:

  • Explicitly defines which column is used for joining
  • Works only when join columns have the same name
  • Can join on one or multiple columns
  • Join column appears once in the output

USING provides clarity without repeating table prefixes, making queries easier to read while remaining predictable.

Exam tip: USING can be combined with INNER, LEFT, RIGHT, and FULL joins.

Multitable Joins

Multitable joins combine three or more tables in a single query. These are extremely common in real world databases where information is spread across many related tables.

Important concepts:

  • Each join must have a valid condition
  • Foreign keys define how tables are connected
  • Joins are evaluated left to right
  • Both inner and outer joins can be mixed carefully

Being able to read an ERD and determine how tables connect is essential for writing correct multitable joins.

Exam tip: Missing join conditions in multitable queries often lead to incorrect row counts.

Self Joins

A self join occurs when a table is joined to itself. This is commonly used for hierarchical data.

Typical use cases:

  • Employee manager relationships
  • Organizational hierarchies
  • Parent child relationships stored in one table

Aliases are mandatory in self joins to distinguish between the two logical instances of the same table.

Exam tip: Self joins often appear in questions involving managers, supervisors, or reporting structures.

Non Equijoins

Non equijoins join tables using conditions other than equality.

Key characteristics:

  • Use operators like BETWEEN, greater than, or less than
  • Often used with range based data
  • Common in grading systems, salary bands, or category mapping

Unlike equijoins, non equijoins do not rely on exact matches between columns.

Exam tip: Look for BETWEEN in join conditions to identify non equijoins.

Oracle Legacy Joins

Before ANSI join syntax became standard, Oracle used join conditions inside the WHERE clause.

Key points:

  • Join conditions appear in WHERE, not JOIN
  • Outer joins use the plus sign
  • Full outer joins are not directly supported
  • UNION is required to simulate full outer joins

Although ANSI joins are recommended today, legacy joins still appear in older Oracle systems and exam questions.

Exam tip: Be able to read legacy join syntax even if you do not use it in practice.

Final Exam Tips for Joins
  • Always identify join type first
  • Inner joins exclude unmatched rows
  • Outer joins introduce NULL values
  • USING and NATURAL JOIN reduce duplication
  • Self joins require aliases
  • Non equijoins use ranges, not equality
  • Legacy joins still matter for reading old code

Mastering joins is a major milestone in SQL. Once you are comfortable combining tables, you unlock the real power of relational databases and move much closer to exam readiness and real world SQL proficiency.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter