In the previous post, we learned about how we restrict the results using where clause, now we will learn more about sql where clause with logical operators in SQL. The three important logical operators are PostgreSQL and, or and not.
Also read, PostgreSQL where clause
Table of Contents
Logical Operators: PostgreSQL
A logical condition combines the two-component conditions to produce a single result based on those conditions or it inverts the result of a single condition.
A row is returned only if the overall result of the condition is true.
There are 3 logical operators present in SQL
1. AND
2. OR
3. NOT
Examples for each of the above Logical Operators is listed below.
PostgreSQL AND Operator
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
testdb=> select * from emp where ename like 'S%' ; 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 (2 rows) testdb=> select * from emp where ename like 'S%' and job='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 (1 row) testdb=> |
Here we have selected ename starting with ‘S’ whose job is ‘CLERK’
PostgreSQL OR operator
OR is an operator that filters the result set to only include rows where either condition is true.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
testdb=> testdb=> select * from emp where ename like 'S%' or job='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 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 (6 rows) testdb=> |
PostgreSQL NOT operator
|
1 2 3 4 5 6 7 8 9 10 |
testdb=> select * from emp where job not in ('ANALYST','CLERK','SALESMAN'); empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----------+------+---------------------------+---------+--------+-------- 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00+05:30 | 2975.00 | 300.00 | 20 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 7839 | KING | PRESIDENT | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | 10 (4 rows) testdb=> |
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
