description:

Oh where has my translation reference gone?

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);
  1. Use comparison operators (such as =, >, <, >=, <=) in the WHERE clause to filter data.
IDNAMEAGE
300Mike80
400Dan50
  1. Use comparison operators (=, <, >, <=, >=, !=) and logical operators (AND, OR, NOT) in the WHERE clause to filter data.
IDNAMEAGE
200Marynull
300Mike80
  1. Using IS NULL in the WHERE clause to check for null values.
IDNAMEAGE
200Marynull
400Dan50
  1. Using function expressions within a WHERE clause.
IDNAMEAGE
100John30
200Marynull
300Mike80
  1. Using the BETWEEN operator in a WHERE clause to filter data based on a range of values.
IDNAMEAGE
200Marynull
300Mike80
  1. Using a Scalar Subquery within a WHERE clause.
IDNAMEAGE
300Mike80
  1. A subquery in the WHERE clause that references columns from the outer query.
IDNAMEAGE
200Marynull

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);
  1. Use comparison operators (such as =, >, <, >=, <=) in the WHERE clause to filter data.
IDNAMEAGE
300Mike80
400Dan50
  1. Using comparison operators (such as =, <, >, <=, >=) and logical operators (such as AND, OR, NOT) in the WHERE clause to filter data.
IDNAMEAGE
200Marynull
300Mike80
  1. Using IS NULL in the WHERE clause to check for null values.
IDNAMEAGE
200Marynull
400Dan50
  1. Using function expressions within a WHERE clause.
IDNAMEAGE
100John30
200Marynull
300Mike80
  1. Using the BETWEEN operator in a WHERE clause to filter data based on a range of values.
IDNAMEAGE
200Marynull
300Mike80
  1. Using a Scalar Subquery within a WHERE clause.
IDNAMEAGE
300Mike80
  1. Correlated Subquery in WHERE clause.
IDNAMEAGE
200Marynull

Known Issues

No issues were found

No related EWIs