Advanced Filtering: IN, OR, and NOT

We previously covered basic filtering using SQL and some boolean operators. In this medium post, we will be covering IN, OR and NOT as a means to filter our table.

In this post, we will learn the following:

• How to use IN and OR operators to filter data.
• How to differentiate between IN and BETWEEN.
• Discuss the hierarchal importance of operations.
• How to use the NOT operator.

IN Operator.

To use the IN operator we need to specify a range of conditions, enclosed in brackets and separated by commas.

Confused? Take a look below:

SELECT
product_id,
unit_price,
supplier_id
FROM products
WHERE supplier_id IN (9, 10, 11);

This will return the selected columns where supplier_id is either 9, 10 or 11.

OR operator.

The OR operator goes through the condition in order and returns results when the first condition is met. This is important to consider when writing your query.

SELECT 
product_name,
unit_price,
supplier_id,
product_name
FROM products
WHERE product_name = 'Tofu' OR 'Konbu';

This will return results when it finds ‘Tofu’. This then means that the second condition will not be evaluated since the first condition has already been met.

IN vs. OR.

IN works in the same way as OR. However, IN has several benefits.

• Allows users to define a long list of options.
• IN executes faster than OR.
• Don’t have to think about order.
• Can contain another SELECT.

OR with AND.

We can limit how SQL likes to execute the first condition in the OR filter by using parentheses. This way we can filter using OR and AND as shown below:

SELECT
product_id, 
unit_price,
supplier_id,
FROM products
WHERE (supplier_id = 9 OR supploer_id = 11)
AND unit_price > 15

This will actually produce a set of results that returns the columns selected where supplier_id is 9 AND unit_price is greater than 15. As well as return columns where supplier_id is 11 AND unit_price is greater than 15.

If we do it without parenthesis like below:

SELECT
product_id, 
unit_price,
supplier_id,
FROM products
WHERE supplier_id = 9 OR supploer_id = 11
AND unit_price > 15

This will return the columns selected and all the results where supplier_id is equal to 9 and equal to 11. And then look for results where supplier_id is equal to 9 AND unit_price is greater than 15 and supplier_id is equal to 11 and greater than 15.

Thanks for bearing with me on those paragraphs, take a look at the image:

It would be advisable to use parenthesis so that you get exactly what you need.

NOT operator.

Say for example we want to evaluate all results from the ‘employees’ table besides those employees that reside in London and Seattle. Here is what we would do:

SELECT *
FROM employees
WHERE NOT city='London' AND NOT city='Seattle';

This would return every column besides columns where the city is equal to London or Seattle.

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.