PostgreSQL WHERE Clause

In PostgreSQL, when we need to query data from a single table or multiple tables based on specified conditions, we can add the WHERE clause to the SELECT statement to filter out the data we do not need.

The WHERE clause can be used not only in SELECT statements but also in UPDATE, DELETE, etc. statements.

Syntax

The following is the general syntax for using the WHERE clause in SELECT statements to read data from the database:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]

We can use comparison operators or logical operators in the WHERE clause, such as >, <, =, LIKE, NOT, etc.

The content of COMPANY.SQL file is as follows:

--　This is the file to create COMPANY table and to populate it with　7　records.
--　Just copy and paste them on psql prompt.
DROP TABLE COMPANY;
CREATE TABLE COMPANY(
　　　ID INT PRIMARY KEY                             NOT NULL,
　　　NAME                                         TEXT                     NOT NULL,
　　　AGE                                         INT                     NOT NULL,
　　　ADDRESS                                     CHAR(50),
　　　SALARY                                     REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul',　32, 'California',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen',　25, 'Texas',　15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy',　23, 'Norway',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark',　25, 'Rich-Mond ',　65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David',　27, 'Texas',　85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim',　22, 'South-Hall',　45000.00 );
INSERT INTO COMPANY VALUES (7, 'James',　24, 'Houston',　10000.00 );

Create the COMPANY table with the following data content:

w3codeboxdb# 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)

The following examples use logical operators to read data from the table.

AND

Find AGE(Age) Field greater than or equal to 25and SALARY(Salary) Field greater than or equal to 65000 data:

w3codeboxdb=#　SELECT　*　FROM COMPANY WHERE AGE >=　25　AND SALARY >=　65000;
　id | name                                  | age                                      | address                                      | salary
----+-------+-----+------------+--------
　　4　|　Mark　　|　　25　|　Rich-Mond　　|　　65000
　　5　|　David　|　　27　|　Texas　　|　　85000
(2　rows)

OR

Find AGE(Age) Field greater than or equal to 25or SALARY(Salary) Field greater than or equal to 65000 data:

w3codeboxdb=#　SELECT　*　FROM COMPANY WHERE AGE >=　25　OR SALARY >=　65000;
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)

NOT NULL

Find in the company table AGE(Age) Records with non-empty fields:

w3codeboxdb=# SELECT　*　FROM COMPANY WHERE AGE IS NOT NULL;
　　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)

LIKE

Find in the COMPANY table NAME (name) Data starting with Pa in the field:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　NAME　LIKE　'Pa%';
id　|　name　|　age　|address　　|　salary
----+------+-----+-----------+--------
　　1　|　Paul　|　　32　|　California|　　20000

IN

The following SELECT statement lists AGE(Age) Field is 　25 or 27 Data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　AGE　IN　(　25,　27　);
　id　|　name　　|　age　|　address　　|　salary
----+-------+-----+------------+--------
　　2　|　Allen　|　　25　|　Texas　　|　　15000
　　4　|　Mark　　|　　25　|　Rich-Mond　　|　　65000
　　5　|　David　|　　27　|　Texas　　|　　85000
(3　rows)

NOT IN

The following SELECT statement lists AGE(Age) Field is not 　25 or 27 Data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　AGE　NOT　IN　(　25,　27　);
　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)

BETWEEN

The following SELECT statement lists AGE(Age) Field in 　25 to 27 Data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY　WHERE　AGE　BETWEEN　25　AND　27;
　id　|　name　　|　age　|　address　　|　salary
----+-------+-----+------------+--------
　　2　|　Allen　|　　25　|　Texas　　|　　15000
　　4　|　Mark　　|　　25　|　Rich-Mond　　|　　65000
　　5　|　David　|　　27　|　Texas　　|　　85000
(3　rows)

Subquery

The following SELECT statement uses SQL subquery, and the subquery statement reads SALARY(Salary) Field greater than 65000 data, then through EXISTS Operator judgment whether it returns rows, if there are returned rows, then read all the AGE(Age) Field.

w3codeboxdb=#　SELECT　AGE　FROM　COMPANY
　　　　　　　　WHERE　EXISTS　(SELECT　AGE　FROM　COMPANY　WHERE　SALARY　>　65000);
　age
-----
　　32
　　25
　　23
　　25
　　27
　　22
　　24
(7　rows)

The following SELECT statement also uses SQL subquery, and the subquery statement reads SALARY(Salary) Field greater than 65000 of AGE(Age) Field data, then use > Operator query greater than the AGE(Age) Field data:

w3codeboxdb=#　SELECT　*　FROM　COMPANY
　　　　　　　　WHERE　AGE　>　(SELECT　AGE　FROM　COMPANY　WHERE　SALARY　>　65000);
　id　|　name　|　age　|　address　　|　salary
----+------+-----+------------+--------
　　1　|　Paul　|　　32　|　California　|　　20000