Where

Oh where has my translation reference gone?

Description

Limits the results of the FROM clause of a query or a subquery based on the specified condition. (Databricks SQL Language Reference WHERE)

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

Syntax

Databricks

WHERE boolean_expression

Snowflake

...
WHERE <predicate>
[ ... ]

Sample Source Patterns

Setup data

Databricks

Query

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

Query

> 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

Query

-- 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);

Results

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

Query

-- 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);

Results

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

Related EWIs

No related EWIs