Snowpark Migration Accelerator: Where¶
Description¶
Filters the data returned by a query or subquery based on specified conditions. (Databricks SQL Language Reference WHERE)
The WHERE
clause filters data by defining specific conditions that must be met. (Snowflake SQL Language Reference WHERE)
Syntax¶
WHERE <boolean_expression>
...
WHERE <predicate>
[ ... ]
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);
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);
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);
Use comparison operators (such as =, >, <, >=, <=) in the
WHERE
clause to filter data.
ID |
NAME |
AGE |
---|---|---|
300 |
Mike |
80 |
400 |
Dan |
50 |
Use comparison operators (=, <, >, <=, >=, !=) and logical operators (AND, OR, NOT) in the
WHERE
clause to filter data.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
300 |
Mike |
80 |
Using
IS NULL
in theWHERE
clause to check for null values.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
400 |
Dan |
50 |
Using function expressions within a
WHERE
clause.
ID |
NAME |
AGE |
---|---|---|
100 |
John |
30 |
200 |
Mary |
null |
300 |
Mike |
80 |
Using the
BETWEEN
operator in aWHERE
clause to filter data based on a range of values.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
300 |
Mike |
80 |
Using a Scalar Subquery within a
WHERE
clause.
ID |
NAME |
AGE |
---|---|---|
300 |
Mike |
80 |
A subquery in the
WHERE
clause that references columns from the outer query.
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);
Use comparison operators (such as =, >, <, >=, <=) in the
WHERE
clause to filter data.
ID |
NAME |
AGE |
---|---|---|
300 |
Mike |
80 |
400 |
Dan |
50 |
Using comparison operators (such as =, <, >, <=, >=) and logical operators (such as AND, OR, NOT) in the
WHERE
clause to filter data.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
300 |
Mike |
80 |
Using
IS NULL
in theWHERE
clause to check for null values.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
400 |
Dan |
50 |
Using function expressions within a
WHERE
clause.
ID |
NAME |
AGE |
---|---|---|
100 |
John |
30 |
200 |
Mary |
null |
300 |
Mike |
80 |
Using the
BETWEEN
operator in aWHERE
clause to filter data based on a range of values.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
300 |
Mike |
80 |
Using a Scalar Subquery within a
WHERE
clause.
ID |
NAME |
AGE |
---|---|---|
300 |
Mike |
80 |
Correlated Subquery in
WHERE
clause.
ID |
NAME |
AGE |
---|---|---|
200 |
Mary |
null |
Known Issues¶
No issues were found