Mastering Oracle SQL

Chapter 3. Joins

Most of the things in life are not self-contained. There is not one shop where you will find all your requirements. This is valid for database tables as well. Quite often, you need information from more than one table. The SQL construct that combines data from two or more tables is called a join. This chapter takes you into the details of joins, their types, and their usage.

A join is a SQL query that extracts information from two or more tables or views. When you specify multiple tables or views in the FROM clause of a query, Oracle performs a join, linking rows from multiple tables together. There are several types of joins to be aware of:

Inner joins

Inner joins are the regular joins. An inner join returns the rows that satisfy the join condition. Each row returned by an inner join contains data from all tables involved in the join.

Outer joins

Outer joins are an extension to the inner joins. An outer join returns the rows that satisfy the join condition and also the rows from one table for which no corresponding rows (i.e., that satisfy the join condition) exist in the other table.

Self joins

A self join is a join of a table to itself.

The following sections discuss each of these joins with examples.

Inner Joins

An inner join returns the rows that satisfy the join condition. Let’s take an example to understand the concept of a join. Say you want to list the name and department name for each employee. To do this, you would use the following SQL statement:

            SELECT ...