Contents
What is an Outer Join (OUTER JOIN clause)? – How to join data in SQL query
In SQL (Structured Query Language), joining tables is a common operation that allows us to combine rows from multiple tables based on a related column. It enables us to retrieve data that is distributed across different database tables to answer complex questions.
An Outer Join, as the name suggests, is a type of join that includes not only the matching rows from both tables but also the unmatched rows from one or both tables. This is different from an Inner Join, which only includes the matching rows.
When performing an Outer Join, we use the OUTER JOIN clause in our SQL query. This clause comes in three variations: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
1. LEFT OUTER JOIN:
In a LEFT OUTER JOIN, all the rows from the left table (the one specified before the JOIN keyword in the query) are included in the result set. If there are matching rows in the right table, they are also included. However, if there are no matching rows in the right table, NULL values are used to fill in the columns from the right table.
A LEFT OUTER JOIN is useful when we want to retrieve all the records from the left table along with any matching records from the right table.
2. RIGHT OUTER JOIN:
Conversely, in a RIGHT OUTER JOIN, all the rows from the right table (the one specified after the JOIN keyword in the query) are included in the result set. If there are matching rows in the left table, they are also included. In case there are no matching rows in the left table, NULL values are used for the columns from the left table.
A RIGHT OUTER JOIN is helpful when we want to retrieve all the records from the right table along with any matching records from the left table.
3. FULL OUTER JOIN:
A FULL OUTER JOIN combines the results of both the LEFT and RIGHT OUTER JOIN. It includes all the rows from both tables, matching rows as well as unmatched rows from either table. If there are no matches, NULL values are used to fill in the columns from the table without a match.
A FULL OUTER JOIN is particularly useful when we want to retrieve all the records from both tables and evaluate the overlap or discrepancies in the data.
To summarize: An Outer Join (OUTER JOIN clause) is a way to combine rows from multiple tables in a SQL query. It includes matching rows as well as unmatched rows from one or both tables. The three variations of the OUTER JOIN clause are LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, each serving different purposes in retrieving data from the tables.
Reference Articles
Read also
[Google Chrome] The definitive solution for right-click translations that no longer come up.