USING Clause and explicit columns rename
![USING Clause and explicit columns rename](https://media.dwh.dev/blog/posts/using-clause-2.jpg)
It seems another "special behavior" of USING in Snowflake has been found.
If you missed it, I previously described a lot of strange things here.
CREATE TABLE t1 (a int, b int, c int);
CREATE TABLE t2 (d int, e int);
CREATE TABLE t3 (a int, b int, c int);
SELECT * FROM t1
JOIN t2 AS t(a,b) ON t1.a = t.a
JOIN t3 USING (a,b)
;
First, which columns will USING compare - t1 or t? There are 3 possibilities:
- It will take the FIRST matching ones (i.e., t1.a and t1.b)
- It will take the LAST ones (i.e., t.a and t.b)
- It will return an error for ambiguous column names
Hint: this query will execute without errors.
So it seems that we will get t1.a, t1.b, t1.c, t.a, t.b, t3.c
But no! We get t1.a, t1.b, t1.c, t3.c
Now let's try this:
CREATE TABLE t4 (a int, b int);
SELECT * FROM t1
JOIN t4 ON t1.a = t4.a
JOIN t3 USING (a,b)
;
Here we will get an error: ambiguous column name 'A'
Why does renaming columns in JOIN have such a strange effect?..