MySQL JOIN vs MySQL IN with SubQuery

Question

I have 2 table foo2 and foo3 with the pictures below. These tables have unique column -> id.

table foo2:

table foo3:

I want to select data from two tables with same id. I have 2 query like this

query1:

SELECT
  foo2.id,
  foo2.`name`
FROM
  foo2
WHERE foo2.id IN (SELECT id FROM foo3);

query2:

SELECT
    foo2.id,
    foo2.`name`
FROM
    foo2
INNER JOIN foo3 ON (foo2.id = foo3.id);

These queries return same data. I want to know which one is better? Which one is efficient?

Thanks for answers.

Depends on the data. For large data a join is normally better. — juergen d, Jul 19 '17 at 10:44
Use `EXPLAIN` and look what the query planner tells you: https://dev.mysql.com/doc/refman/5.7/en/using-explain.html — Christian Stade-Schuldt, Jul 19 '17 at 10:45
Logically, `name` is the `PRIMARY KEY`. Get rid of `id` and promote `name` to be the PK. — Rick James, Jul 19 '17 at 16:02

score 1 · Answer 1 · answered Jul 19 '17 at 10:44

If you want to know which performs better, then test the two queries on your data and on your system.

It is important to note that the two queries are not the same. They may return the same value on your data, but the JOIN could return duplicate rows, if there are duplicates in foo3.

As a personal preference, I would tend to use JOIN rather than IN if the two are going to return the same rows (say, because I know that foo3.id is unique).

score 0 · Answer 2 · answered Jul 19 '17 at 10:46

0

You can go with Query2 approach.

As mention by you in second approach INNER JOIN may good in scenario where table foo3 had millions of rows. So where clause may took more time then INNER JOIN.

answered Jul 19 '17 at 10:46

Sagar Gangwal

7,544
3
24
38

MySQL JOIN vs MySQL IN with SubQuery

2 Answers2