Comparison Operators

This PostgreSQL tutorial explores all of the comparison operators used to test for equality and inequality, as well as the more advanced operators.

Description

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in PostgreSQL:

Comparison Operator	Description
=	Equal
<>	Not Equal
!=	Not Equal
>	Greater Than
>=	Greater Than or Equal
<	Less Than
<=	Less Than or Equal
IN ( )	Matches a value in a list
NOT	Negates a condition
BETWEEN	Within a range (inclusive)
IS NULL	NULL value
IS NOT NULL	Non-NULL value
LIKE	Pattern matching with % and _
EXISTS	Condition is met if subquery returns at least one row

Let's explore the comparison operators that you can use in PostgreSQL.

Example - Equality Operator

In PostgreSQL, you can use the = operator to test for equality in a query.

For example:

SELECT *

FROM employees

WHERE first_name = 'Sarah';

In this example, the SELECT statement above would return all rows from the employees table where the first_name is equal to Sarah.

Example - Inequality Operator

In PostgreSQL, there are two ways to test for inequality in a query. You can use either the <> or != operator.

For example, we could test for inequality using the <> operator, as follows:

SELECT *

FROM employees

WHERE first_name <> 'Sarah';

In this example, the SELECT statement would return all rows from the employees table where the first_name is not equal to Sarah.

Or you could also write this query using the != operator, as follows:

SELECT *

FROM employees

WHERE first_name != 'Sarah';

Both of these queries would return the same results.

Example - Greater Than Operator

You can use the > operator in PostgreSQL to test for an expression greater than.

SELECT *

FROM products

WHERE product_id > 50;

In this example, the SELECT statement would return all rows from the products table where the product_id is greater than 50. A product_id equal to 50 would not be included in the result set.

Example - Greater Than or Equal Operator

In PostgreSQL, you can use the >= operator to test for an expression greater than or equal to.

SELECT *

FROM products

WHERE product_id >= 50;

In this example, the SELECT statement would return all rows from the products table where the product_id is greater than or equal to 50. In this case, product_id equal to 50 would be included in the result set.

Example - Less Than Operator

You can use the < operator in PostgreSQL to test for an expression less than.

SELECT *

FROM inventory

WHERE inventory_id < 25;

In this example, the SELECT statement would return all rows from the inventory table where the inventory_id is less than 25. An inventory_id equal to 25 would not be included in the result set.

Example - Less Than or Equal Operator

In PostgreSQL, you can use the <= operator to test for an expression less than or equal to.

SELECT *

FROM inventory

WHERE inventory_id <= 25;

In this example, the SELECT statement would return all rows from the inventory table where the inventory_id is less than or equal to 25. In this case, n inventory_id equal to 25 would be included in the result set.

Example - Advanced Operators

To learn more about the advanced comparison operators in PostgreSQL, we've written tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.

• IN ( )
• NOT
• BETWEEN
• IS NULL
• IS NOT NULL
• LIKE
• EXISTS