CODEWITHSUNDEEP
mysqlgroup-bysubquery
Kristian
Kristian

Reputation: 3461

Mysql WHERE IN subquery

I'd like to list all rows having a match on same table. So far i have came up with this

SELECT *
FROM parim_firms
WHERE firm_name IN (
    SELECT firm_name
    FROM parim_firms
    GROUP BY firm_name
    HAVING COUNT(*) > 1
)

But this query keeps running, although the subquery itself runs in 0.1 sec.

How could i optimize this?

I think the subquery executes for each row, not only once. Am i right?

Upvotes: 2

Views: 107

Answers (1)

John Woo
John Woo

Reputation: 263733

how about joining it?

SELECT  a.*
FROM    parim_firms a
        INNER JOIN
        (
            SELECT firm_name
            FROM parim_firms
            GROUP BY firm_name
            HAVING COUNT(*) > 1
        ) b ON a.firm_name = b.firm_name

PS: be sure to add index on column firm_name for faster execution.

Upvotes: 4

Related Questions