Can I use WHERE clause after JOIN USING in snowflake?

2
votes

Can I use WHERE after

JOIN USING?

In my case if I run on snowflake multiple times the same code:

with CTE1 as
(
select * 
from A
left join B 
on A.date_a = B.date_b
)
select * 
from CTE1
inner join C 
using(var1_int)
where CTE1.date_a >= date('2020-10-01')
limit 1000;

sometimes I get a result and sometimes i get the error:

SQL compilation error: Can not convert parameter 'DATE('2020-10-01')' of type [DATE] into expected type [NUMBER(38,0)]

where NUMBER(38,0) is the type of var1_int column

2
sqljoinwhere-clausesnowflake-cloud-data-platformusing
You totally changed the question after the 1st version after a correct answer. You should roll this back, accept the 1st answer & ask a new question. PS This is not clear. What example are you asking about, and what is your question about it? Please edit to give 1 query. The 1st code block with A & B has no minimal reproducible example. Of course the 2nd block returns a.date_1. Find the first point in the evaluation that doesn't return what you expect. Give that code. Say what you expect & why you expect it with justification from the manual. You still haven't given a minimal reproducible example for it because you haven't done that. – philipxy
You should first check the relational schemas in the database. Then you have to look at which column is the primary key which column is the foreign key. After checking them, you can join more efficiently. Finally, I see that the date type is number. You need to edit it according to the data type. I suggest you try again after applying these. – Mehmet Yalçın
the answer was not solving the problem and as said: the second block returns b.date_1 instead of a. – Adamik Ewert
That was not your question at the time of the first answer, you asked about an error & the answer answered it & also solved it & questions should not be changed in a way that invalidates reasonable answers. How to Ask help center Meta Stack Overflow Meta Stack Exchange – philipxy

2 Answers

1
votes

Your problem has nothing to do with the existence of a where clause. Of course you can use a where clause after joins. That is how SQL queries are constructed.

According to the error message, CTE1.date_a is a number. Comparing it to a date results in a type-conversion error. If you provided sample data and desired results, then it might be possible to suggest a way to fix the problem.

1
votes

tl;dr: Instead of JOIN .. USING() always prefer JOIN .. ON.

You are right to be suspicious of the results. Given your staging, only one of these queries returns without errors:

select a.date_1, id_1
from AE_USING_TST_A a
left join AE_USING_TST_B b 
on a.date_1 = b.date_2
join AE_USING_TST_C v
using(id_1)
where A.date_1 >= date('2020-10-01')

-- Can not convert parameter 'DATE('2020-10-01')' of type 
-- [DATE] into expected type [NUMBER(38,0)]
;


select a.date_1, a.id_1
from AE_USING_TST_A a
left join AE_USING_TST_B b 
on a.date_1 = b.date_2
join AE_USING_TST_C v
on a.id_1=v.id_1
where A.date_1 >= date('2020-10-01')

-- 2020-10-11   2
;

I would call this a bug, except that the documentation is clear about not doing this kind of queries with JOIN .. USING:

To use the USING clause properly, the projection list (the list of columns and other expressions after the SELECT keyword) should be “*”. This allows the server to return the key_column exactly once, which is the standard way to use the USING clause. For examples of standard and non-standard usage, see the examples below.

https://docs.snowflake.com/en/sql-reference/constructs/join.html

The documentation doubles down on the problems of using USING() on non-standard situations, with a different query acting "wrong":

The following example shows non-standard usage; the projection list contains something other than “*”. Because the usage is non-standard, the output contains two columns named “userid”, and the second occurrence (which you might expect to contain a value from table ‘r’) contains a value that is not in the table (the value ‘a’ is not in the table ‘r’).

So just prefer JOIN .. ON. For extra discussion on the SQL ANSI standard not defining behavior for some cases of USING() check: