...
The goal is to convert a subquery to join if possible. This is possible in most cases for subqueries in SELECT (aka "derived table") and FROM clauses. Also, it is possible for some IN-clauses.
Example 1: JOIN conversion for derived table
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT emp.name, dept.name FROM emp, dept WHERE emp.salary>1000 AND emp.dept_id=dept.id |
Example 2: JOIN conversion for FROM clause
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT emp.name, dept.name FROM emp, dept WHERE emp.salary>1000 AND emp.dept_id=dept.id AND dept.state='CA' |
Also, it is possible for some IN-clauses. MariaDB calls it "table pullout optimization" [1]
Example 3: Table pullout optimization
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT emp.name
FROM emp
WHERE emp.salary>1000 AND emp.dept_id IN (SELECT id FROM dept WHERE state='CA') |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
SELECT emp.name
FROM emp, dept
WHERE emp.salary>1000 AND emp.dept_id=dept.id AND dept.state='CA' |
[1] https://mariadb.com/kb/en/library/table-pullout-optimization/
TODO
...