PostgreSQL Not Equal To Operator

PostgreSQL Not Equal To Operator

The PostgreSQL != or <> operators are used to compare whether one expression is not equal to another. These operators are essential for filtering query results based on a comparison that checks if a specified column or expression is not equal to a given value.

---

Syntax

SELECT columns
FROM table_name
WHERE column != value;

SELECT columns
FROM table_name
WHERE column <> value;

The != or <> operators have the following components:

• columns: The columns to be retrieved from the table.
• table_name: The table from which to retrieve the data.
• column: The column to be compared.
• value: The value to compare against.

---

Example PostgreSQL Not Equal To Queries

Let's look at some examples of PostgreSQL != or <> operator queries:

1. Basic Not Equal To Example

SELECT customer_id, customer_name
FROM customers
WHERE customer_id != 1;

This query retrieves the customer_id and customer_name from the customers table where the customer_id is not equal to 1.

2. Not Equal To with String Comparison

SELECT customer_id, customer_name
FROM customers
WHERE customer_name <> 'John Doe';

This query retrieves the customer_id and customer_name from the customers table where the customer_name is not equal to 'John Doe'.

3. Not Equal To with Date Comparison

SELECT order_id, order_date
FROM orders
WHERE order_date != '2024-01-01';

This query retrieves the order_id and order_date from the orders table where the order_date is not equal to '2024-01-01'.

4. Not Equal To with NULL Comparison

SELECT customer_id, customer_name
FROM customers
WHERE customer_name IS NOT NULL;

This query retrieves the customer_id and customer_name from the customers table where the customer_name is not NULL. Note that NULL values must be compared using IS NOT NULL or IS NULL.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the Not Equal To operator to filter data.

Step 1: Creating a Table

This step involves creating a new table named customers to store customer data.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    join_date DATE
);

In this example, we create a table named customers with columns for customer_id, customer_name, and join_date.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the customers table.

INSERT INTO customers (customer_name, join_date)
VALUES ('John Doe', '2024-01-01'),
       ('Jane Smith', '2024-02-15'),
       ('Jim Brown', '2024-03-10');

Here, we insert data into the customers table.

Step 3: Using the Not Equal To Operator

This step involves using the != or <> operator to filter data from the customers table.

-- Basic Not Equal To
SELECT customer_id, customer_name
FROM customers
WHERE customer_id != 1;

-- Not Equal To with String Comparison
SELECT customer_id, customer_name
FROM customers
WHERE customer_name <> 'John Doe';

-- Not Equal To with Date Comparison
SELECT order_id, order_date
FROM orders
WHERE order_date != '2024-01-01';

-- Not Equal To with NULL Comparison
SELECT customer_id, customer_name
FROM customers
WHERE customer_name IS NOT NULL;

These queries demonstrate how to use the != or <> operator to filter data from the customers table, including basic equality comparisons, string comparisons, date comparisons, and NULL comparisons.

Conclusion

The PostgreSQL != or <> operators are fundamental tools for comparing whether one expression is not equal to another. Understanding how to use the != or <> operators and their syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.