WHERE Clause
The WHERE clause selects rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result.
Syntax
The WHERE clause supports the following syntax:
WHERE boolean_expression
Expression
A boolean expression can include one or more of the following operators:
- AND
- OR
- NOT
- IS NULL
- IS NOT NULL
- LIKE
- SIMILAR TO
- BETWEEN
- IN
- EXISTS
- Comparison operators
- Quantified comparison operators
Examples
The following query compares order totals where the states are California and New York:
0: jdbc:drill:> SELECT o1.cust_id, sum(o1.order_total) AS ny_sales,
(SELECT SUM(o2.order_total) FROM hive.orders o2
WHERE o1.cust_id=o2.cust_id and state='ca') AS ca_sales
FROM hive.orders o1 WHERE o1.state='ny' GROUP BY o1.cust_id
ORDER BY cust_id LIMIT 20;
|------------|------------|------------|
| cust_id | ny_sales | ca_sales |
|------------|------------|------------|
| 1001 | 72 | 47 |
| 1002 | 108 | 198 |
| 1003 | 83 | null |
| 1004 | 86 | 210 |
| 1005 | 168 | 153 |
| 1006 | 29 | 326 |
| 1008 | 105 | 168 |
| 1009 | 443 | 127 |
| 1010 | 75 | 18 |
| 1012 | 110 | null |
| 1013 | 19 | null |
| 1014 | 106 | 162 |
| 1015 | 220 | 153 |
| 1016 | 85 | 159 |
| 1017 | 82 | 56 |
| 1019 | 37 | 196 |
| 1020 | 193 | 165 |
| 1022 | 124 | null |
| 1023 | 166 | 149 |
| 1024 | 233 | null |
|------------|------------|------------|
The following query uses a workspace named dfw.views and joins a view named “custview” with a hive table named “orders” to determine sales for each membership type:
0: jdbc:drill:> select membership, sum(order_total) as sales from hive.orders, custview
where orders.cust_id=custview.cust_id
group by membership order by 2;
|------------|------------|
| membership | sales |
|------------|------------|
| "basic" | 380665 |
| "silver" | 708438 |
| "gold" | 2787682 |
|------------|------------|
3 rows selected