In the previous post we learned about PostgreSQL where clause introduction, In this post, we learn more about restricting the rows using different types of using where clause in postgreSQL with examples.
when retrieving data from the database, you may need to do the following.
1. restrict the rows of data that are displayed.
2. specify the order in which the rows are displayed
Following are the different ways where we use where clause in PostgreSQL.
Table of Contents
comparison operators in PostgreSQL where clause
comparison operators are used in conditions that compare one expression to another value. They are used in the where clause in the following format.
Following are the comparison operators
Operator Meaning
———— —————–
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
syntax:
where expression operator value
e.g., where sal > 4000
Example.,
testdb=> select * from emp where sal > 4000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----------+------+---------------------------+---------+-------+-------- 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 (1 row) testdb=>
The BETWEEN Operator
You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower
range and an upper range.
Values specified with the BETWEEN operator are inclusive. You must specify the lower limit first.
If you want to display all the employees whose salary is between 1200 and 2000, use the below query.
select * from emp where sal between 1200 and 2000;
testdb=> select * from emp where sal between 1200 and 2000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+---------------------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10 (5 rows) testdb=>
IN operator in SQL where clause:
The IN clause is used to display the records that match the values that are set in IN condition.
syntax
where expression IN (‘value1′,’value2’..);
Example.,
testdb=> select * from emp where job in ('ANALYST','CLERK');
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-----------+---------+------+----------------------------------+---------+--------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | 20
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00+05:30 | 950.00 | 11.00 | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00+05:30 | 1300.00 | 16.00 | 10
7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20
(7 rows)
PostgreSQL where date is today:
testdb=> testdb=> select * from emp where hiredate > now()-interval '1 day'; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-----------+-------+------+----------------------------------+--------+------+-------- 7777 | ADAMSMITH | CLERK | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | 20 (1 row) testdb=>
PostgreSQL where not equal:
testdb=> select * from emp where Job <> 'CLERK'; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+---------------------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00+05:30 | 2850.00 | 20.00 | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00+05:30 | 2450.00 | 10.00 | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00+05:30 | 3000.00 | 67.00 | 20 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00+05:30 | 3000.00 | 1.00 | 20 (10 rows) testdb=>
PostgreSQL where multiple conditions
testdb=> select * from emp where job='SALESMAN'; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+---------------------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | 30 (4 rows) testdb=> select * from emp where job='SALESMAN' and comm > 1000; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+----------+------+---------------------------+---------+---------+-------- 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | 30 (1 row) testdb=>