Exists

Transact-SQL subqueries using EXISTS statement transformation details

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

Some parts in the output code are omitted for clarity reasons.

Types of Subqueries

Subqueries can be categorized as correlated or uncorrelated:

A correlated subquery, refers to one or more columns from outside of the subquery. (The columns are typically referenced inside the WHERE clause of the subquery.) A correlated subquery can be thought of as a filter on the table that it refers to, as if the subquery were evaluated on each row of the table in the outer query.

An uncorrelated subquery, has no such external column references. It is an independent query, the results of which are returned to and used by the outer query once (not per row).

The EXISTS statement is considered a correlated subquery.

Source Code

IN -> SqlServer_01.sql

-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcExists
AS
BEGIN
IF(EXISTS(Select AValue from ATable))
  return 1;
END;

Expected Code

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE ProcExists ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.

  if (SELECT(`   EXISTS(Select
         AValue
      from
         ATable
   )`)) {
    return 1;
  }
$$;

Known Issues

No issues were found.

Related EWIs

No related EWIs.