1. Which mapping of join algorithm to best use case is most accurate?
Difficulty: MediumType: MCQTopic: Query Performance & Optimizer
Nested loops shine when the outer side is small and the inner side can be probed by an index fast. Hash joins work well for large inputs and equality joins without indexes. Merge joins are ideal when both sides are ordered on the join keys, avoiding extra sorts. Knowing these trade-offs helps you design indexes and predicates that steer the optimizer toward efficient operators.
Example code
-- add index to favor nested loops CREATE INDEX idx_items_order ON order_items(order_id);