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 *