Table Joins

In a relational database, it is commonly regarded as a best practice to distribute data across multiple tables. While it might be tempting to store all data in a single table, using multiple tables and employing table joins offers several advantages in terms of database design, organization, and efficiency. Leveraging the relationships established by primary and foreign keys between entities, Oracle SQL supports four different types of table joins: inner joins, left outer joins, right outer joins, and full outer joins. 

In SQL, JOIN specifications are placed within the FROM clause of a SELECT statement. That is, the logical order of handling clauses is structured in a way that the FROM clause, which specifies the tables involved and any necessary joins, is processed first. Here's a basic structure of a SQL query with a JOIN clause, as well as other major clauses in a SELECT statement:

SELECT t1.column1, t1.column2, t2.column3, t2.column4, ... 
FROM left_table t1
    JOIN right_table t2 ON t1.column = t2.column
WHERE conditions
GROUP BY columns
HAVING conditions
ORDER BY columns;

In the syntax above, the left_table and the right_table are aliased by t1 and t2. Unlike some other RDBMS vendors, Oracle does not allow to use AS keywords to alias tables in a JOIN section. In the third line, the JOIN right_table t2 ON t1.column = t2.column indicates a join operation. In Oracle SQL, without specifying a specific type of join (such as LEFT or RIGHT), JOIN is interpreted as an INNER JOIN. So, the left table (t1) is inner joined with the right table (t2) based on the condition t1.column = t2.column. This means that rows are combined where the values in the specified columns are equal.

INNER JOIN

An inner join is a type of table joining operation in a relational database that returns only the rows where there is a match in both tables based on a specified condition. It is the most common type of join and is used to combine rows from two or more tables based on a related column. To illustrate, let's consider two tables resulted from the SQL commands below:

In the figure shown above, I created two tables: table_a and table_b. Then I added 3 rows to each table. With the added rows, inner joining two tables will result in:

As depicted in the figure shown above, the inner join query retrieved only two rows that have the matching key_column values in both tables.

LEFT JOIN and RIGHT JOIN

A left join is another type of table join in relational databases. It combines rows from two tables based on a specified condition, retrieving all the records from the left table and the matching records from the right table. Even if there are no matching records in the right table, the result will still include the rows from the left table with NULL values from the right table. For example:

In the figure shown above, we see that Oracle retrieved value_column values from t2, even when there is no matching key_column value.

The right join works as a mirror image of a left join. The only difference is that it retains all rows from the right table instead of the left table. In the cases where there is no matching row in the left table, the right join includes the row with NULL values.

For example:

Note that you can include the keyword OUTER in the left or right join: LEFT OUTER JOIN or RIGHT OUTER JOIN. However, there is no functional differences depending on the inclusion of the keyword. It's just a matter of personal preference or organizational coding styles.

FULL OUTER JOIN

Lastly, a FULL OUTER JOIN in SQL is a type of join operation that returns all rows from both tables being joined, with matched rows from both tables where the specified condition is met, as well as unmatched rows filled with NULL values in columns from the table that doesn't have a match.

A full outer join is less common than inner, left, or right joins, but it could be useful when you want to retrieve all records from both tables, regardless of whether there are matching rows.