Hive – BETWEEN

BETWEEN operator in HIVE

When we have to check if column value lies between a range of value then we use BETWEEN operator in the query. You have to specify MIN and MAX value for the range when using BETWEEN operator.

Example:

select pres_name,pres_bs,pres_dob from usa_president where pres_dob between ‘1850-01-01’ and ‘1900-01-01’;

+————————+———–+————-+–+
| pres_name | pres_bs | pres_dob |
+————————+———–+————-+–+
| Theodore Roosevelt | New York | 1858-10-27 |
| William Howard Taft | Ohio | 1857-09-15 |
| Woodrow Wilson | Virginia | 1856-12-28 |
| Warren G. Harding | Ohio | 1865-11-02 |
| Calvin Coolidge | Vermont | 1872-07-04 |
| Herbert Hoover | Iowa | 1874-08-10 |
| Franklin D. Roosevelt | New York | 1882-01-30 |
| Harry S. Truman | Missouri | 1884-05-08 |
| Dwight D. Eisenhower | Texas | 1890-10-14 |
+————————+———–+————-+–+

Be very careful when specifying the range. If by mistake you specify range in MAX to MIN order, you will NOT get any records.

select pres_name,pres_bs,pres_dob from usa_president where pres_dob between ‘1900-01-01’ and ‘1850-01-01’;

+————+———-+———–+–+
| pres_name | pres_bs | pres_dob |
+————+———-+———–+–+
+————+———-+———–+–+

Also both the values specified in range are included in the BETWEEN operator. You can also specify numeric, alphanumeric, string values. BETWEEN can be replaced using “column_nm >= MIN_val and column_nmn <= MAX_val”. So the above query can be written in another manner:

select pres_name,pres_bs,pres_dob from usa_president where pres_dob >= ‘1856-12-28’ and pres_dob <= ‘1890-10-14’;

+————————+———–+————-+–+
| pres_name | pres_bs | pres_dob |
+————————+———–+————-+–+
| Theodore Roosevelt | New York | 1858-10-27 |
| William Howard Taft | Ohio | 1857-09-15 |
| Woodrow Wilson | Virginia | 1856-12-28 |
| Warren G. Harding | Ohio | 1865-11-02 |
| Calvin Coolidge | Vermont | 1872-07-04 |
| Herbert Hoover | Iowa | 1874-08-10 |
| Franklin D. Roosevelt | New York | 1882-01-30 |
| Harry S. Truman | Missouri | 1884-05-08 |
| Dwight D. Eisenhower | Texas | 1890-10-14 |
+————————+———–+————-+–+

Leave a Reply

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