How to use JOINS instead of sub query

Question

SELECT *
FROM table_a 
LEFT JOIN table_b ON (table_b.id = table_a.id) 
WHERE table_b.created BETWEEN ? AND ? 
      AND table_b.manager IN(
        SELECT DISTINCT (b.id) 
        FROM table_b a 
          INNER JOIN table_b b ON a.manager = b.id 
            AND b.user_level > 0
            AND b.id != 1
      )

How can I remove the sub query and use JOINS instead in the above query

Thanks

please show us your table's structure and example data, and an example of the output you want. — CodeBird, Jul 17 '14 at 09:28
A join is not necessary a replacement for a sub-query using `IN` - the results may be different (depending on your data model and data) — , Jul 17 '14 at 09:30

score 0 · Answer 1 · edited May 23 '17 at 11:50

MySQL 5.5 (and lower version)'s optimizer produces a DEPENDENT SUBQUERY for IN (SELECT ...) which means every matching row is found, IN(SELECT ...) is evaluated that makes select slow.

Simple optimization of your query is make inline view as follows:

SELECT *
FROM table_a LEFT JOIN table_b ON (table_b.id = table_a.id) 
    INNER JOIN (
            SELECT DISTINCT b.id AS id
            FROM table_b a 
              INNER JOIN table_b b ON a.manager = b.id 
                AND b.user_level > 0
                AND b.id != 1
    ) x ON table_b.manager = x.id
WHERE table_b.created BETWEEN ? AND ? 
  AND x.id IS NOT NULL;

I think avobe query would produce as same as your own.

Last, DISTINCT is not a function. please use SELECT DISTINCT b.id rather SELECT DISTINCT (b.id)

How to use JOINS instead of sub query

1 Answers1