...
The goal is to convert a subquery to join if possible. This is possible in most cases for Important prerequisite is that number of resulting rows is not changed. Outer(FK) → inner(PK) joins are possible. E.g. subqueries in SELECT (aka "derived table") and FROM clauses.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT emp.name FROM emp, dept WHERE emp.salary>1000 AND emp.dept_id=dept.id AND dept.state='CA' |
Another example where inner subquery return at most one row is outer(PK) → inner(FK GROUP BY PK) case. Subquery is not eliminated in this case, but IN expression is replaced with more comfortable JOIN.
Example 4: Replacing IN with JOIN
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT dept.name
FROM dept
WHERE dept.id IN (SELECT emp.dept_id FROM emp GROUP BY emp.dept_id HAVING COUNT(*) > 10) |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT dept.name
FROM dept JOIN (SELECT emp.dept_id FROM emp GROUP BY emp.dept_id HAVING COUNT(*) > 10) emp_subquery ON dept.id=emp_subquery.dept_id |
[1] https://mariadb.com/kb/en/library/table-pullout-optimization/
...