Why Does Mixing ANSI 1992 Joins and Commas in MySQL Queries Cause Errors?

The reason why mixed use of ANSI 1992 JOIN syntax and commas in MySQL queries causes errors

When joining multiple tables in a MySQL query, be sure to consistently use ANSI 1992 JOIN syntax or comma-separated notation. Mixing these two methods can result in errors such as "Unknown column 'm.id' in 'on clause'" error.

Error reason

In the following query:

<code class="language-sql">SELECT m.*, t.*
FROM memebers as m, telephone as t
INNER JOIN memeberFunctions as mf ON m.id = mf.memeber
INNER JOIN mitgliedTelephone as mt ON m.id = mt.memeber
WHERE mf.function = 32</code>

This query attempts to use a mix of comma notation (used to join m and t) and ANSI 1992 JOIN syntax (used to join mf and mt). According to the MySQL documentation, this method is no longer supported. The comma operator now has lower precedence than JOIN, causing operand interpretation errors.

Solution

To resolve this issue, consistently use comma notation or ANSI 1992 JOIN syntax. For improved clarity and reliability, it is recommended to use JOIN syntax:

<code class="language-sql">SELECT m.*, t.*
FROM memebers AS m
JOIN telephone AS t ON m.id = t.id
JOIN memeberFunctions AS mf ON m.id = mf.memeber AND mf.function = 32
JOIN mitgliedTelephone AS mt ON m.id = mt.memeber</code>

Notes

• Note that the above query performs a Cartesian product between m and t, which can result in very large datasets. Be sure to include appropriate filters in your query.
• If using comma notation, be sure to specify the desired join type (e.g., JOIN, LEFT JOIN, RIGHT JOIN). Otherwise, the default is an inner connection, which may not be the desired connection type in some cases.

The above is the detailed content of Why Does Mixing ANSI 1992 Joins and Commas in MySQL Queries Cause Errors?. For more information, please follow other related articles on the PHP Chinese website!