The PostgreSQL WHERE proviso is utilized to indicate a condition while bringing the information from single table or getting together with various tables.
On the off chance that the given condition is fulfilled, really at that time it returns explicit incentive from the table. You can sift through lines that you don't need remembered for the outcome set by utilizing the WHERE provision.
The WHERE proviso not exclusively is utilized in SELECT proclamation, yet it is additionally utilized in UPDATE, DELETE articulation, and so forth, which we would analyze in resulting sections.
Syntax
The essential sentence structure of SELECT explanation with WHERE provision is as per the following −
SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]
You can indicate a search_condition utilizing correlation or sensible administrators. like >, <, =, LIKE, NOT, and so on The accompanying models would make this idea understood.
Example
Consider the table COMPANY having records as follows −
testdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
Here are basic models demonstrating use of PostgreSQL Logical Operators. Following SELECT explanation will list down all the records where AGE is more noteworthy than or equivalent to 25 AND pay is more prominent than or equivalent to 65000.00 −
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
The above given PostgreSQL articulation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)
The accompanying SELECT assertion records down all the records where AGE is more noteworthy than or equivalent to 25 OR compensation is more prominent than or equivalent to 65000.00 −
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
The above given PostgreSQL articulation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+-------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(4 rows)
The accompanying SELECT assertion records down all the records where AGE isn't NULL which implies all the records, since none of the record has AGE equivalent to NULL −
testdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
The above given PostgreSQL articulation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(7 rows)
The accompanying SELECT assertion records down all the records where NAME begins with 'Dad', doesn't make a difference what comes after 'Dad'.
testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
The above given PostgreSQL proclamation will create the accompanying outcome −
id | name | age |address | salary
----+------+-----+-----------+--------
1 | Paul | 32 | California| 20000
The accompanying SELECT assertion records down all the records where AGE esteem is either 25 or 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
The above given PostgreSQL proclamation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(3 rows)
The accompanying SELECT assertion records down all the records where AGE esteem is neither 25 nor 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
The above given PostgreSQL explanation will deliver the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(4 rows)
The accompanying SELECT assertion records down all the records where AGE esteem is in BETWEEN 25 AND 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
The above given PostgreSQL articulation will create the accompanying outcome −
id | name | age | address | salary
----+-------+-----+------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(3 rows)
The accompanying SELECT assertion utilizes SQL subquery where subquery discovers all the records with AGE field having SALARY > 65000 and later WHERE statement is being utilized alongside EXISTS administrator to list down all the records where AGE from the external question exists in the outcome returned by sub-inquiry −
testdb=# SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
The above given PostgreSQL explanation will create the accompanying outcome −
age
-----
32
25
23
25
27
22
24
(7 rows)
The accompanying SELECT assertion utilizes SQL subquery where subquery discovers all the records with AGE field having SALARY > 65000 and later WHERE condition is being utilized alongside > administrator to list down all the records where AGE from outside question is more noteworthy than the age in the outcome returned by sub-inquiry −
testdb=# SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
The above given PostgreSQL explanation will create the accompanying outcome −
id | name | age | address | salary
----+------+-----+------------+--------
1 | Paul | 32 | California | 20000