WHERE clause

Description

The WHERE clause is used to limit the results of the FROM clause of a query or a subquery based on the specified condition.

Syntax

  1. WHERE boolean_expression

Parameters

  • boolean_expression

    Specifies any expression that evaluates to a result type boolean. Two or more expressions may be combined together using the logical operators ( AND, OR ).

Examples

  1. CREATE TABLE person (id INT, name STRING, age INT);
  2. INSERT INTO person VALUES
  3.     (100, 'John', 30),
  4.     (200, 'Mary', NULL),
  5.     (300, 'Mike', 80),
  6.     (400, 'Dan',  50);
  8. -- Comparison operator in `WHERE` clause.
  9. SELECT * FROM person WHERE id > 200 ORDER BY id;
  10. +---+----+---+
  11. | id|name|age|
  12. +---+----+---+
  13. |300|Mike| 80|
  14. |400| Dan| 50|
  15. +---+----+---+
  17. -- Comparison and logical operators in `WHERE` clause.
  18. SELECT * FROM person WHERE id = 200 OR id = 300 ORDER BY id;
  19. +---+----+----+
  20. | id|name| age|
  21. +---+----+----+
  22. |200|Mary|null|
  23. |300|Mike|  80|
  24. +---+----+----+
  26. -- IS NULL expression in `WHERE` clause.
  27. SELECT * FROM person WHERE id > 300 OR age IS NULL ORDER BY id;
  28. +---+----+----+
  29. | id|name| age|
  30. +---+----+----+
  31. |200|Mary|null|
  32. |400| Dan|  50|
  33. +---+----+----+
  35. -- Function expression in `WHERE` clause.
  36. SELECT * FROM person WHERE length(name) > 3 ORDER BY id;
  37. +---+----+----+
  38. | id|name| age|
  39. +---+----+----+
  40. |100|John|  30|
  41. |200|Mary|null|
  42. |300|Mike|  80|
  43. +---+----+----+
  45. -- `BETWEEN` expression in `WHERE` clause.
  46. SELECT * FROM person WHERE id BETWEEN 200 AND 300 ORDER BY id;
  47. +---+----+----+
  48. | id|name| age|
  49. +---+----+----+
  50. |200|Mary|null|
  51. |300|Mike|  80|
  52. +---+----+----+
  54. -- Scalar Subquery in `WHERE` clause.
  55. SELECT * FROM person WHERE age > (SELECT avg(age) FROM person);
  56. +---+----+---+
  57. | id|name|age|
  58. +---+----+---+
  59. |300|Mike| 80|
  60. +---+----+---+
  62. -- Correlated Subquery in `WHERE` clause.
  63. SELECT * FROM person AS parent
  64.     WHERE EXISTS (
  65.         SELECT 1 FROM person AS child
  66.         WHERE parent.id = child.id AND child.age IS NULL
  67.     );
  68. +---+----+----+
  69. |id |name|age |
  70. +---+----+----+
  71. |200|Mary|null|
  72. +---+----+----+