WHERE clause in HIVE
WHERE clause is used to FILTER out records from final result set. When you run any query in HIVE and you want to FILTER out records such that all the rows in output shall abide by some condition then use WHERE clause. You can mention conditions in WHERE which must be true for row in order to be in output result set.
Example:
select pres_name,pres_dob,pres_bs from usa_president where pres_bs=’New York’;
+————————+————-+———–+–+
| pres_name | pres_dob | pres_bs |
+————————+————-+———–+–+
| Martin Van Buren | 1782-12-05 | New York |
| Millard Fillmore | 1800-01-07 | New York |
| Theodore Roosevelt | 1858-10-27 | New York |
| Franklin D. Roosevelt | 1882-01-30 | New York |
| Donald Trump | 1946-06-14 | New York |
+————————+————-+———–+–+
You can specify multiple conditions in WHERE clause. These conditions can be on single column or on different columns by using “AND“,”OR” operators.
select pres_name,pres_dob,pres_bs from usa_president where pres_bs=’New York’ OR pres_bs=’Virginia’;
+————————-+————-+———–+–+
| pres_name | pres_dob | pres_bs |
+————————-+————-+———–+–+
| George Washington | 1732-02-22 | Virginia |
| Thomas Jefferson | 1743-04-13 | Virginia |
| James Madison | 1751-03-16 | Virginia |
| James Monroe | 1758-04-28 | Virginia |
| Martin Van Buren | 1782-12-05 | New York |
| William Henry Harrison | 1773-02-09 | Virginia |
| John Tyler | 1790-03-29 | Virginia |
| Zachary Taylor | 1784-11-24 | Virginia |
| Millard Fillmore | 1800-01-07 | New York |
| Theodore Roosevelt | 1858-10-27 | New York |
| Woodrow Wilson | 1856-12-28 | Virginia |
| Franklin D. Roosevelt | 1882-01-30 | New York |
| Donald Trump | 1946-06-14 | New York |
+————————-+————-+———–+–+
select pres_name,pres_dob,pres_bs from usa_president where pres_bs=’New York’ AND pres_bp=’Queens’;
+—————+————-+———–+–+
| pres_name | pres_dob | pres_bs |
+—————+————-+———–+–+
| Donald Trump | 1946-06-14 | New York |
+—————+————-+———–+–+
Be very careful that STRING comparison in HIVE is case sensitive, so VIRGINIA is not equal to Virginia or virginia. We will talk more about this later. For now remember that WHERE keyword is used to list conditions for FILTERing data and only records which satisfy all the condition/s will come in final output.
