In a relational data model, it is often considered as the best practice to distribute data across multiple tables, rather than store it in a single table. This is because splitting data into related tables improves data organization, reduces redundancy and enhances data integrity. This practice is known as data normalization.
However, data normalization inevitably mandates table joins. This is because even a single piece of information could be spread across many tables, with each table representing a distinct entity. Thus, to retrieve a complete set of related data, your SQL queries must combine information from these tables.
The table join is a fundamental concept in SQL, allowing you to link rows from two or more tables based on a related column. Oracle SQL supports four different types of table joins: inner join, left join, right join, and full outer join. In this guide, we will explore each type of join, explain their differences, and demonstrate how to use them effectively to retrieve data from related tables.
Understanding the Basic Syntax of the JOIN Clause
In a SELECT statement, the JOIN clause is placed within the FROM clause. As part of the entire FROM clause, it defines the initial set of rows by combining two tables based on a common column. This implies that the syntax of the JOIN clause requires three pieces of information:
- What are the two tables that you're trying to combine?
- When combining the rows from the two tables, which column value should be matched?
- When there are is any non-matching values in the common column, how the associated rows should be treated?
If you can clearly answer these three questions, you can easily construct the JOIN clause using the following syntax:
SELECT t1.column1, t1.column2, t2.column1, t2.column3, ...FROM left_table t1 {INNER | LEFT | RIGHT | FULL OUTER} JOIN right_table t2 ON t1.column1 = t2.column1;
- SELECT t1.column1, t1.column2, t2.column1, t2.column3, ...:
- Specifies the columns you want to retrieve. Columns can come from both tables (left_table and right_table).
- t1 and t2 are table aliases that represent the left and right tables, respectively.
- FROM left_table t1:
- Defines the first (left) table that is involved in the join. t1 is an alias for the left table, which is mandatory in Oracle SQL.
- {INNER|LEFT|RIGHT|FULL OUTER} JOIN right_table t2:
- Specifies the type of join to use:
- INNER JOIN: Returns only rows where there is a match in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table. If there's no match, the result will include NULLs for the right table columns.
- RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN but returns all rows from the right table and matching rows from the left table.
- FULL OUTER JOIN: Returns rows when there is a match in one of the tables. It returns all rows from both tables, and where there's no match, NULLs are included.
- ON t1.column1 = t2.column2:
- Defines the condition that specifies how the rows should be matched between the two tables. It usually involves foreign key and primary key columns, which logically link the related data in the two tables.
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.
Aside: Equi-Joins
Subqueries can also be used as a method of table joins. By employing correlated subqueries in the WHERE clause, we can establish relationships between tables and perform various join operations, including equi-joins, semi-joins, and anti-joins.
An equi-join combines rows from two tables based on a specific equality condition between columns in both tables. You can think of it as a sort of inner join. Both are executed by the same execution plan and there is no performance difference.
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.
0 Comments