5.17. PostgreSQL WHERE clause

In PostgreSQL, when we need to query data from a single table or multiple tables according to specified conditions, we can add a WHERE clause to the SELECT statement to filter out the data we don’t need.

WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。

5.17.1. Grammar ¶

The following is the general syntax for reading data from a database using the WHERE clause in a SELECT statement:

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, and so on.

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

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

In the following examples, we use logical operators to read the data in the table.

5.17.2. AND ¶

Find out AGE(年龄) Field is greater than or equal to 25, and SALARY(薪资) Data with fields greater than or equal to 65000:

runoobdb=# 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)

5.17.3. OR ¶

Find out AGE(年龄) Field is greater than or equal to 25, or SALARY(薪资) Data with fields greater than or equal to 65000:

runoobdb=# 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)

5.17.4. NOT NULL ¶

Find out in the company table AGE(年龄) Records whose fields are not empty:

runoobdb=#  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)

5.17.5. LIKE ¶

Find out in the COMPANY table NAME(名字) Data that begins with Pa in the field:

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

5.17.6. IN ¶

The following SELECT statement lists AGE(年龄) Data with field 25 or 27:

runoobdb=# 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)

5.17.7. NOT IN ¶

The following SELECT statement lists AGE(年龄) Data whose field is not 25 or 27:

runoobdb=# 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)

5.17.8. BETWEEN ¶

The following SELECT statement lists AGE(年龄) Data with fields from 25 to 27:

runoobdb=# 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)

5.17.9. Subquery ¶

The following SELECT statement uses a subquery of SQL, which is read in the subquery statement SALARY(薪资) Data whose field is greater than 65000, and then pass the EXISTS Operator determines whether it returns rows, and if any, reads all AGE(年龄) Field.

runoobdb=# 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 the subquery of SQL, which is read in the subquery statement SALARY(薪资) Fields greater than 65000 AGE(年龄) Field data, and then use the > The operator query is greater than the AGE(年龄) Field data:

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