Description\n-----------\n\nA subquery can quite often, but not in all cases, be rewritten as a JOIN.\n\nRewriting Subqueries as JOINS\n\nA subquery using IN can be rewritten with the DISTINCT keyword. Consider this query:\n\n``sql\nSELECT  FROM table1 WHERE col1 IN (SELECT col1 FROM table2);\n`\n\nIt can be rewritten like this:\n\n`sql\nSELECT DISTINCT table1. FROM table1, table2 WHERE table1.col1=table2.col1;\n`\n\nNOT IN or NOT EXISTS queries can also be rewritten. For example, these two queries return the same result:\n\n`sql\nSELECT  FROM table1 WHERE col1 NOT IN (SELECT col1 FROM table2);\nSELECT  FROM table1 WHERE NOT EXISTS (SELECT col1 FROM table2 \n         WHERE table1.col1=table2.col1);\n`\n\nThey can both be rewritten like this:\n\n`sql\nSELECT table1. FROM table1 LEFT JOIN table2 ON table1.id=table2.id \n       WHERE table2.id IS NULL;\n`\n\nSubqueries that can be rewritten as a LEFT JOIN are sometimes more efficient.\n\nUsing Subqueries instead of JOINS\n\nThere are some scenarios, though, which call for subqueries rather than joins:\n\n When you want duplicates, but not false duplicates. Suppose Table_1 has three rows — {1,1,2}\\\n  — and Table_2 has two rows\\\n  — {1,2,2}. If you need to list the rows\\\n  in Table_1 which are also in Table_2, only this subquery-based SELECT statement will give the right answer (1,1,2):\n\n`sql\nSELECT Table_1.column_1 \nFROM   Table_1 \nWHERE  Table_1.column_1 IN \n  (SELECT Table_2.column_1 \n    FROM   Table_2);\n`\n\n This SQL statement won't work:\n\n`sql\nSELECT Table_1.column_1 \nFROM   Table_1,Table_2 \nWHERE  Table_1.column_1 = Table_2.column_1;\n`\n\n because the result will be {1,1,2,2}\\\n  — and the duplication of 2 is an error. This SQL statement won't work either:\n\n`sql\nSELECT DISTINCT Table_1.column_1 \nFROM   Table_1,Table_2 \nWHERE  Table_1.column_1 = Table_2.column_1;\n`\n\n because the result will be {1,2} — and the removal of the duplicated 1 is an error too.\n When the outermost statement is not a query. The SQL statement:\n\n`sql\nUPDATE Table_1 SET column_1 = (SELECT column_1 FROM Table_2);\n`\n\n can't be expressed using a join unless some rare SQL3 features are used.\n When the join is over an expression. The SQL statement:\n\n`sql\nSELECT  FROM Table_1 \nWHERE column_1 + 5 =\n  (SELECT MAX(column_1) FROM Table_2);\n`\n\n is hard to express with a join. In fact, the only way we can think of is this SQL statement:\n\n`sql\nSELECT Table_1.\nFROM   Table_1, \n      (SELECT MAX(column_1) AS max_column_1 FROM Table_2) AS Table_2\nWHERE  Table_1.column_1 + 5 = Table_2.max_column_1;\n`\n\n which still involves a parenthesized query, so nothing is gained from the transformation.\n When you want to see the exception. For example, suppose the question is: Which books are longer than Das Kapital? These two queries are effectively almost the same:\n\n`sql\nSELECT DISTINCT Bookcolumn_1.                     \nFROM   Books AS Bookcolumn_1 JOIN Books AS Bookcolumn_2 USING(page_count) \nWHERE  title = 'Das Kapital';\n\nSELECT DISTINCT Bookcolumn_1. \nFROM   Books AS Bookcolumn_1 \nWHERE  Bookcolumn_1.page_count > \n  (SELECT DISTINCT page_count \n  FROM   Books AS Bookcolumn_2 \n  WHERE  title = 'Das Kapital');\n`\n\n The difference is between these two SQL statements is, if there are two editions of Das Kapital (with different page counts), then the self-join example will return the books which are longer than the shortest edition of Das Kapital. That might be the wrong answer, since the original question didn't ask for "... longer than ANY` book named Das Kapital" (it seems to contain a false assumption that there's only one edition).\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins-subqueries/subqueries/subqueries-and-joins