PostgreSQL | PostgreSQL Basic Syntax | Using Comparison Operators (<, >, =, BETWEEN, IS NULL)
Comparison operators compare two values and determine whether they are greater than, less than, equal to, or different from each other. This page explains how to use comparison operators.
Comparing Greater Than, Less Than, and Equal To
Comparison operators compare the left and right sides and return a boolean value as the result.
The four comparison operators used to compare size are <, <=, >, and >=.
< left side is less than right side
> left side is greater than right side
<= left side is less than or equal to right side
>= left side is greater than or equal to right side
The three comparison operators used to compare equality are =, <>, and !=.
= left side and right side are equal
<> left side and right side are not equal
!= left side and right side are not equal
–
Try it in practice by creating the following test table.
mydb=# create table customer(name varchar(10), old integer);
CREATE TABLE
mydb=#
Add the following data to the table.
mydb=# insert into customer values ( 'kimkc', 41), ( 'devkuma', 20), ( 'yunho', 32), ( 'yongtea', 34), ( 'jiwoo', 12);
INSERT 0 5
mydb=#
Then use a SELECT command to check whether the value of the old column is greater than, less than, or equal to 20.
mydb=# select name, old,
mydb-# old <20 as "less than 20",
mydb-# old > 20 as "greater than 20",
mydb-# old = 20 as "equal to 20"
mydb-# from customer;
name | old | less than 20 | greater than 20 | equal to 20
---------+-----+--------------+-----------------+------------
kimkc | 41 | f | t | f
devkuma | 20 | f | f | t
yunho | 32 | f | t | f
yongtea | 34 | f | t | f
jiwoo | 12 | t | f | f
(5 rows)
The results of evaluating values stored in a column with comparison operators were retrieved.
Comparing Whether a Value Is Within a Range (BETWEEN)
The BETWEEN condition compares whether the target value is within a specified range and returns a boolean value as the result.
a BETWEEN x AND y a is in the range from x or greater to y or less
a NOT BETWEEN x AND y a is not in the range from x or greater to y or less
If the target value a is greater than or equal to x and less than or equal to y, the result is TRUE. With NOT, the result is TRUE when a is not greater than or equal to x and less than or equal to y.
The BETWEEN condition can be written using comparison operators and logical operators as follows. The following two expressions have the same meaning.
a >= x AND a <= y
a BETWEEN x AND y
The NOT BETWEEN condition can also be written using comparison operators and logical operators as follows. The following two expressions have the same meaning.
a < x OR a > y
a NOT BETWEEN x AND y
When a BETWEEN x AND y is specified and x is greater than y, there are no values greater than or equal to x and less than or equal to y, so the result is always FALSE. If SYMMETRIC is specified, PostgreSQL sorts the values of x and y and determines whether the value is within the range from the smaller value to the larger value.
a BETWEEN SYMMETRIC x AND y
a NOT BETWEEN SYMMETRIC x AND y
If the target value a is within the range from the smaller of x and y to the larger, the result is TRUE. With NOT, the result is the opposite.
–
Try it in practice. For the customer table created earlier, check whether the value of the old column is within the range from 15 to 33.
mydb=# select name, old,
mydb-# old between 15 and 33 as "15 through 33"
mydb-# from customer;
name | old | 15 through 33
---------+-----+---------------
kimkc | 41 | f
devkuma | 20 | t
yunho | 32 | t
yongtea | 34 | f
jiwoo | 12 | f
(5 rows)
Values within the specified range became TRUE, and values outside the range became FALSE.
Next, change the numbers that indicate the range of the BETWEEN condition.
mydb=# select name, old,
mydb-# old between 33 and 15 as "33 through 15"
mydb-# from customer;
name | old | 33 through 15
---------+-----+---------------
kimkc | 41 | f
devkuma | 20 | f
yunho | 32 | f
yongtea | 34 | f
jiwoo | 12 | f
(5 rows)
Because there is no range that is greater than or equal to 33 and less than or equal to 15, all results became FALSE.
If SYMMETRIC is specified, the two values used to specify the range are sorted, and the range is interpreted correctly.
mydb=# select name, old,
mydb-# old between symmetric 33 and 15 as "15 through 33"
mydb-# from customer;
name | old | 15 through 33
---------+-----+---------------
kimkc | 41 | f
devkuma | 20 | t
yunho | 32 | t
yongtea | 34 | f
jiwoo | 12 | f
(5 rows)
If the value is within the range from the smaller value to the larger value, the result is TRUE; otherwise it is FALSE.
Comparing Whether a Value Is NULL (IS NULL, IS NOT NULL)
IS NULL compares whether the target value is NULL and returns a boolean value as the result.
expression IS NULL expression is NULL
expression IS NOT NULL expression is not NULL
If the target value is NULL, the result is TRUE. With NOT, the result is TRUE when the value is not NULL. If you use the comparison operator = to check whether a value is NULL, you will not get the correct result.
The following non-standard syntax is also available with the same result.
expression ISNULL expression is NULL
expression NOTNULL expression is not NULL
–
Try it in practice by creating the following test table.
mydb=# create table friends(name varchar(10), old integer);
CREATE TABLE
mydb=#
Add the following data to the table.
mydb=# insert into friends values ('kimkc', 39), ('devkuma', NULL), (NULL, 19);
INSERT 0 3
mydb=# \pset null 'NULL'
Null display is "NULL".
mydb=# select * from friends;
name | old
---------+------
kimkc | 39
devkuma | NULL
NULL | 19
(3 rows)
By default, when NULL is retrieved and displayed, nothing is shown, so the display is configured to show NULL when the value is NULL.
Use a SELECT command to determine whether the values stored in the name and old columns are NULL.
mydb=# select name, name is null as "NULL?", old, old is null as "NULL?" from friends;
name | NULL? | old | NULL?
---------+-------+------+-------
kimkc | f | 39 | f
devkuma | f | NULL | t
NULL | t | 19 | f
(3 rows)
If the value stored in the column is NULL, the result is TRUE; otherwise it is FALSE.
–
This page explained how to use the comparison operators provided by PostgreSQL.