What is the default MySQL JOIN behaviour, INNER or OUTER?

by

In MySQL writing JOIN unqualified implies INNER JOIN. In other words the INNER in INNER JOIN is optional. INNER and CROSS are synonyms in MySQL. For clarity I write JOIN or INNER JOIN if I have a join condition and CROSS JOIN if I don’t have a condition.

The allowed syntax for joins is described in the documentation.

Right now I’m thinking a stand-alone JOIN is nothing more than (identical to) using commas and WHERE clauses.

The effect is the same, but the history behind them is different. The comma syntax is from the ANSI-89 standard. However there are a number of problems with this syntax so in the ANSI-92 standard the JOIN keyword was introduced.

I would strongly recommend that you always use JOIN syntax rather than the comma.

  • T1 JOIN T2 ON ... is more readable than T1, T2 WHERE ....
  • It is more maintainable because the table relationships and filters are clearly defined rather than mixed together.
  • The JOIN syntax is easier to convert to OUTER JOIN than the comma syntax.
  • Mixing the comma and JOIN syntax in the same statement can give curious errors due to the precedence rules.
  • It is less likely to accidentally create a cartesian product when using the JOIN syntax due to a forgotten join clause, because the join clauses are written next to the joins and it is easy to see if one is missing.

More Related Contents:

  1. How can I select data from different tables?
  2. Combine Multiple child rows into one row MYSQL
  3. MySQL cartesian product between two SELECT statements
  4. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  5. How can I do three table JOINs in an UPDATE query?
  6. MySQL – UPDATE query based on SELECT Query
  7. 1052: Column ‘id’ in field list is ambiguous
  8. MySQL update table based on another tables value
  9. MySQL: is a SELECT statement case sensitive?
  10. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  11. MySQL join with where clause
  12. JOIN queries vs multiple queries
  13. Why does MySQL report a syntax error on FULL OUTER JOIN?
  14. Mixing ANSI 1992 JOINs and COMMAs in a query
  15. INSERT INTO…SELECT for all MySQL columns
  16. Cumulative sum over a set of rows in mysql
  17. MySql Count cannot show 0 values
  18. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  19. MySQL update a joined table
  20. MySQL difference between two rows of a SELECT Statement
  21. MySQL JOIN with LIMIT 1 on joined table
  22. MySQL – How to search for exact word match using LIKE?
  23. How do I get the first day of the week of a date in mysql?
  24. quick selection of a random row from a large table in mysql
  25. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  26. Laravel join with 3 Tables
  27. MySQL Insert & Joins
  28. SELECT query return 1 row from each group
  29. How to join two tables using a comma-separated-list in the join field
  30. How to store a one to many relation in MySQL database?

Leave a Comment