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

Answer 1

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

Answer 2

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.

Answer 3

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;
$$;