Sql WHERE condition is not equal to?

sql

best-practices

performance

join

byNikita Barsukov·Oct 2, 2024

In SQL, non-equality is expressed with <> or != inside the WHERE clause. Here's an example:

SELECT * FROM your_table WHERE your_column <> 'desired_value';

This query fetches rows where your_column does not equal 'desired_value'.

Tackling the null beast

NULL in SQL is a peculiar beast: it isn't equal to anything, even to another NULL. So, when NULL lurks among your data, <> or != won't behave like you'd expect. You need to corner NULL with an extra condition:

SELECT * FROM your_table WHERE your_column <> 'desired_value' OR your_column IS NULL;

This manoeuvre allows you to catch rows where your_column is neither 'desired_value' nor NULL.

The pitfalls of NOT IN

Sometimes, you might reach for NOT IN as an alternative, especially when you're up against multiple values. But watch your step! A NULL hiding in a subquery can booby-trap your results:

SELECT * FROM your_table WHERE your_column NOT IN (SELECT possible_value FROM other_table);

If other_table.possible_value includes a NULL, it ambushes your query, leaving you with no results. For more stealthy operations, opt for NOT EXISTS instead.

ANSI vs. others: compatibility showdown

In the boxing ring, we have <>, the official ANSI SQL standard contender for non-equality, and !=, the popular choice across various SQL databases. To land your punches, always verify for the specific SQL platform:

-- Mr. ANSI SQL standard
SELECT * FROM your_table WHERE your_column <> 'value';  -- "You can't touch this!"
SELECT * FROM your_table WHERE your_column != 'value';  -- "I won't back down!"

It's always a good strategy to keep your SQL punches cross-compatible. Avoid black eyes when switching among MySQL, PostgreSQL, or SQL Server.

DELETE with concern, or suffer in silence

A DELETE statement flexes much harder with a non-equality condition. Innocent data rows can easily get caught in the crossfire:

-- Deletes all rows without a second thought
DELETE FROM your_table WHERE your_column <> 'value_to_keep';  -- "So long, and thanks for all the fish!"

Be mindful of the impact on your data. Always shield your data with a backup before you let loose with such operations.

Keep an eye on performance

Don't let the fun distract you - negation in WHERE clauses can pack quite a punch on performance, especially in large databases. Make sure you benchmark and optimize your queries. If your trusty sidekick columns aren't already indexed, give them the superpower they deserve for a quick rescue.