Using where clause in Apache Hive query

In this article, We will learn where  clause in Apache Hive.
Where clause is used to filter the column data that satisfies given condition. We will learn how to use where clause in different ways in the following steps.

1) Create table called employee in Apache Hive using this article.

2)  Check the data in Hive table employee.

Select * from employee;

3) Where clause in Hive  supports many operators.

The query below uses equal operator and filters the data if deptno column matches  to 10.

 select * from employee where deptno=10;

4) Where clause in Hive also allows funtions.

The query below selects data from employee table if year of joining is 2014. year is the hive function that is performed on doj column.

 select * from employee where year(doj)=2014;

5) Where clause also allows like operator to filter the data using a pattern.

The query below selects the data from employee table if name starts with 'B' using like operator.

select * from employee where name like 'B%'

6) Using where clause with in operator.

We define set of values using in operator. IN operator allows us to compare  a column data against set of values.

The query below selects the data from employee table if deptno equals to 10 or 20.

select * from employee where deptno in (10,20);

7) Subquery in Where  clause is also allowed.

Please check here to learn more about subqueries in where clause.

We have learned how to use where clause :

1) Plain where clause

2) Using fuction in where clause

3) Using like operator in where clause to search column data with a pattern.

4) Using like operator with in operator

5) Using subquery in where clause