Snowflake SQL Tutorial 12 - Why Your Query Returns ZERO Rows - Case Sensitivity in Snowflake Explained

Snowflake SQL · Episode 12

In this post we are going to cover comments, formatting and case sensitivity in Snowflake.

Two of those three are small. A comment is a note we leave inside a query for whoever reads it next, and Snowflake ignores it. Formatting is the spaces and the line breaks we lay a query out with, and Snowflake ignores those too. The third one is not small at all. Snowflake treats a name and a string by opposite rules, and that is why a query that looks perfectly correct comes back with zero rows.

We will start with some sample data, then the two kinds of comment, then the layout, and then we will spend the rest of the post on capital letters. This is the last post of Level 1.

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

Episode 12 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 comments
3. Now let's lay a query out badly on purpose
4. Now let's write a query with no capital letters in it
5. Now let's look for 'khan' with a small k
6. Now let's put an identifier in double quotes
7. Let's put all of it in three lines

Let's create some sample data

Every query on this page runs against one small table called EMPLOYEES. If you followed the earlier posts you already have it. If not, the script below creates it and fills it.

Copy this into a Snowflake worksheet and run it once. Then you can run everything on this page yourself.

Sample data — run once

-- 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');

Two columns do most of the work in the first half of this page, FIRST_NAME and SALARY. The one to look at closely is LAST_NAME.

Have a look at how the names go in. 'Khan', 'Reid', 'Fischer'. A capital letter and then lower case. That detail is the whole point of the second half of this page. The values are stored exactly as the INSERT wrote them, and nothing later changes them.

Let's start with comments

Two hyphens start a comment. Everything after them on that line is ignored by the parser, so we can write for the human reading the query instead of for Snowflake. Let's put one in the middle of a SELECT list.

Line comment — everything after -- is ignored

SELECT FIRST_NAME, -- this is a line comment
       SALARY
FROM EMPLOYEES LIMIT 2;

Result2 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00

Both columns came back exactly as they would have without the comment. That is the whole property worth knowing. A comment changes nothing about what the query does.

Now let's try the other kind of comment

The block form opens with /* and closes with */. Everything between them is ignored, including in the middle of a line, and including across as many lines as we like.

Block comment — /* ... */

SELECT FIRST_NAME, /* a block comment */ SALARY FROM EMPLOYEES LIMIT 2;

Result2 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00

The same result again. The block form earns its keep while we are debugging. We can switch off a whole chunk of a long query with two characters at each end, instead of putting hyphens on every line and then taking them all off again.

Worth a habit

Use them. A query that made perfect sense when you wrote it will not make sense to you in three months, and it certainly will not to the next person who opens it. The comment that pays for itself is not the one that says what the query does — the SQL already says that — it is the one that says why.

Now let's lay a query out badly on purpose

Spaces, tabs and newlines between the parts of a statement mean nothing to the parser. It collapses them before it does anything else. So let's take the same query and lay it out as badly as we can manage. Extra spaces, clauses wandering across lines, indentation lined up with nothing.

The same statement, laid out deliberately oddly

SELECT
            FIRST_NAME ,
                       SALARY
    FROM        EMPLOYEES
          LIMIT 2;

Here is that statement with its whitespace collapsed, which is how the capture log records it. The collapsing is itself the point of this section. Its real result is underneath it.

Query

SELECT        FIRST_NAME,
              SALARY
       FROM   EMPLOYEES
       LIMIT  2;

Result2 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00

Identical to the two commented queries above. So the layout is entirely for the human reading it, and it costs us nothing at all.

One habit is worth adopting. One clause per line. SELECT on one line, FROM on the next, WHERE on the next. When a query gets long enough to scroll, that habit is what lets us find the clause we are looking for without reading the whole thing.

Now let's write a query with no capital letters in it

Now the interesting part. The query below has no capital letters anywhere. Not in the keywords, not in the column names, not in the table name.

Query — not one capital letter

select first_name, salary from employees where dept_id = 10;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Daniel	131000.00

It runs. Keywords are not case sensitive, so select and SELECT are the same word. Most people capitalise keywords so they stand out from the names around them, and that is worth doing. It is a style choice though, not a requirement.

Now look at the column headings

FIRST_NAME and SALARY, in capitals. Nobody typed a capital letter in the query that produced them.

That is Snowflake folding every unquoted identifier to upper case before it looks anything up. first_name, FIRST_NAME and First_Name all arrive at the same stored name. That is exactly why we can type a name however we like and the query still works.

Let's try it from the other direction and ask for FIRST_NAME in capitals this time.

Query — typed in capitals

SELECT FIRST_NAME FROM EMPLOYEES LIMIT 2;

Result2 rows · 1 column

FIRST_NAME
Aisha
Marcus

The heading came back in capitals again, just as it did when we typed it in lower case. The case we type an unquoted name in makes no difference to anything, because by the time Snowflake resolves it the case is gone.

Now let's look for 'khan' with a small k

Let's count the employees whose last name is 'khan', with a lower-case k.

Query — lower-case 'khan'

SELECT COUNT(*) AS MATCHES_LOWER FROM EMPLOYEES WHERE LAST_NAME = 'khan';

Result1 row · 1 column

MATCHES_LOWER
0

Zero. There is an employee called Khan. Her row is in the setup script at the top of this page and she has been on screen in every episode of this course. And this query matches nothing.

Let's run the same query with a capital K.

Query — capital 'Khan'

SELECT COUNT(*) AS MATCHES_EXACT FROM EMPLOYEES WHERE LAST_NAME = 'Khan';

Result1 row · 1 column

MATCHES_EXACT
1

So the rule from the last section does not apply here. This is the distinction worth carrying around:

In WHERE LAST_NAME = 'Khan'	What it is	How case is treated
LAST_NAME	A name (an identifier)	Folded to upper case — case does not matter
'Khan'	A string (data)	Compared exactly as stored — case matters

Why your query returns zero rows

This is the single most common reason a beginner gets no rows back from a query that looks completely correct. Nothing is wrong with the syntax, nothing is wrong with the table, and there is no error message to read. The letters just do not match.

Can we compare without caring about case?

Yes, but we have to ask for it. We fold both sides with UPPER or LOWER, or we use ILIKE, which we met in episode 4.

Three patterns — not captured for this episode

-- Three ways to compare without caring about case.
-- UPPER on both sides:
SELECT * FROM EMPLOYEES WHERE UPPER(LAST_NAME) = UPPER('khan');

-- LOWER on both sides:
SELECT * FROM EMPLOYEES WHERE LOWER(LAST_NAME) = LOWER('KHAN');

-- ILIKE, which is case-insensitive by definition (Episode 4):
SELECT * FROM EMPLOYEES WHERE LAST_NAME ILIKE 'khan';

The important word in all three is both. UPPER(LAST_NAME) = 'khan' still matches nothing, because we folded one side and left the other alone.

Now let's put an identifier in double quotes

Quotes do not only apply to strings. Let's give two columns an alias in double quotes, one in lower case and one in capitals, and see what the headings do.

Query — two quoted aliases

SELECT FIRST_NAME AS "lower case alias", LAST_NAME AS "UPPER CASE ALIAS" FROM EMPLOYEES LIMIT 3;

Result3 rows · 2 columns

lower case alias	UPPER CASE ALIAS
Aisha	Khan
Marcus	Reid
Lena	Fischer

Both headers came back exactly as typed, spaces and all. Double quotes switch the folding off. Snowflake keeps the name exactly as we wrote it. That is genuinely useful when the result is going straight into a report and we want readable column titles.

There is a bill that comes with it. From that moment on, we have to match the name exactly. A column created as "lower case alias" cannot be referred to later as LOWER_CASE_ALIAS, because the folding that would have made those the same name has been switched off.

The episode 10 failure, explained

This closes the loop from episode 10, where a quoted alias produced an error. An alias defined as "Given Name" and then asked for as GIVEN_NAME is not a typo and not a bug — they are simply two different names, and quoting the first one is what made them different.

The practical advice is short. Use double-quoted identifiers where a human reads the output and nothing else refers to the name. Avoid them for anything a later query, view or pipeline has to reach for.

Let's put all of it in three lines

Everything on this page comes down to three rules. They are worth memorising as a set rather than one at a time, because each one is the exception that explains the next.

What you wrote	What Snowflake does with it	What that means for you
Unquoted name — last_name	Folded to LAST_NAME	Type it however you like
Quoted name — "Given Name"	Kept exactly as typed	Match it exactly from then on
String — 'Khan'	Compared byte for byte	Always case sensitive, either way

The third line is the one that costs people an afternoon, and it is the one with no error message attached. A name we got the case wrong in fails loudly. A string we got the case wrong in succeeds quietly and returns nothing.

That is the end of Level 1

We can now write a SELECT, choose and rename its columns, filter it, sort it, limit it and de-duplicate it, and we know what NULL does to all of those. Level 2 is aggregation: taking many rows and getting one row back.

Key points

• -- comments out the rest of the line; /* ... */ comments out everything between, including mid-line and across lines.
• Whitespace between the parts of a statement is collapsed before parsing, so layout is free — one clause per line is the habit that pays off.
• Keywords are not case sensitive: a query with no capital letters in it anywhere runs exactly the same as one written in capitals.
• Unquoted identifiers are folded to upper case, which is why first_name and FIRST_NAME are the same name — and why the result headers come back in capitals either way.
• Strings are the exception. LAST_NAME = 'khan' and LAST_NAME = 'Khan' are different comparisons, and only one of them matches.
• To compare without caring about case, fold both sides with UPPER or LOWER, or use ILIKE.
• A double-quoted identifier is kept exactly as typed and must be matched exactly from then on — that is why a quoted alias cannot be referred to later by its unquoted spelling.
• A wrong-case name gives you an error. A wrong-case string gives you an empty result and no explanation, which is why it catches everybody.

Next in this series

In the next post we will start Level 2 with COUNT. Many rows go in and one row comes out, and three counts on the same table return three different numbers. All of them correct.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the setup script above. The EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Identifier and quoting behaviour follows the Snowflake identifier requirements documentation.