To refine data selection from a second table using data from the first table, which approach is most efficient?

• A. Subquery
• B. Aggregate
• C. Union
• D. Join

Answer: (A)

Filtering a result set based on values from another table is best done with a subquery. A subquery lets you define the criteria in the inner query using data from the first table and then use IN or EXISTS to restrict what you pull from the second table. This keeps the outer query focused on the second table’s columns while the inner query selects exactly which keys or criteria from the first table matter.

For example, you can select rows from the second table where a key appears in the first table, or where related rows in the first table meet a condition. Using EXISTS can be efficient because the database can stop searching as soon as it finds a match, and the inner query can leverage indexes. Similarly, IN (SELECT …) keeps the logic clear and lets the optimizer work with the result set of the inner query.

A join could also combine data from both tables, but if you only need to filter the second table based on the first rather than return columns from both, a subquery often provides the most direct and efficient approach. Aggregation, union, and other operations don’t align with the goal of refining results using data from another table in this way.