Hive – IN

IN operator is used to compare any column value in the table with multiple possible values. It returns TRUE if value is matching with any value listed inside IN operator.

Example:

select pres_name,pres_dob,pres_bs from usa_president where pres_bs IN (‘Virginia’,’New York’,’New Jersey’);

+————————-+————-+————-+–+
| 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 |
| Grover Cleveland | 1837-03-18 | New Jersey |
| Grover Cleveland | 1837-03-18 | New Jersey |
| 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 |
+————————-+————-+————-+–+

We can also specify SELECT statement inside IN clause. In such case we call it as “SUBQUERY” which we will discuss in other post. For now, remember when you have to COMPARE column value with multiple possible values then we use “IN” operator. If there is NO MATCH then no output rows are returned. We can use IN inside WHERE clause or in SELECT statement with CASE statement.

Leave a Reply

Your email address will not be published. Required fields are marked *