Your cart is currently empty!
Category: postgresql
-
Khóa học miễn phí PostgreSQL – Where Clause nhận dự án làm có lương
PostgreSQL – WHERE Clause
The PostgreSQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.
If the given condition is satisfied, only then it returns specific value from the table. You can filter out rows that you do not want included in the result-set by using the WHERE clause.
The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.
Syntax
The basic syntax of SELECT statement with WHERE clause is as follows −
SELECT column1, column2, columnN FROM table_name WHERE [search_condition]
You can specify a search_condition using like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.
Example
Consider the table 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 simple examples showing usage of PostgreSQL Logical Operators. Following SELECT statement will list down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00 −
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
The above given PostgreSQL statement will produce the following result −
id | name | age | address | salary ----+-------+-----+------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows)
The following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00 −
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
The above given PostgreSQL statement will produce the following result −
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 following SELECT statement lists down all the records where AGE is not NULL which means all the records, because none of the record has AGE equal to NULL −
testdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
The above given PostgreSQL statement will produce the following result −
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 SELECT statement lists down all the records where NAME starts with ”Pa”, does not matter what comes after ”Pa”.
testdb=# SELECT * FROM COMPANY WHERE NAME LIKE ''Pa%
The above given PostgreSQL statement will produce the following result −
id | name | age |address | salary ----+------+-----+-----------+-------- 1 | Paul | 32 | California| 20000
The following SELECT statement lists down all the records where AGE value is either 25 or 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
The above given PostgreSQL statement will produce the following result −
id | name | age | address | salary ----+-------+-----+------------+-------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
The following SELECT statement lists down all the records where AGE value is neither 25 nor 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
The above given PostgreSQL statement will produce the following result −
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 following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27 −
testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
The above given PostgreSQL statement will produce the following result −
id | name | age | address | salary ----+-------+-----+------------+-------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query −
testdb=# SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);The above given PostgreSQL statement will produce the following result −
age ----- 32 25 23 25 27 22 24 (7 rows)
The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query −
testdb=# SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);The above given PostgreSQL statement will produce the following result −
id | name | age | address | salary ----+------+-----+------------+-------- 1 | Paul | 32 | California | 20000
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí PostgreSQL – Like Clause nhận dự án làm có lương
PostgreSQL – LIKE Clause
The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1.
There are two wildcards used in conjunction with the LIKE operator −
- The percent sign (%)
- The underscore (_)
The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.
If either of these two signs is not used in conjunction with the LIKE clause, then the LIKE acts like the equals operator.
Syntax
The basic syntax of % and _ is as follows −
SELECT FROM table_name WHERE column LIKE ''XXXX%'' or SELECT FROM table_name WHERE column LIKE ''%XXXX%'' or SELECT FROM table_name WHERE column LIKE ''XXXX_'' or SELECT FROM table_name WHERE column LIKE ''_XXXX'' or SELECT FROM table_name WHERE column LIKE ''_XXXX_''
You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value.
Example
Here are number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators −
| S. No. | Statement & Description |
|---|---|
| 1 |
WHERE SALARY::text LIKE ”200%” Finds any values that start with 200 |
| 2 |
WHERE SALARY::text LIKE ”%200%” Finds any values that have 200 in any position |
| 3 |
WHERE SALARY::text LIKE ”_00%” Finds any values that have 00 in the second and third positions |
| 4 |
WHERE SALARY::text LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length |
| 5 |
WHERE SALARY::text LIKE ”%2” Finds any values that end with 2 |
| 6 |
WHERE SALARY::text LIKE ”_2%3” Finds any values that have 2 in the second position and end with a 3 |
| 7 |
WHERE SALARY::text LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3 |
Postgres LIKE is String compare only. Hence, we need to explicitly cast the integer column to string as in the examples above.
Let us take a real example, consider the table , having records as follows −
# 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 is an example, which would display all the records from COMPANY table where AGE starts with 2 −
testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE ''2%
This would produce the following result −
id | name | age | address | salary ----+-------+-----+-------------+-------- 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 8 | Paul | 24 | Houston | 20000 (7 rows)
The following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −
testdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE ''%-%
This would produce the following result −
id | name | age | address | salary ----+------+-----+-------------------------------------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 (2 rows)
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
