PostgreSQL

In PostgreSQL, under the Data Query Language (DQL), the != or <> operators are used to compare two values to check for inequality. Both operators serve the same purpose and are functionally identical. They are often used in SELECT queries to filter out rows where a specific condition does not hold. These operators return TRUE when the values being compared are not equal.

Below is an example of how you might use the <> or != operators in a simple banking context to filter customer data.

Example SQL usage

  SELECT *
  FROM customers
  WHERE account_status != 'active';

This query returns all customers whose account status is not active.

Steps for Using the != or <> Operators

1. Basic Inequality Comparison:
   • Use either != or <> to filter data based on inequality.
   • Both operators function the same way and can be used interchangeably.

     SELECT *
     FROM accounts
     WHERE account_type <> 'savings';

   This query fetches all rows from the accounts table where the account type is not 'savings'.

2. Working with Numeric Fields:
   • Inequality can be applied to numeric fields to find records where certain amounts, balances, or counts are not equal to a specified number.

     SELECT *
     FROM transactions
     WHERE transaction_amount != 1000;

   This query retrieves all transactions that are not equal to 1000 units (for example, dollars).

3. Combining with Other Conditions:
   • The != or <> operators can be used in combination with other conditions like AND or OR.

     SELECT *
     FROM customers
     WHERE customer_status != 'inactive'
     AND account_balance <> 0;

   This query selects all customers who are not inactive and whose account balance is not zero.

4. Using in Joins or Subqueries:
   • You can use these operators in more complex queries like joins or subqueries to compare columns from different tables.

     SELECT c.customer_id, c.name, a.account_balance
     FROM customers c
     JOIN accounts a ON c.customer_id = a.customer_id
     WHERE a.account_balance <> 500;

   This retrieves the customer ID, name, and account balance of customers whose balance is not equal to 500.

5. Handling NULL Values:
   • Remember that PostgreSQL treats NULL as an unknown value. Comparing NULL using != or <> won't return rows where the field is NULL. Use IS NOT NULL for such cases.

     SELECT *
     FROM accounts
     WHERE last_transaction_date IS NOT NULL
     AND account_type != 'checking';

   This query gets all accounts that have had a transaction and are not checking accounts.

   These are the basics of using the != or <> operator in PostgreSQL to query inequality conditions in your database.

To gain complete access, login with gmail or outlook, no need of signup. click here

In PostgreSQL SQL, you can achieve the same result using the <> operator for "not equal" comparisons:

SELECT *
FROM org_client
WHERE gender <> 'F';

Try it now

In PostgreSQL SQL, you would also use the <> operator to filter orders with statuses other than 5:

SELECT *
FROM act_order
WHERE order_status_id <> 5;

Try it now

In PostgreSQL SQL, you would use the <> operator to filter orders with shipping dates not equal to December 12th:

SELECT *
FROM act_order
WHERE shipped_date <> '2023-12-12'::DATE;

Try it now

Example 1:

Let's examine the process of extracting information from a specified table using the SQL '!=' or '<>' (not equal) operator with string data. Retrieve records for clients labeled as male while excluding those identified as female.

Example 1 - Raw data from client table

Example 1 - Query

SELECT *
FROM org_client
WHERE gender <> 'F';

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Let's explore the procedure of extracting information from a designated table using the SQL '!=' or '<>' (not equal) operator with numeric data. Fetch orders with statuses different from 5.

Example 2 - Raw data from orders table

Example 2 - Query

SELECT *
FROM act_order
WHERE order_status_id <> 5;

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output

Example 3:

Let's delve into the process of extracting information from a specified table using the SQL '!=' or '<>' (not equal) operator with date data. Retrieve orders with shipping dates other than December 12th.

Example 3 - Raw data from orders table

Example 3 - Query

SELECT *
FROM act_order
WHERE shipped_date <> '2023-12-12'::DATE;

Example 3 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. Please be aware that the query will attempt to filter based on the value of December 12th. Since many records have NULL for the shipped date value, NULL cannot be compared as it is not an actual value. Consequently, these records will be excluded from the results as well.

Example 3 - Query Output