CODEWITHSUNDEEP
mysqlselectnestedinner-join
pedmillon
pedmillon

Reputation: 143

Rewrite nested SELECT as INNER JOIN MySQL

I have a query using WHERE IN statement 

SELECT DISTINCT(col) 
FROM tab1 
WHERE id>71 AND id<5073 
AND col IN (SELECT DISTINCT(col) FROM tab0 WHERE id>55 AND id<320)

I tried to rewrite it using INNER JOIN, but I got fewer results. What am I doing wrong?

SELECT DISTINCT(t1.col) 
FROM tab1 as t1 
    INNER JOIN (
        SELECT DISTINCT(col) 
        FROM tab0 WHERE id>55 AND id<320
    ) AS t0o ON t1.col = t0o.col 
WHERE t1.id>71 AND t1.id<5073

Upvotes: 0

Views: 271

Answers (1)

SIDU
SIDU

Reputation: 2278

SELECT distinct a.col
FROM tab1 AS a
JOIN tab0 AS b
WHERE a.id > 71 AND a.id < 5073
AND b.id > 55 AND b.id < 320
AND a.col = b.col

Upvotes: 1

Related Questions