Snowflake为何不对USING连接报告歧义列引用？其判定SQL列引用歧义的规则是什么？

Great observation! Let's unpack Snowflake's rules for resolving column reference ambiguity, and why it treats USING joins differently than ON joins (and PostgreSQL).

Here's the clear set of rules Snowflake follows when checking for ambiguous column references:

• Unqualified columns in ON joins: If an unqualified column name exists in two or more joined tables/subqueries, Snowflake will throw an ambiguous column reference error. This aligns with how PostgreSQL handles these cases.
• USING join special handling: When you use the USING clause to join on a shared column (like x in your example), Snowflake applies two key exceptions:
  • The column specified in USING is automatically deduplicated in the result set (you only get one instance of x, not one from each table).
  • For other duplicate column names (like y in your query), Snowflake uses a left-to-right precedence rule: it resolves the unqualified column to the one from the leftmost table in the join order.
• General rule: Snowflake only flags ambiguity if the unqualified column exists in multiple tables and isn't covered by the USING clause's special resolution logic.

Snowflake's design for USING joins prioritizes convenience and readability for common join patterns:

1. The USING clause is intended to simplify joins where you're matching on identical column names across tables. As part of that simplification, Snowflake assumes you want the shared join column (x) to be a single value, and for other duplicate columns, it defaults to the left table's value unless you explicitly qualify it.
2. This implicit precedence is a deliberate choice—Snowflake aims to reduce boilerplate in queries where the left table's column is the intended one (a common scenario when joining a main table with a lookup table, for example).

In contrast, PostgreSQL takes a stricter, "no assumptions" approach: it requires explicit qualification for any duplicate column name, even in USING joins, because it doesn't want to guess which table's column you intended to reference. That's why both of your queries hit ambiguity errors in PostgreSQL.

内容的提问来源于stack exchange，提问作者Christoph Hegemann