Why Does Mixing ANSI 1992 JOINs and Commas in MySQL Queries Cause 'Unknown column' Errors?

Problems caused by mixing ANSI 1992 JOIN and commas in MySQL queries

In a MySQL query, if you use both ANSI 1992 JOIN syntax and comma-delimited table join syntax, an "Unknown column 'm.id' in 'on clause'" error may occur. This is because mixing the two syntaxes can lead to query syntax ambiguity.

Problem Solution

According to the MySQL documentation, mixing comma-based table join syntax (for example, members as m, telephone as t) with explicit JOIN clauses is not recommended. To fix this, replace the commas with CROSS/INNER/LEFT JOIN statements.

Explanation of reasons

Previously, the comma operator and JOIN had the same precedence, but in newer MySQL versions, JOIN has higher precedence. Therefore, the comma operator no longer takes precedence over JOIN clauses. This means that the query interpreter incorrectly separates the operands, causing errors in the ON clause.

Modified query

To correct the syntax, rewrite the query using an explicit JOIN clause:

<code class="language-sql">SELECT m.*, t.*
FROM memebers AS m
JOIN telephone AS t ON m.id = t.member_id  -- 添加连接条件
JOIN memeberFunctions AS mf ON m.id = mf.memeber AND mf.function = 32
JOIN mitgliedTelephone AS mt ON m.id = mt.memeber;</code>

Other notes

Note that the modified query assumes a cross-join between m and t since you did not specify any criteria. If necessary, you may need to modify the query to include appropriate join conditions. I've added a hypothetical join condition ON m.id = t.member_id as an example, you'll need to modify this condition based on your actual database table structure.

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