Multiple choice technology databases

Which query will have a better performance? a) SELECT count(), request_type_name FROM KCRT_REQUEST_TYPES WHERE request_type_id IN (SELECT request_type_id FROM kcrt_requests) GROUP BY request_type_name; OR b) SELECT count(), request_type_name FROM kcrt_requests kr,kcrt_request_types krt WHERE kr.request_type_id = krt.request_type_id GROUP BY request_type_name ;

  1. a

  2. b

  3. Can't tell

  4. both will perform the same
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Query (b) uses an explicit JOIN which is generally more efficient than the IN subquery in (a). The JOIN allows the optimizer to choose better execution plans and avoids the overhead of the subquery. IN clauses can be less efficient with large datasets.

AI explanation

Query (b) — the explicit join — is correct. Query (a) uses an unfiltered IN subquery against kcrt_requests, meaning the optimizer often must evaluate or materialize the subquery per outer row (or at best build a semi-join), and depending on the optimizer/version this can be less efficient than a direct join, especially on older cost-based optimizers or when indexes/statistics aren't ideal for subquery transformation. Query (b) directly joins KCRT_REQUEST_TYPES to kcrt_requests on the key column, letting the optimizer choose an efficient join method (hash/merge/nested-loop) with a single, straightforward access plan. As general SQL tuning guidance for this style of 'count grouped by a joined attribute' query, the explicit join form is the recommended and typically better-performing pattern over an unfiltered IN-subquery.

Keep practicing — related questions