Join vs. sub-query

Taken from the MySQL manual (13.2.10.11 Rewriting Subqueries as Joins):

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

So subqueries can be slower than LEFT [OUTER] JOIN, but in my opinion their strength is slightly higher readability.

Related Posts:

  1. How can I do a FULL OUTER JOIN in MySQL?
  2. MySQL Multiple Joins in one query?
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  5. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  6. Unknown column in ‘field list’ error on MySQL Update query
  7. Unknown column in ‘field list’ error on MySQL Update query
  8. SQL Inner-join with 3 tables?
  9. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  10. MySQL – Operand should contain 1 column(s)
  11. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  12. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  13. MySQL create table if not exists and insert record only if table was created
  14. How do I use properly CASE..WHEN in MySQL
  15. Nested select statement in SQL Server
  16. MySQL query String contains
  17. What’s the difference between VARCHAR and CHAR?
  18. Case statement in MySQL
  19. How to declare a variable in MySQL?
  20. MySQL Cannot Add Foreign Key Constraint
  21. How to split the name string in mysql?
  22. How do I import an SQL file using the command line in MySQL?
  23. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  24. mysql Foreign key constraint is incorrectly formed error
  25. MySQL: Invalid use of group function
  26. How can I return pivot table output in MySQL?
  27. Difference between JOIN and INNER JOIN
  28. Compare dates in MySQL
  29. MySQL “WITH” clause
  30. What is the difference between JOIN and UNION?
  31. Error Code: 2013. Lost connection to MySQL server during query
  32. MySQL syntax for Join Update
  33. SQL join on multiple columns in same tables
  34. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  35. how to drop partition without dropping data in MySQL?
  36. 1052: Column ‘id’ in field list is ambiguous
  37. 1052: Column ‘id’ in field list is ambiguous
  38. MySQL combine two columns into one column
  39. Simple way to calculate median with MySQL
  40. Error 1046 No database Selected, how to resolve?
  41. MySQL Error 1264: out of range value for column
  42. Remove duplicate rows in MySQL
  43. How to SUM and SUBTRACT using SQL?
  44. Cast from VARCHAR to INT – MySQL
  45. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  46. How do you force mysql LIKE to be case sensitive?
  47. MySQL – UPDATE multiple rows with different values in one query
  48. Postgres Error: More than one row returned by a subquery used as an expression
  49. How to create a MySQL hierarchical recursive query?
  50. ERROR 1148: The used command is not allowed with this MySQL version
  51. Insert into a MySQL table or update if exists
  52. How to round down to nearest integer in MySQL?
  53. MySQL starts with searching issue
  54. Query to convert from datetime to date mysql
  55. Is it possible to GROUP BY multiple columns using MySQL?
  56. MySQL error: key specification without a key length
  57. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  58. Best way to test if a row exists in a MySQL table
  59. Check if MySQL table exists without using “select from” syntax?
  60. MySQL equivalent of DECODE function in Oracle
  61. mysql update column with value from another table
  62. The used SELECT statements have a different number of columns
  63. How to delete from multiple tables in MySQL?
  64. MySQL: Can’t create table (errno: 150)
  65. How to copy a row and insert in same table with a autoincrement field in MySQL?
  66. updating table rows in postgres using subquery
  67. Alternative to except in MySQL
  68. Equivalent of explode() to work with strings in MySQL
  69. ROW_NUMBER() in MySQ
  70. MySQL OPTIMIZE all tables?
  71. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  72. MySQL – Get row number on select
  73. MySQL error: Unknown column in ‘where clause’
  74. How to truncate the text returned for a column in a MySQL query
  75. Using union and order by clause in mysql
  76. MySQL LIKE IN()?
  77. Efficiently convert rows to columns in sql server
  78. Cannot delete or update a parent row: a foreign key constraint fails
  79. Sql Server equivalent of a COUNTIF aggregate function
  80. Column name or number of supplied values does not match table definition
  81. SQL – HAVING vs. WHERE
  82. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  83. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  84. How do I query for all dates greater than a certain date in SQL Server?
  85. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  86. postgresql: error duplicate key value violates unique constraint
  87. Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?
  88. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  89. Update statement with inner join on Oracle
  90. LEFT function in Oracle
  91. SQLite string contains other string query
  92. How to get the count of each distinct value in a column?
  93. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  94. Better way to migrate to server?
  95. Why does changing Database Table Prefixes result in loss of content?
  96. WP MultiSite: Can’t select database error after changing to new domain
  97. Help me SELECT thumbnail from SQL and use
  98. Database SQL query error
  99. Creating a “forum” – showing last post or last commented post
  100. Replace Unwanted Space in Post Content URL

Leave a Comment