DEV Community

Md. Jamal Uddin
Md. Jamal Uddin

Posted on

Filtering Data with SQL

cover
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:

customers

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;
Enter fullscreen mode Exit fullscreen mode

WHERE Clause

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

operators

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
    WHERE name = 'Walmart';
Enter fullscreen mode Exit fullscreen mode

Alt Text

another example

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE id = 1011
Enter fullscreen mode Exit fullscreen mode

Alt Text

Non-matches data

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name <> 'Alice'
Enter fullscreen mode Exit fullscreen mode

Alt Text

Filtering with a Range of Data

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE id BETWEEN 1001 AND 1021
Enter fullscreen mode Exit fullscreen mode

Alt Text

Filtering with no value

SELECT
    id,
    name,
    website,
    primary_poc
FROM accounts
  WHERE name IS NULL
Enter fullscreen mode Exit fullscreen mode

Alt Text

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');
Enter fullscreen mode Exit fullscreen mode

Alt Text

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'
Enter fullscreen mode Exit fullscreen mode

Alt Text

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'
Enter fullscreen mode Exit fullscreen mode

Alt Text

Agian

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

Alt Text

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'
Enter fullscreen mode Exit fullscreen mode

Alt Text

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'
Enter fullscreen mode Exit fullscreen mode

Alt Text

again

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

Alt Text

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.

Discussion (0)