cláusula WHERE

Aplica-se a: SQL do Databricks Runtime do Databricks

Limita os resultados da cláusula FROM de uma consulta ou uma subconsulta com base na condição especificada.

Sintaxe

WHERE boolean_expression

Parâmetros

• boolean_expression

  Qualquer expressão avaliada como um tipo de resultado BOOLEAN. Você pode combinar duas ou mais expressões usando os operadores lógicos como AND ou OR.

Exemplos

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

-- Comparison operator in `WHERE` clause.
> SELECT * FROM person WHERE id > 200 ORDER BY id;
 300 Mike 80
 400  Dan 50

-- Comparison and logical operators in `WHERE` clause.
> SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;
 200 Mary NULL
 300 Mike   80

-- IS NULL expression in `WHERE` clause.
> SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;
 200 Mary null
 400  Dan   50

-- Function expression in `WHERE` clause.
> SELECT * FROM person WHERE length(name) > 3 ORDER BY id;
 100 John   30
 200 Mary NULL
 300 Mike   80

-- `BETWEEN` expression in `WHERE` clause.
SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;
 200 Mary NULL
 300 Mike   80

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

-- 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);
 200 Mary NULL

Artigos relacionados

• QUALIFY
• SELECT