PostgreSQL: How to Use WHERE NOT EQUAL

Often in PostgreSQL you may want to select rows where a column is not equal to a specific value.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE team != 'Mavs';

This particular example will return all rows in the athletes table where the value in the team column is not equal to Mavs.

Note: We use the syntax != to represent “not equal” in PostgreSQL.

The following example shows how to use this syntax in practice.

Example: How to Use WHERE NOT EQUAL in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER,
  assists INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Cavs', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Mavs    |     31 |      14
         6 | Mavs    |     16 |       3
         7 | Celtics |     10 |       5
         8 | Celtics |     14 |      13
         9 | Cavs    |     20 |       8
        10 | Hawks   |     26 |       3

Suppose that we would like to only return the rows where the value in the team column is not equal to Mavs.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE team != 'Mavs';

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         7 | Celtics |     10 |       5
         8 | Celtics |     14 |      13
         9 | Cavs    |     20 |       8
        10 | Hawks   |     26 |       3

Notice that the resulting table only contains the rows where the team field is not equal to Mavs.

Feel free to replace ‘Mavs’ with any other value that you would like to search for in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Check if Array is Empty
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array Field Contains Value