SQL queries and filters: What's the difference between a regular WHERE filter and a true/false filter?

user_16643284 0
Viewed 36

main.table:

Order_ID order_value
123 $100
345 $1000

other.table:

client_id order_id order_type
1 123 domestic
2 345 international

Now, I want to join these two tables and create a new table in my BI tool with a bunch of useful links for order_id that are domestic.

 CTE.v1 as(select ot.order_id, ot.order_type from other.table as ot where client_id in (1,2))

    Select 
    (mt.order_id) as id,
    (Select 'This is your useful link #1'),
    (Select 'This is your useful link #2')
    from main.table as mt 
    join CTE.v1 AS ot on mt.order_id = ot.order_id
    where order_type='domestic'

Now to my question: I've seen this CTE.v2 being used to achieve the same results with here:

        CTE.v2 as(
select ot.order_id, ot.order_value, 
iff(sum(iff(ot.order_type = 'Domestic', 1, 0)) > 0, true, false) is_domestic 
from other.table as ot where client_id in (1,2))
    
        Select 
        (mt.order_id) as id,
        (Select 'This is your useful link #1'),
        (Select 'This is your useful link #2')
        from main.table as mt 
         join CTE.v1 AS ot on mt.order_id = ot.order_id
        where ot.is_domestic

What's the difference between just filtering after performing the join with my CTE.v1, which seems more straightforward to me vs creating this true/false column and then filtering for it? Are there any benefits of v2 (true/false column) that I'm not seeing or is it just unnecessary to create a whole new column just to filter for something you can filter for right away?

snowflake-cloud-data-platform
1 Answers

SQL is a declarative language. You express the logic, and the processor (SQL engine) handles the workflow. Snowflake relies on this principle when optimizing the queries. Therefore these kinds of minor modifications mostly end up with the same execution plan.

By the way, I noticed that you put some extra aggregation on your second query, it should be like this:

with CTE as(
select ot.order_id, ot.order_type, 
ot.order_type = 'domestic'  is_domestic 
from other_table as ot where client_id in (1,2))
Select 
(mt.order_id) as id,
(Select 'This is your useful link #1'),
(Select 'This is your useful link #2')
from main_table as mt 
join CTE AS ot on mt.order_id = ot.order_id
where ot.is_domestic;

I run the queries and verified that both of them have the same execution plans. You can also use the query profile and see that Snowflake generates the same plan, and apply the same filters to both queries.

https://docs.snowflake.com/en/user-guide/ui-query-profile.html

In summary, there is no difference unless you hit a bug :)

user_125509650
Related