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 ...