CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-connector
Pingpong
Pingpong

Reputation: 8009

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

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: enter image description here

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

Upvotes: 3

Views: 10509

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

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

Upvotes: 1

Persixty
Persixty

Reputation: 8589

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.

Upvotes: 0

Gokhan Atil
Gokhan Atil

Reputation: 10109

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

Upvotes: 3

Related Questions