Filtering Data with SQL

Photo by Devin Avery on Unsplash

We can use several operators and clauses for filtering data with SQL including:

• WHERE
• BETWEEN
• IN
• OR
• NOT
• LIKE

Basic Filtering

When we need to be specific about the data we want to retrieve and reduce the number of records we retrieve to increase query performance and reduce the strain on the client application to governance limitations then we have to use the filter option to be better perform these operations.

let's assume we have a table called accounts:

for filtering, we often use the WHERE clause to perform our query:

Syntax

SELECT column_name_1, column_name_2
FROM table_name
WHERE column_name_1 = value;

WHERE Clause

there are several operators to use in the WHERE clause. see below table:

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
    WHERE name = 'Walmart';

another example

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE id = 1011

Non-matches data

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name <> 'Alice'

Filtering with a Range of Data

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE id BETWEEN 1001 AND 1021

Filtering with no value

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name IS NULL

We have to keep in mind that the order of sequence is very important in SQL statements. First SELECT then FROM then WHERE taken place.

Advanced Filtering with SQL

Now we have known how to filter data in SQL to meet our goal or find our exact records of data. but sometimes we need more complex filtering to get our desire records from data sources.

in this section we use:

• IN
• OR
• AND
• NOT

The IN operator

First of all, let's talk about the IN operator. this is used to filter a range of records.

Example

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name IN ('Walmart', 'Apple');

The OR operator

Now we're going to show you the OR operator. that allows you to get the result when only one or both conditions are matched. In this case, DBMS tools ignore the second condition of the WHERE clause if the first condition match.

Example

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE primary_poc = 'Tamara Tume' OR primary_poc = 'Jodee Lupo'

when you need to match both conditions to get the result then you should use the AND operator over OR.

Sometimes IN and ORcan accomplish the same thing; however, generally with IN you don't have to think about the order in which you're placing your different conditions. Using IN, we can use another SELECT for subqueries and when talk began for the performance issue of your complex query then you can use IN over OR because IN executes faster than OR.

OR with AND

When we use OR with AND we often make simple mistakes and get a different result.

Let's see below two examples for a better understanding:

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE id = 1001 OR id = 1021 AND name = 'McKesson'

Agian

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE (id = 1001 OR id = 1021) AND name = 'McKesson'

You may see different results in two queries. because here SQL performs OR before AND and the first condition is true then omit other conditions. In the second example, we use parenthesis to separate AND and OR conditions to get the actual result.

Note: SQL order of operation is AND before OR. but here you write OR before AND and OR first condition is true so, rest of the condition is omitted.

The NOT operator

Finally, we are here to talk about the NOT operator. when we want some result that does not contain a specific thing then we can use the NOT operator the get the result.

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE primary_poc IS NOT NULL AND NOT name = 'Apple'

Using Wildcards to Filter data

Sometimes we have to deal with string data and you don't know the whole string or maybe you are looking for something that starts with an alphabet/word or ends with a specific word/alphabet. that's why we use the Wildcards operator to perform filtering with SQL.

we can use

• Percentage(%)
• Underscore(_)

• Bracket []
  as a Wildcard operator. but keep in mind that each special character is not supported by all DBMS tools.

• DB2 does not support Underscore(_)

• SQLite does not support Bracket[]

Let's see some example:

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name LIKE 'A%'; -- name starts with 'A'

again

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name LIKE '%art'; -- name ends with 'art'

there are lots of tips and tricks out there over the internet. you can find them just simpling googling. I just want to share with you the basic concept to understand.

Thanks for reading.

Note: I would love to see your comments and accepting your valuable thought.