Subqueries
Explore how to use SQL subqueries as an alternative to JOINs for retrieving related data with unmatched rows. Learn to write nested queries step-by-step, manage filters, and understand when to choose subqueries over JOINs to simplify complex data operations.
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 ...