Multi-row Subquery Comparisons

Multi-row Subquery Comparisons is a SQL statement in the Subqueries category. Subquery returning multiple columns, compared with tuple syntax (row value comparison). The syntax is SELECT * FROM table WHERE (col1, col2) IN (SELECT col_a, col_b FROM other_table);. It returns tuple-matched rows. A typical example: SELECT * FROM employees WHERE (dept_id, salary) IN ( SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id ); -- Employees who earn the highest salary in their department A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query. A close relative is EXISTS, which returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets.