Snowpark Migration Accelerator: Where¶
Description¶
Filters the data returned by a query or subquery based on specified conditions. (Databricks SQL Language Reference WHERE (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-where.html))
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