MERGE JOIN (Physical Operator)

MERGE JOIN (Physical Operator) is a SQL statement in the JOIN Types category. Merge join is efficient when both input tables are sorted on the join column. O(n+m) complexity. The syntax is -- Merge join: both inputs sorted on join key, then merged like zipping two sorted lists.. It returns merge join optimization. A typical example: -- Merge join is chosen when: -- 1. Both tables have indexes on the join column -- 2. JOIN condition is equality (=) -- 3. Tables are sorted by the join key -- Index on employees.dept_id and departments.id enables merge join: SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id; -- Merge join is: -- 1. O(n+m) = very fast for large tables -- 2. Good… A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type.