Hive – BETWEEN

By RajApache HIVE0 comments

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 in between clause. If by mistake you specify range in MAX to MIN order, you will NOT get any records. It must always be from MIN to MAX.

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