PostgreSQL: Comparison Operators

In this PostgreSQL post 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:

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 = 'Julie';

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

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 > 'Julie';

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

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

SELECT *
FROM employees
WHERE first_name != 'Julie';

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