-1

From this page - https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html - it states that the following query:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

can be converted to an inner join as follows:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

However, from here - https://stackoverflow.com/questions/33653228/convert-sql-query-with-where-in-select-distinctid-from-a-to-join - I read that there is another way:

SELECT t1.* FROM t1 JOIN (SELECT DISTINCT t2.id FROM t2) t2 ON t1.id = t2.id;

Thus, which is the most correct and optimized way? If it is the latter, why didn't the MySQL doc recommend it?

  • Usually DISTINCT requires an extra pass over the data to de-dup it. – Rick James Dec 3 '19 at 3:14
0

Hi elaisha and welcome to DBA@SE.

SQL is a declarative and composeable language, and as such, you can achieve some task with varying types of syntax. Logically, these different queries are the same, and in an ideal world the query optimizer would be able to understand the essence of 'what' you want, and come up with the same execution plan regardless of the syntax you used.

In general, I recommend to start with the most concise supported syntax and check alternatives only if an issue arises. Except for the IN example, the other two fail the test - the first one uses an obsolete syntax for the join using comma separated table sources instead of the explicit INNER JOIN ... ON ..., and the last one is definitely not concise with its redundant sub query. Even if at some point in time, and with some database it proved to perform magically better than the syntax alternatives.

I would consider a 4th option which is both concise, correct, and IMHO the most readable. It also has a 'potential' for optimizer short cuts as part of it's logical evaluation as not all data needs to be processed, it's enough that one row satisfies the condition and there is no need to process additional rows for the same outer value from t1:

SELECT * FROM t1 WHERE EXISTS (SELECT NULL FROM t2 WHERE t1.id = t2.id);

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.