EXISTS

Description

EXISTS conditions test for the existence of rows in a subquery, and return true if a subquery returns at least one row. If NOT is specified, the condition returns true if a subquery returns no rows. (Redshift SQL Language Reference EXISTS condition)

Grammar Syntax

[ NOT ] EXISTS (table_subquery)

This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table

First Tab

CREATE TABLE ExistsTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
);

INSERT INTO ExistsTest (id, name, lastname) VALUES
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);

Second Tab

CREATE TABLE ExistsTest (
    id INTEGER,
    name VARCHAR(30),
    lastname VARCHAR(30)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/08/2025",  "domain": "test" }}'

INSERT INTO ExistsTest (id, name, lastname) VALUES
 (1, 'name1', 'lastname1'),
 (2, 'name2', NULL),
 (3, 'name3', 'lastname3'),
 (4, 'name4', NULL);

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT * FROM ExistsTest
WHERE EXISTS (
SELECT 1 FROM ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;

Results

ID	NAME	LASTNAME
1	name1	lastname1
2	name2	NULL
3	name3	lastname3
4	name4	NULL

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT * FROM
ExistsTest
WHERE EXISTS (
SELECT 1 FROM
ExistsTest
WHERE lastname = 'lastname1'
)
ORDER BY id;

Results

ID	NAME	LASTNAME
1	name1	lastname1
2	name2	NULL
3	name3	lastname3
4	name4	NULL

Related EWIs

No related EWIs.

Known Issues

No issues were found.