Compartir vía

WHERE, cláusula

  • Artículo

Se aplica a:casilla marcada como Sí Databricks SQL casilla marcada como Sí Databricks Runtime

Limita los resultados de la cláusula FROM de una consulta o subconsulta en función de la condición especificada.

Sintaxis

WHERE boolean_expression

Parámetros

  • boolean_expression

    Cualquier expresión que se evalúa como un tipo de resultado BOOLEAN. Puede combinar dos o más expresiones mediante operadores lógicos como AND o OR.

Ejemplos

> 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