Subqueries

Limitations of INNER JOIN

As mentioned earlier, JOIN is arguably the most powerful feature of SQL. The type of JOIN employed throughout this course is known as the INNER JOIN. As illustrated, it exclusively fetches rows with matching columns from both tables. While the INNER JOIN is a powerful tool for combining rows from two or more tables based on a related column and seems straightforward for basic tasks, it has limitations and there are scenarios where it falls short in obtaining the expected outcomes. Among these are its inability to include unmatched rows, which refer to the absence of corresponding records in one of the two tables, in the query results. That is, the INNER JOIN retrieves only the rows that have matching values in both joined tables. If there are unmatched rows in either table, indicating that corresponding records do not exist in one of the tables, they are excluded from the result set. This limitation may not be suitable for scenarios where you need to include all rows from one table, regardless of matches.

For example, consider generating a list of Products that do not appear in any sales transactions from our new set of reports (Report # 7). Creating this report solely with INNER JOIN is not feasible, as it necessitates the inclusion of products in the report that do not have matches (appear) in the Sale transaction data.

To manage such intricate situations, additional forms of JOIN, referred to as OUTER JOIN, are utilized. These encompass variations like LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN. The OUTER JOINs provide an extensive and potent range of features. Yet, crafting a query utilizing these JOIN clauses can sometimes present considerable challenges.