Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL NOT EQUAL (<>)

The `<>` operator in PostgreSQL is used to compare two expressions for inequality. It checks if the values on either side are not equal and is often used in `WHERE` clauses to filter results.

Usage

The `<>` operator is used when you need to filter data based on the inequality of two expressions. It is commonly employed in `SELECT`, `UPDATE`, `DELETE`, and `INSERT` statements with `RETURNING` clauses to exclude rows where the specified condition is true.

sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

In this syntax, `column_name <> value` specifies that only rows where `column_name` does not equal `value` will be considered.

Examples

1. Basic Inequality Check

sql
SELECT *
FROM products
WHERE price <> 100;

This query retrieves all rows from the `products` table where the `price` is not equal to 100.

2. Using NOT EQUAL with Strings

sql
SELECT customer_id, name
FROM customers
WHERE status <> 'inactive';

Here, the query selects customers whose `status` is not 'inactive', demonstrating the use of `<>` with string values.

3. Combining NOT EQUAL with Logical Operators

sql
SELECT order_id, order_date
FROM orders
WHERE order_status <> 'shipped' AND total_amount <> 0;

This example retrieves orders that are neither shipped nor have a total amount of zero, illustrating the combination of `<>` with other logical conditions.

4. NOT EQUAL with Dates

sql
SELECT event_id, event_name
FROM events
WHERE event_date <> '2023-10-31';

This query fetches events that are not scheduled on October 31, 2023, showcasing the versatility of `<>` with date values.

Tips and Best Practices

  • Use with caution on large datasets. The `<>` operator can be less efficient on large datasets, potentially causing full table scans. Consider indexing the column to improve performance.
  • Combine with other conditions. Use `<>` alongside other logical operators (`AND`, `OR`) for more precise filtering.
  • Clarify NULL handling. When either operand is NULL, the result of the comparison is unknown, and the row is not included in the result set. Use `IS DISTINCT FROM` to handle comparisons involving NULL values.
  • Opt for explicit checks when possible. While `<>` is versatile, sometimes explicit equality checks (using `=`) can be more intuitive and faster for certain queries.
  • Note on standard equivalence. The `<>` operator is equivalent to `!=` in PostgreSQL, which may be familiar to users from other SQL dialects.