Skip to content

Use IN rather than NOT IN

The SQL IN and NOT IN command allows you to specify multiple values in the WHERE clause. You can imagine it as a series of EQUAL TO and NOT EQUAL TO commands that are separated by the OR condition.

The NOT IN command compares specific column values from the first table with another column values in the second table or a subquery and returns all values from the first table that are not found in the second table, without performing any filter for the distinct values. The NULL is considered and returned by the NOT IN command as a value.

For some cases you can get same output with IN and NOT IN operators. So its good to use IN rather than NOT IN for places like that.

Example:

TRAINING_EVENT_TAB table has five states as below. and those and mandatory columns.

  • 'Published', 'Confirmed', 'Completed', 'Canceled', 'Planned'

SQL using IN

SELECT training_event_id,
       training_event_name,
       training_duration
FROM   TRAINING_EVENT_TAB
WHERE  rowstate IN ('Published', 'Confirmed');

SQL using NOT IN

SELECT training_event_id,
       training_event_name,
       training_duration
FROM   TRAINING_EVENT_TAB
WHERE  rowstate NOT IN ( 'Completed', 'Canceled', 'Planned');

So, output of these two SQL statement are same. both are return records that are with rowstate EQUALS TO 'Published' and 'Confirmed'.