Snowflake SQL Tutorial 4 - BETWEEN, IN, LIKE, ILIKE & RLIKE Explained

Snowflake SQL · Episode 4

In this post we are going to cover five operators that go in a WHERE clause: BETWEEN, IN, LIKE, ILIKE and RLIKE.

All five are shorthand. BETWEEN is a range, IN is a list, and the last three match a pattern instead of a whole value. They add no new power to the WHERE clause. They make the same conditions much shorter to write and much easier to read, which is the whole point on a clause you will come back to in six months.

We will take them one at a time. Along the way we will settle the question everybody asks about BETWEEN by running the long way round beside it, and we will meet the same NULL trap from the last post in a new place.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 4 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Let's start with BETWEEN
  3. Now let's use BETWEEN on dates
  4. Now let's turn it around with NOT BETWEEN
  5. Now let's filter on a list with IN
  6. Now let's try NOT IN and count the rows
  7. Now let's match a pattern with LIKE
  8. Now let's get the case wrong on purpose
  9. Can we match something a wildcard cannot say?
  10. Let's put the five side by side

Let's create some sample data

Everything on this page runs against one small table called EMPLOYEES. The script below creates it and fills it with eight rows, so you can paste it into a Snowflake worksheet and follow along.

Sample data
-- Creates the EMPLOYEES table used by every episode in this series.
-- Run it once; every later episode can reuse it.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

It is the same table every post in this series uses, so you only set it up once.

Three columns do most of the work today. SALARY and HIRE_DATE are what BETWEEN ranges over, and DEPT_ID is the list column for IN.

Keep one detail from the INSERT above in mind. Every row has a SALARY, but one row’s DEPT_ID is NULL. That single NULL is the reason this post has a trap in it.

A second table, later on

One query near the end uses a different table, SALES_RAW. That is the deliberately messy one, with inconsistent spacing and free-text phone numbers. It comes from the same one-off setup script as EMPLOYEES, and we will introduce it where we use it rather than here.

Let's start with BETWEEN

BETWEEN takes a low bound and a high bound and keeps the rows that fall inside. Let's try it on salary.

Query
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY BETWEEN 90000 AND 110000;
Result4 rows · 2 columns
FIRST_NAMESALARY
Marcus96500.00
Lena104000.00
Priya96500.00
Grace92000.00

Four rows. We named the column once instead of twice, and on a clause with several conditions in it that saving compounds.

Which raises the question this operator is famous for. Are the two bounds themselves included, or does the range stop just short of them? An off-by-one at a boundary is the kind of bug you find weeks later in a monthly total, so it is worth knowing rather than assuming.

Now let's prove it instead of claiming it

Let's write the same question the long way, with >= and <=. If BETWEEN is inclusive at both ends, this has to return exactly the same rows.

The same question, long-hand
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY >= 90000 AND SALARY <= 110000;
Result4 rows · 2 columns
FIRST_NAMESALARY
Marcus96500.00
Lena104000.00
Priya96500.00
Grace92000.00

The same four people, in the same order. So BETWEEN is inclusive at both ends, and it is precisely a shorthand for >= AND <=.

The BETWEEN documentation says the same thing. The two grids above are how you check it on your own account in ten seconds.

Order of the bounds

The low bound goes first. BETWEEN 110000 AND 90000 is not an error and it is not a reversed range. It asks for rows that are both at least 110000 and at most 90000, which is nothing at all. An empty grid with no error message is the symptom.

Now let's use BETWEEN on dates

Salary makes the inclusivity easy to see, but dates are where this operator earns its keep. One quarter, one month, one financial year. The operator does not change.

Query
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '2020-01-01' AND '2022-12-31';
Result4 rows · 2 columns
FIRST_NAMEHIRE_DATE
Marcus2021-07-01
Tomas2022-09-05
Priya2020-11-17
Grace2021-04-19

Four rows, and the same inclusivity applies. '2020-01-01' and '2022-12-31' are both in range. That is why a date range written like this normally ends on the last day of the period rather than the first day of the next one.

Dates versus timestamps

The inclusive high bound is a trap on a TIMESTAMP column rather than a DATE one. HIRE_DATE here is a DATE, so there is nothing after it in the day. A timestamp later in the day on '2022-12-31' is greater than '2022-12-31' itself, which reads as midnight. So a whole day of rows quietly falls outside the range.

Now let's turn it around with NOT BETWEEN

We put NOT in front and we get everything outside the range.

Query
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY NOT BETWEEN 90000 AND 110000;
Result4 rows · 2 columns
FIRST_NAMESALARY
Aisha118000.00
Tomas87250.00
Daniel131000.00
Sofia71400.00

The other four people. Four inside the range plus four outside it accounts for all eight rows in the table, so nothing went missing here.

Hold on to that arithmetic working out. The next pair of operators is where it stops working, and the reason has nothing to do with NOT.

Now let's filter on a list with IN

IN takes a comma-separated list and keeps the rows matching any item in it. Let's ask for two departments.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID IN (10, 30);
Result5 rows · 2 columns
FIRST_NAMEDEPT_ID
Aisha10
Marcus10
Priya30
Daniel10
Sofia30

Five rows. Written out, that condition is DEPT_ID = 10 OR DEPT_ID = 30, so IN is shorthand for a chain of ORs on one column.

At this length the OR version is no worse. The difference shows up when the list has twenty items in it. IN stays one readable line and the OR chain does not.

It works on text as well

Same operator, single-quoted values.

Query
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE LAST_NAME IN ('Khan', 'Lin', 'Novak');
Result3 rows · 2 columns
FIRST_NAMELAST_NAME
AishaKhan
TomasNovak
GraceLin

Three rows. One thing to know about that list: if we put a value in it that matches nothing, Snowflake ignores it. No error and no warning. IN asks for the rows that match something in our list. It does not check that every item in our list exists.

Now let's try NOT IN and count the rows

IN (10, 30) returned five rows out of eight. So NOT IN (10, 30) should return three. Let's run it.

Query
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID NOT IN (10, 30);
Result2 rows · 2 columns
FIRST_NAMEDEPT_ID
Lena20
Tomas20

Two. Five and two make seven, and the table has eight rows. One employee is in neither result.

It is Grace, and the reason is the one value we flagged in the setup section. Her DEPT_ID is NULL.

Asking “is NULL one of 10 or 30?” does not produce false. It produces NULL, which is neither true nor false, and so does its negation. The WHERE clause documentation states the consequence plainly: “if an expression evaluates to NULL, the row for that expression is removed from the result set.” A row that is unknown fails both the question and its opposite.

Shown twice on purpose

This is the second post in a row where a row vanishes from a result and nothing tells us. That repetition is deliberate. It is not a quirk of <> from the last post and it is not a quirk of NOT IN. It is how NULL behaves everywhere in a WHERE clause. NOT IN is simply where it does the most damage, especially when the list comes from a subquery rather than from literals, where a single NULL empties the whole result.

The fix, and the rules behind it, get a post of their own shortly. For now the habit worth forming is the arithmetic check. If a filter and its negation do not add up to the row count of the table, the column we filtered on has NULLs in it.

Now let's match a pattern with LIKE

BETWEEN and IN compare whole values. LIKE compares a shape, using two special characters that the Snowflake LIKE / ILIKE / RLIKE documentation defines precisely.

  • “A percent sign (%) matches any sequence of zero or more characters.”
  • “An underscore (_) matches any single character.”

Let's put % at the end, which asks for a prefix. Everything starting with that text.

Query — starts with
SELECT FIRST_NAME, EMAIL FROM EMPLOYEES WHERE EMAIL LIKE 'a%';
Result1 row · 2 columns
FIRST_NAMEEMAIL
Aishaaisha.khan@example.com

One row. It is the only email address beginning with a lowercase a.

Now let's put a percent on both sides

A % at each end asks for contains.

Query — contains
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '%an%';
Result2 rows · 2 columns
FIRST_NAMELAST_NAME
AishaKhan
PriyaRaman

Two rows. Khan and Raman both end in an, and that is enough to match. “Zero or more” is the part that makes it work. Each % is happy to match nothing at all, so a value that starts or ends with the text still comes back.

Now let's use an underscore instead

An underscore stands for exactly one character.

Query
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE '_isha';
Result1 row · 1 column
FIRST_NAME
Aisha

One row. The pattern asks for any single character followed by isha.

It is worth being clear about what that excludes. A name with two characters in front of isha would not match, because _ is one character, not one-or-more. If you want one-or-more you are asking for a regular expression, which is the last part of this post.

PatternMeansMatches
'a%'Starts with aa, ab, abc
'%an%'Contains anan, Khan, Raman
'_isha'Exactly one character, then ishaAisha, but not Alisha
'%'Any value at alleverything except NULL

Now let's get the case wrong on purpose

Here is the thing about LIKE that catches people, and it is not buried in a footnote. It is the first line of the documentation: “Performs a case-sensitive comparison to determine whether a string matches or does not match a specified pattern.”

Let's ask for a name in lowercase that the table stores with a capital letter.

Query — lowercase pattern
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'aisha';
Result0 rows · 1 column
FIRST_NAME

Zero rows. The pattern is spelled correctly, the column is spelled correctly, and the employee exists. The data stores a capital A.

No error, no warning, just an empty grid. That is the most expensive kind of wrong answer, because it looks like a fact about your data.

The next sentence of the documentation hands us the fix: “For case-insensitive matching, use ILIKE instead.” Same pattern, one different keyword.

Query — ILIKE
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME ILIKE 'aisha';
Result1 row · 1 column
FIRST_NAME
Aisha

One row. Nothing else about the query changed.

Which one to default to

Build the habit on the input, not on the data. If the pattern came from a search box, a spreadsheet, a ticket or anywhere else a human typed it, you almost always want ILIKE. Reach for LIKE when the case genuinely carries meaning: a code, a key, a flag.

Can we match something a wildcard cannot say?

% and _ cover a surprising amount of ground, but they cannot express alternatives. There is no LIKE pattern meaning “starts with an A, an M or a P”.

That is what the third operator is for. The documentation is explicit about why it behaves differently: “RLIKE uses POSIX ERE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.” Let's ask for those three starting letters.

Query
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME RLIKE '^[AMP].*';
Result3 rows · 1 column
FIRST_NAME
Aisha
Marcus
Priya

Three rows. In that pattern ^ anchors the match to the start of the string, [AMP] is a character class meaning any one of those three letters, and .* is “anything, any number of times”, which is the regular-expression spelling of %.

Now let's try it on messy real data

That example was a neat one. Pattern matching usually earns its place on text nobody validated on the way in.

SALES_RAW is this course’s deliberately messy table. Its phone numbers are free text, some written with a parenthesised area code and some not. A regular expression can ask for exactly that format.

Query
SELECT CUSTOMER_TXT, PHONE_TXT FROM SALES_RAW WHERE PHONE_TXT RLIKE '^\\(\\d{3}\\).*';
Result2 rows · 2 columns
CUSTOMER_TXTPHONE_TXT
Northwind Traders (704) 555-0142
Meridian Health(617) 555-0179

Two rows. The doubled backslashes in that pattern are not a typo and they are not part of the regular expression. Snowflake processes backslash escape sequences inside a single-quoted string literal, so writing \\( in the SQL is how we hand the regular expression a single \(.

What RLIKE actually receives is ^\(\d{3}\).*. That is: anchored at the start, then a literal opening parenthesis, escaped because a bare parenthesis groups, then three digits, then a literal closing parenthesis, then anything.

Now look at the first CUSTOMER_TXT value in that grid rather than at the pattern. It still carries its leading and trailing spaces. The pattern matched despite them because we applied it to PHONE_TXT and not to the name. A value like that will break a join, a GROUP BY or an equality test later on. Cleaning it is the TRIM family, which is Episode 65.

There is much more here

Regular expressions get two full posts later in the course, covering REGEXP_LIKE, REGEXP_SUBSTR and capture groups. RLIKE is the door to all of it. For now it is enough to know it exists, and that it is a different pattern language from LIKE.

Let's put the five side by side

All five are shorthand. Nothing here is impossible with the comparison operators and OR.

That is worth remembering when one of them surprises you. You can always rewrite it the long way and look at the difference, which is exactly how we settled the BETWEEN question above.

OperatorLong-hand equivalentReach for it when
BETWEEN a AND bcol >= a AND col <= bA continuous range of numbers, dates or timestamps. Both ends included.
IN (a, b, c)col = a OR col = b OR col = cA fixed list of values, especially a long one.
LIKE 'pattern'no single-operator equivalentA prefix, suffix or contains test. Case-sensitive. % is any run of characters, _ is exactly one.
ILIKE 'pattern'the usual workaround elsewhere is UPPER(col) LIKE UPPER('pattern')The same test on text a human typed, where case should not decide the answer.
RLIKE 'regex'no equivalentAlternatives, anchors, repetition and character classes. Anything wildcards cannot say.

Put NOT in front of BETWEEN or IN for the complement. And remember what the complement does not include, which is any row where the column is NULL.

Key points

  • BETWEEN is inclusive at both ends, and exactly equivalent to >= AND <=. That was shown above rather than asserted.
  • The low bound is written first. A reversed range returns an empty result, not an error.
  • IN is shorthand for a chain of ORs on one column, works on numbers and quoted text, and silently ignores list items that match nothing.
  • IN and NOT IN do not add up to the row count of the table when the filtered column contains NULL. The unknown row fails the question and its negation alike.
  • That missing row is not an operator quirk. It is how NULL behaves in any WHERE clause, and it is worst with NOT IN over a subquery.
  • In a LIKE pattern % matches any sequence of zero or more characters and _ matches exactly one. One, never one-or-more.
  • LIKE is case-sensitive. Mis-cased patterns return an empty grid with no error, which is the hardest kind of mistake to spot.
  • ILIKE is the same operator without the case sensitivity. Default to it for anything a human typed.
  • RLIKE swaps the SQL pattern syntax for POSIX ERE regular expressions, for alternatives and anchors that wildcards cannot express.
Next in this series

In the next post we will cover AND, OR and NOT. We combine several conditions in one WHERE clause, and we meet the operator precedence mistake that everybody makes exactly once.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES data is invented demo data created by that script, and SALES_RAW is a deliberately untidy table from the same setup. No performance or benchmark claims are made anywhere in this series. Operator descriptions are quoted from the Snowflake LIKE / ILIKE / RLIKE documentation, the BETWEEN documentation and the WHERE clause documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)