If exists (select top 1 1 from table) in Snowflake

Question

How to write the T-SQL below in SnowFlake

if exists (select top 1 1 from tableName)

This returns true or false

Update

I tried to run the if in the screenshot below in the Snowflake browser, but get the error:

https://docs.snowflake.com/en/sql-reference/snowflake-scripting/if.html

Can you explain a bit more what select top 1 1 from ... does? (Will provide clarity for me + future readers) — Yaakov Bressler, Aug 08 '22 at 15:11
@YaakovBressler It is just a way to help query engine to faster determine the result(access the first row(any) and return literal 1 instead of accessing actual column). For modern query optimizer it does not matter and it is the same as `IF EXISTS( SELECT * FROM tablename)` — Lukasz Szozda, Aug 08 '22 at 15:16
^ I meant, to contextualize the Q. But also, very helpful @LukaszSzozda TY — Yaakov Bressler, Aug 08 '22 at 16:48

score 3 · Answer 1 · answered Aug 08 '22 at 15:09

3

It depends on where you will use it:

create table tableName( id number);

SELECT  exists (select top 1 1 from tableName);
-- returns false

insert into tablename values (1 );

SELECT  exists (select top 1 1 from tableName);
-- returns true

answered Aug 08 '22 at 15:09

Gokhan Atil

9,278
2
11
24

Lukasz Szozda · Answer 2 · 2022-08-08T17:21:04.863

1

The direct equivalent of IF:

-- SQL Server
if exists (select top 1 1 from tableName)
 -- come code

is an anonymous block with branch construct(Snwoflake Scripting):

BEGIN
  IF (EXISTS(select top 1 1 from tableName)) THEN
    -- some code
  END IF;
END;

If Classic WebUI is used then Using Snowflake Scripting in SnowSQL and the Classic Web Interface:

EXECUTE IMMEDIATE $$
BEGIN
  IF (EXISTS(select top 1 1 from tableName)) THEN
    RETURN 1;
  END IF;
END;
$$;

edited Aug 08 '22 at 17:21

answered Aug 08 '22 at 15:13

Lukasz Szozda

162,964
23
234
275

Pls see my update, any idea for the error? This is why I thought I had the wrong syntax, but it is not. – Pingpong Aug 08 '22 at 17:07
@Pingpong Either switch to Snowsight or wrap the begin/end block with EXECUTE IMMEDIATE – Lukasz Szozda Aug 08 '22 at 17:16

Persixty · Answer 3 · 2022-08-08T15:18:39.310

This expression evaluates true if (and only if) the table tablename contains any data (that is 1 or more rows).

IF EXISTS ( 
    SELECT TOP 1
        1
    FROM tablename
)

It should have the same effect as

IF EXISTS ( 
    SELECT 
        *
    FROM tablename
)

I don't know but I would expect Snowflake to be smart enough to do no more than determine if there are results in either case.

The second form is the common SQL idiom in my experience.

If exists (select top 1 1 from table) in Snowflake

3 Answers3