HIVE SELECT WHERE Statement
The WHERE clause in SELECT statement is used to fetch data from a HIVE table based on a particular condition. Also AND, OR, NOT operator can be combined with WHERE clause to impose multiple conditions.
Syntax:
SELECT column1,column2,...,columnN from <tablename> WHERE <condition>
column1,column2 are the columns of the table.
Example:
SELECT * from Employee WHERE city = 'Delhi';
emp_id |
name |
city |
salary |
103 |
Harry |
Delhi |
80000 |
105 |
Sakil |
Delhi |
90000 |
Selects the only row where the city is equal to 'Delhi' .
Using multiple conditions with AND
Syntax:
SELECT column1,column2,...,columnN from <tablename> WHERE <condition1> AND <condition2>
Example:
SELECT * from Employee WHERE city = 'Delhi' AND name = 'Sakil';
emp_id |
name |
city |
salary |
105 |
Sakil |
Delhi |
90000 |
Using multiple conditions with OR
Syntax:
SELECT column1,column2,...,columnN from <tablename> WHERE <condition1> OR <condition2>
Example:
SELECT * from Employee WHERE city = 'Delhi' OR name = 'Sakil';
emp_id |
name |
city |
salary |
103 |
Harry |
Delhi |
80000 |
105 |
Sakil |
Delhi |
90000 |
Using multiple conditions with AND and OR
Syntax:
SELECT column1,column2,...,columnN from <tablename> WHERE <condition1> AND <condition2> OR<condition3>
Example:
SELECT * from Employee WHERE city = 'Delhi' AND (salary = '80000' OR salary = '90000');
emp_id |
name |
city |
salary |
103 |
Harry |
Delhi |
80000 |
105 |
Sakil |
Delhi |
90000 |