Snowpark Migration Accelerator: Where

Description

Limits the results of the FROM clause of a query or a subquery based on the specified condition. (Databricks SQL Language Reference WHERE (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-where.html))

The WHERE clause specifies a condition that acts as a filter. (Snowflake SQL Language Reference WHERE)

Syntax

WHERE boolean_expression
Copy
...
WHERE <predicate>
[ ... ]
Copy

Sample Source Patterns

Setup data

Databricks

CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John',   30),
    (200, 'Mary', NULL),
    (300, 'Mike',   80),
    (400, 'Dan' ,   50);
Copy

Snowflake

> CREATE TABLE person (id INT, name STRING, age INT);
> INSERT INTO person VALUES
    (100, 'John',   30),
    (200, 'Mary', NULL),
    (300, 'Mike',   80),
    (400, 'Dan' ,   50);
Copy

Pattern code

Databricks

-- 1. Comparison operator in `WHERE` clause.
SELECT * FROM person WHERE id > 200 ORDER BY id;

-- 2. Comparison and logical operators in `WHERE` clause.
SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;

-- 3. IS NULL expression in `WHERE` clause.
SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;

-- 4. Function expression in `WHERE` clause.
SELECT * FROM person WHERE length(name) > 3 ORDER BY id;

-- 5. `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;

-- 6. Scalar Subquery in `WHERE` clause.
SELECT * FROM person WHERE age > (SELECT avg(age) FROM person);

-- 7. Correlated Subquery in `WHERE` clause.
SELECT * FROM person AS parent
   WHERE EXISTS (SELECT 1 FROM person AS child
                  WHERE parent.id = child.id
                    AND child.age IS NULL);
Copy

  1. Comparison operator in WHERE clause.

ID

NAME

AGE

300

Mike

80

400

Dan

50

  1. Comparison and logical operators in WHERE clause.

ID

NAME

AGE

200

Mary

null

300

Mike

80

  1. IS NULL expression in WHERE clause.

ID

NAME

AGE

200

Mary

null

400

Dan

50

  1. Function expression in WHERE clause.

ID

NAME

AGE

100

John

30

200

Mary

null

300

Mike

80

  1. BETWEEN expression in WHERE clause.

ID

NAME

AGE

200

Mary

null

300

Mike

80

  1. Scalar Subquery in WHERE clause.

ID

NAME

AGE

300

Mike

80

  1. Correlated Subquery in WHERE clause.

ID

NAME

AGE

200

Mary

null

Snowflake

-- 1. Comparison operator in `WHERE` clause.
SELECT * FROM person WHERE id > 200 ORDER BY id;

-- 2. Comparison and logical operators in `WHERE` clause.
SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;

-- 3. IS NULL expression in `WHERE` clause.
SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;

-- 4. Function expression in `WHERE` clause.
SELECT * FROM person WHERE length(name) > 3 ORDER BY id;

-- 5. `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;

-- 6. Scalar Subquery in `WHERE` clause.
SELECT * FROM person WHERE age > (SELECT avg(age) FROM person);

-- 7. Correlated Subquery in `WHERE` clause.
SELECT * FROM person AS parent
   WHERE EXISTS (SELECT 1 FROM person AS child
                  WHERE parent.id = child.id
                    AND child.age IS NULL);
Copy

  1. Comparison operator in WHERE clause.

ID

NAME

AGE

300

Mike

80

400

Dan

50

  1. Comparison and logical operators in WHERE clause.

ID

NAME

AGE

200

Mary

null

300

Mike

80

  1. IS NULL expression in WHERE clause.

ID

NAME

AGE

200

Mary

null

400

Dan

50

  1. Function expression in WHERE clause.

ID

NAME

AGE

100

John

30

200

Mary

null

300

Mike

80

  1. BETWEEN expression in WHERE clause.

ID

NAME

AGE

200

Mary

null

300

Mike

80

  1. Scalar Subquery in WHERE clause.

ID

NAME

AGE

300

Mike

80

  1. Correlated Subquery in WHERE clause.

ID

NAME

AGE

200

Mary

null

Known Issues

No issues were found

Language: English