SELECT & Filtering

Almost every job that touches data starts with a question: which orders came in this week, who has not filled in an email address, what are the five most expensive products? In a relational database you ask those questions with a SELECT statement. It is the most used piece of SQL by a wide margin, and the habits you build here carry into joins, grouping and every report you will ever write.

This chapter is a hands-on tour of reading data well. You will pick exactly the columns you need and rename them, narrow rows down with WHERE, and combine conditions with AND, OR and NOT. You will meet NULL, the value that quietly breaks comparisons, then sort results, page through them and remove duplicates. The last stop is the logical order a query runs in, which explains many puzzling errors, such as why an alias works in ORDER BY but not in WHERE.

Every example uses one small sample table, so you can see each result and predict it before you run anything. By the end you will be able to write a clear query from a plain-English question, explain why it returns the rows it does, and spot the common mistakes before they reach a report. The chapter closes with a list of those mistakes, practice queries and a one-page cheat sheet.

SQL (Structured Query Language) is how you talk to a relational database. It is a declarative language: you describe the rows you want, and the database works out how to fetch them. You never write a loop, pick an index or decide the order of the work.

This is why one short query can run well on six rows or six million: the database may scan the whole table or jump straight to the matches, and your query does not change either way.

Data lives in tables. A table is a grid: each row (also called a record) is one thing, such as one student, and each column (a field) is one fact about every row, such as the city. Every column has a type that says what kind of value it holds.

Every example in this chapter reads from one small table called students with six columns: id, name, city, age, marks and email. It has six rows, and email is NULL (no value stored) for two of them, Ravi and Kiran.

The runnable examples use Python's built-in sqlite3 module, so you can build this exact table in memory and try every query yourself. The first one creates the table, loads the six rows and reads them all back with SELECT *, which means all columns.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, city TEXT, age INTEGER, marks INTEGER, email TEXT)')
db.executemany('INSERT INTO students VALUES (?, ?, ?, ?, ?, ?)', [
    (1, 'Asha', 'Pune', 20, 88, 'asha@x.com'),
    (2, 'Ravi', 'Delhi', 22, 72, None),
    (3, 'Meena', 'Pune', 21, 95, 'meena@x.com'),
    (4, 'Kiran', 'Goa', 20, 72, None),
    (5, 'Zoya', 'Delhi', 23, 60, 'zoya@x.com'),
    (6, 'Dev', 'Goa', 19, 81, 'dev@x.com'),
])

for row in db.execute('SELECT * FROM students;'):
    print(row)

Python shows NULL as None

The id column is the table's primary key. It uniquely identifies each row, so no two students can share an id, and it can never be NULL. Two students may share a name or a city, but the id is how you point at exactly one of them.

SQL keywords such as SELECT, FROM and WHERE are case-insensitive, so select and SELECT mean the same thing. Names are another matter: depending on the database, table and column names may be case-sensitive, so the safe habit is to write them exactly as they were created. A statement ends with a semicolon, and spaces, tabs and newlines do not change its meaning, so you can spread a long query over several lines to keep it readable.

The next example runs the same question two ways, one lowercase on a single line and one uppercase over three lines, and checks that the answers are identical.

SELECT is read-only. It builds a result to show you and leaves the table exactly as it was, so you can run it as often as you like without risk. The final example tries to add a second row with id 1, which the primary key refuses, and then counts the rows before and after to show nothing moved.

The SELECT list decides which columns appear in the result. This section uses a smaller, four-column version of the students table (id, name, city and marks), so every result is short enough to check by eye.

Name the columns you want after SELECT, separated by commas. You get exactly those columns, in the order you wrote them, not the order they were stored in. Swap the two names and the output columns swap too. The program below builds the table in an in-memory SQLite database and defines a small show helper that prints a query's result. Later examples reuse both.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (id INTEGER, name TEXT, city TEXT, marks INTEGER)")
rows = [(1, 'Asha', 'Pune', 82), (2, 'Ravi', 'Delhi', 74), (3, 'Meena', 'Goa', 91),
        (4, 'Kiran', 'Pune', 67), (5, 'Zoya', 'Delhi', 88), (6, 'Dev', 'Goa', 59)]
db.executemany("INSERT INTO students VALUES (?, ?, ?, ?)", rows)

def show(sql):
    cur = db.execute(sql)
    print(" | ".join(c[0] for c in cur.description))
    for row in cur:
        print(" | ".join(str(v) for v in row))

show("SELECT name, city FROM students")
print()
show("SELECT city, name FROM students WHERE id <= 2")

SELECT name, city FROM students; then the same columns swapped

When you want every column, write SELECT *. The star expands to all columns in their stored order. It is handy when you are exploring an unfamiliar table and just want to see what is in it.

The star is risky inside application code. If someone later adds a column to the table, such as an email address, your query silently returns it as well. Code that reads results by position, or that should never see certain data, can then break or leak. Naming the columns keeps the result stable.

A SELECT does not even need a table. Without FROM, it simply computes the values you give it, so it works as a calculator.

show("SELECT 2 + 3")

SELECT 2 + 3; with no FROM

Each item in the select list can be an expression, not just a column name. The database evaluates it once per row. Here every student gets 5 bonus marks added, and the result column is named bonus_marks. The stored marks column is not touched.

show("SELECT name, marks + 5 AS bonus_marks FROM students")

an expression in the select list, renamed with AS

The keyword AS gives an output column a new name, called an alias. It renames the column only in this result. The table itself keeps its original column names, and the next query that asks for marks still finds it.

show("SELECT marks AS score FROM students WHERE id = 1")
print()
show("SELECT marks FROM students WHERE id = 1")

the alias lives only in one result

If you want the alias to contain spaces, wrap it in double quotes. Standard SQL uses double quotes for names, while single quotes are for text values.

show('SELECT marks AS "Final Score" FROM students WHERE id = 1')

double quotes around an alias with a space

Tables can have aliases too. Write the short name right after the table in FROM, and use it as a prefix for columns. This is not needed with one table, but it becomes essential once a query touches several tables.

show("SELECT s.name, s.city FROM students s WHERE s.id = 1")

FROM students s lets you write s.name

A common surprise is that you cannot use a select-list alias inside WHERE of the same query. The reason is the order in which the database works: it filters rows with WHERE before it builds the select list, so the alias does not exist yet when WHERE runs.

So the filter has to repeat the real column or expression. The alias is only for the result. Some engines, SQLite among them, happen to tolerate an alias in WHERE, but standard SQL and most other databases reject it, so do not rely on that.

-- fails in standard SQL: score does not exist yet
SELECT name, marks AS score FROM students WHERE score > 80;

alias used in WHERE

show("SELECT name, marks AS score FROM students WHERE marks > 80")

filter on the real column instead

Now compare the two ways of choosing columns side by side.

A plain SELECT hands back every row in the table. The WHERE clause narrows that down. For each row, the database evaluates the condition you wrote. It keeps the row only when the condition is true and drops it otherwise.

The examples in this section run against a small students table in an in-memory SQLite database. The helper show prints each query and then its rows. Asha and Meena live in Pune, so asking for Pune should return exactly those two.

The = in that query is one of six comparison operators. Each compares the value on its left with the value on its right and gives true or false for that row.

The operators work the same way on numbers. Two queries cover the numeric cases. marks > 80 is strict, so a score of exactly 80 would not pass. age <= 20 includes 20 itself, so Asha and Dev, who are both 20, make it in alongside Kiran, who is 19.

Ravi scored 72, so marks > 80 leaves him out. The condition was false for his row.

What you put around a word tells SQL what kind of thing it is. Getting this wrong is the most common beginner error.

<> is the opposite of =: it keeps every row whose city is anything but Pune. != means the same thing in most databases, but <> is the standard spelling.

Text comparison needs care with capital letters. In some databases the comparison is case-sensitive, so 'pune' does not match 'Pune'. Others ignore case by default. SQLite, used here, is case-sensitive for =, so the lowercase query below finds nothing. Do not rely on one database's behaviour when you write for another.

Either side of an operator can also be a column. marks > age compares two values from the same row, and the database does that for every row in turn. With this data every student's marks are higher than their age, so all five rows pass. The point is that the right side does not have to be a fixed value.

A single comparison rarely answers a real question. You usually want students from one city and above a mark, or from one of two cities. SQL lets you join conditions with three keywords: AND, OR and NOT. Each one takes conditions that are TRUE or FALSE for a row and produces a new TRUE or FALSE.

The examples in this section use the sample table of five students with the columns id, name, city and marks. The code below builds it in memory and defines a small helper, names, that runs a WHERE clause and prints who matched.

AND keeps a row only when both conditions are true. If either side is false, the row is dropped. Asking for students from Pune who also scored above 90 leaves a single name.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (id INTEGER, name TEXT, city TEXT, marks INTEGER)")
db.executemany("INSERT INTO students VALUES (?, ?, ?, ?)", [
    (1, 'Asha', 'Pune', 85),
    (2, 'Kiran', 'Goa', 78),
    (3, 'Dev', 'Goa', 88),
    (4, 'Meena', 'Pune', 95),
    (5, 'Rohan', 'Delhi', 72),
])

def names(where):
    sql = "SELECT name FROM students WHERE " + where + " ORDER BY id"
    rows = db.execute(sql).fetchall()
    print(where, "->", ", ".join(r[0] for r in rows))

names("city = 'Pune' AND marks > 90")

WHERE city = 'Pune' AND marks > 90

Asha is in Pune but scored 85, so she fails the second test. Dev scored 88 and is not in Pune, so he fails both. Only Meena passes both.

OR is more generous: a row is kept when at least one condition is true. It is also kept when both are. Goa students plus anyone above 90 gives a wider list. NOT goes the other way and flips a single condition, so NOT city = 'Delhi' keeps every row whose city is anything other than Delhi.

names("city = 'Goa' OR marks > 90")
names("NOT city = 'Delhi'")

OR needs one true side, NOT flips the test

Kiran and Dev match through the city, Meena through her marks. The NOT query returns 4 of the 5 students, everyone except Rohan.

When one WHERE clause mixes several keywords, SQL needs a rule for which to apply first. Just as multiplication happens before addition in arithmetic, NOT binds tightest, then AND, and OR comes last. The database groups the AND parts together before it looks at any OR.

This rule is where many wrong results come from. Suppose you want students from Goa or Pune who scored above 90, and you write the clause the way you would say it out loud.

names("city = 'Goa' OR city = 'Pune' AND marks > 90")

Looks right, but is not

Because AND runs first, the database reads this as city = 'Goa' OR (city = 'Pune' AND marks > 90). Every Goa student qualifies on city alone, with no mark test at all, so Kiran and Dev (78 and 88) slip in. Asha is dropped because she is from Pune but scored only 85.

The fix is to say what you mean with parentheses. Anything inside brackets is worked out first, so the two cities are combined before the mark test is applied to them.

names("(city = 'Goa' OR city = 'Pune') AND marks > 90")

Parentheses force the grouping you meant

Now both Goa students and Asha are tested against marks above 90, and only Meena passes.

So far a condition was either TRUE or FALSE. With missing data there is a third outcome: comparing anything to NULL gives NULL, meaning unknown. WHERE keeps a row only when the whole condition is TRUE, so an unknown result drops the row. The combining keywords have specific rules for unknown values, and the code below prints each one.

def show(expr):
    value = db.execute("SELECT " + expr).fetchone()[0]
    label = {None: "NULL", 1: "TRUE", 0: "FALSE"}[value]
    print(f"{expr:<14} -> {label}")

for expr in ["TRUE AND NULL", "FALSE AND NULL", "TRUE OR NULL", "FALSE OR NULL", "NOT NULL"]:
    show(expr)

What the database says when one side is unknown

The logic is that the unknown side could turn out to be either value, and the result is only certain when the answer would be the same either way. FALSE AND anything is FALSE, and TRUE OR anything is TRUE, so those stay definite. The other cases remain NULL.

Sometimes you want rows whose value is one of several exact choices. You could chain = tests with OR, but the query gets long fast. The IN operator checks membership: the row passes when its value equals any item in the list.

The examples in this section run against a small students table with the columns name, city and marks. The Python below builds it in memory and defines a tiny run helper that prints each query and the names it returns.

WHERE city IN ('Goa', 'Pune') is shorthand for city = 'Goa' OR city = 'Pune'. Both forms keep the same four rows. Adding NOT in front flips the test, so NOT IN keeps only the rows whose city is missing from the list: Ravi (Delhi) and Zoya (Mumbai).

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (name TEXT, city TEXT, marks INTEGER)")
db.executemany("INSERT INTO students VALUES (?, ?, ?)", [
    ("Asha", "Pune", 88), ("Ravi", "Delhi", 72), ("Meena", "Goa", 95),
    ("Kiran", "Pune", 80), ("Zoya", "Mumbai", 60), ("Dev", "Goa", 75),
])

def run(sql):
    print(sql)
    names = [row[0] for row in db.execute(sql)]
    print(" ->", ", ".join(names) or "(no rows)")

run("SELECT name FROM students WHERE city IN ('Goa', 'Pune')")
run("SELECT name FROM students WHERE city = 'Goa' OR city = 'Pune'")
run("SELECT name FROM students WHERE city NOT IN ('Goa', 'Pune')")

IN, its OR equivalent, and NOT IN

When the question is "from this value to that value", use BETWEEN. It is inclusive on both ends, so a mark equal to the low or the high bound still passes. marks BETWEEN 72 AND 88 is the same as marks >= 72 AND marks <= 88.

In the sample table that keeps Asha (88), Ravi (72), Kiran (80) and Dev (75). Ravi and Asha sit exactly on the bounds and are still returned. Meena (95) and Zoya (60) fall outside.

The order matters: write the low value first. BETWEEN 88 AND 72 expands to marks >= 88 AND marks <= 72, which no number can satisfy. The database does not swap the bounds for you, and it raises no error. It just returns nothing.

run("SELECT name FROM students WHERE marks BETWEEN 72 AND 88")
run("SELECT name FROM students WHERE marks >= 72 AND marks <= 88")
run("SELECT name FROM students WHERE marks BETWEEN 88 AND 72")

Inclusive bounds, and the reversed-bounds trap

Exact comparison is no help when you only know part of a string. LIKE compares text against a pattern, and the pattern has two wildcards. Every other character in it must match literally.

Read a pattern from left to right. 'A%' means "starts with A, then anything", so only Asha matches. '%a' means "anything, then ends with a", which gives Asha, Meena and Zoya. '_a%' means "one character, then a, then anything", so the second letter must be a. Only Ravi qualifies: Asha has s second, Kiran has i, and Dev has e.

Wrapping both ends in % finds a fragment anywhere inside the text. '%an%' matches any name containing an, which here is just Kiran.

run("SELECT name FROM students WHERE name LIKE 'A%'")
run("SELECT name FROM students WHERE name LIKE '%a'")
run("SELECT name FROM students WHERE name LIKE '_a%'")
run("SELECT name FROM students WHERE name LIKE '%an%'")

Starts with, ends with, second letter, contains

Negate a pattern with NOT LIKE to keep the rows that do not match. Case handling also differs by database. SQLite, used here, ignores case for plain English letters, so 'a%' finds Asha. In PostgreSQL, LIKE is case-sensitive, so 'a%' would not match Asha. PostgreSQL offers ILIKE for a case-insensitive match. MySQL usually ignores case, depending on the column's collation.

run("SELECT name FROM students WHERE name NOT LIKE '%a'")
run("SELECT name FROM students WHERE name LIKE 'a%'")

NOT LIKE, and SQLite ignoring case

The three operators answer different questions. Pick the one that matches the shape of what you are looking for.

Some students in our sample table never gave an email address. SQL does not store a blank or a zero for them. It stores NULL, a marker that means the value is unknown or missing. NULL is not the number 0 and it is not the empty string ''. A student with an empty string has an email that happens to be blank. A student with NULL has no known email at all.

Here are the rows we will use for the whole section. Six students, four with an email and two without. Ravi and Kiran have NULL in the email column.

The natural way to find the students without an email is to write email = NULL. That query runs without any error and returns nothing, and the next page explains why. The correct test is the special operator IS NULL.

SELECT name, email
FROM students
WHERE email IS NULL;

IS NULL finds the missing emails

To get the opposite, the students who do have an email, use IS NOT NULL. It returns the other four rows.

Ordinary conditions are either true or false. NULL adds a third outcome: unknown. If you do not know a value, you cannot say whether it equals 5, is greater than 3, or equals another unknown value. So any comparison that involves NULL gives NULL, written here as unknown. This includes comparing NULL with itself.

This explains the failed query from the previous page. WHERE email = NULL evaluates to NULL for every row, and the next step shows what WHERE does with NULL.

WHERE keeps a row only when its condition is TRUE. A row whose condition is FALSE is dropped. A row whose condition is NULL is dropped too. NULL is not an error and not a match. It just fails the test quietly.

SELECT name
FROM students
WHERE email = NULL;

Every row gives NULL, so none is kept

Suppose you want everyone except the student whose email is a@x.com. You would expect five rows, but Ravi and Kiran are missing. Their email is NULL, so email <> 'a@x.com' is NULL for them, and WHERE drops NULL rows.

SELECT name, email
FROM students
WHERE email <> 'a@x.com';

Only 3 rows come back, not 5

If the students with a missing email should be included, say so explicitly with OR email IS NULL.

email NOT IN ('a@x.com', NULL) is shorthand for email <> 'a@x.com' AND email <> NULL. The second part is NULL for every row, so the whole condition can never be TRUE. The query returns no rows at all, even for Meera, Dev and Sana. This often happens when the list comes from a subquery or a column that contains a NULL.

SELECT name
FROM students
WHERE email NOT IN ('a@x.com', NULL);

One NULL in the list empties the result

When you display data, a column of NULLs looks unfinished. COALESCE takes a list of values and returns the first one that is not NULL. COALESCE(email, 'none') gives the email when there is one and the text none otherwise. It changes only the output, not the stored data.

Aggregate functions ignore NULL values. COUNT(email) counts only the rows where email has a value, while COUNT(*) counts every row. In our table that is 4 against 6, and the difference is the number of missing emails.

A table is a set of rows, and a set has no built-in order. When you run a plain SELECT, the database returns rows in whatever order is cheapest for it. On a small table that is often the order you inserted them, so the output looks stable. A new index, a larger table, a parallel scan or a database upgrade can change that order without any warning. If the order matters, ask for it with ORDER BY.

The examples below use the six-row students table from the sample data. The first block builds it in an in-memory SQLite database and defines a small helper q() that prints a query and its rows. NULL is shown as NULL.

ORDER BY sorts ascending by default, so smallest first. Add DESC after the column to get the highest value first. ASC exists too, but you rarely need to write it. Here both directions are run on the students with marks above 80.

You can sort by more than one column by separating them with commas. The database sorts by the first column, and only when two rows tie on it does it look at the second. So ORDER BY city ASC, marks DESC groups the students by city in alphabetical order, and inside each city puts the highest marks first. Each column gets its own ASC or DESC.

ORDER BY runs after the SELECT list is computed, so it can use an alias you defined there. It can also use a column position, where ORDER BY 2 means the second item in the SELECT list. Both examples below sort the same way.

NULL is not a number, so the standard does not say whether it sorts before or after real values, and databases disagree. This matters for Sana, whose marks are NULL. Most databases also let you decide explicitly with NULLS FIRST or NULLS LAST after the column.

The block below runs on SQLite, so the plain sort puts NULL first, like MySQL. PostgreSQL would put Sana at the bottom of the first result. The two explicit forms give the same answer on any database that supports them.

LIMIT n caps the result at at most n rows. If fewer rows match, you simply get fewer. Combined with a descending sort it answers the classic top-N question: the top 3 students are the first three rows of ORDER BY marks DESC. The sort comes first and LIMIT then cuts the sorted list.

OFFSET m skips the first m rows of the sorted result before LIMIT starts counting. So LIMIT 2 OFFSET 2 skips two rows and returns rows 3 and 4. With a page size of 2, that is page 2.

To turn a page number into an offset, skip all the earlier pages: OFFSET = (n - 1) * page_size. For a page size of 2, page 1 starts at offset 0, page 2 at offset 2 and page 3 at offset 4.

There is a catch with ties. Ravi and Kiran both have 72 marks, so ORDER BY marks DESC alone does not say which of them comes first. The database may choose differently on different runs or pages, so a student could appear on two pages or on none. The fix is a tiebreaker: add a unique column such as id as the last sort key. The paging loop below does exactly that.

LIMIT and OFFSET work in PostgreSQL, MySQL and SQLite, but not everywhere. SQL Server and Oracle use their own forms, and the standard OFFSET ... FETCH form is the most portable. The meaning is the same in every case: sort, skip, then take.

Paging with a big OFFSET gets slower the deeper you go. The database cannot jump to row 100001; it still reads and sorts the 100000 skipped rows and then throws them away. Page 1 is fast, page 5000 is not.

Keyset pagination avoids that. Instead of counting rows to skip, remember the last value you showed and ask for rows after it with a WHERE condition on the sort column. With an index on id, the database seeks straight to that spot. Say the previous page ended at id 2:

Our sample table students has six rows, but only three different cities, because several students live in the same place. When you only want to know which cities exist, repeating each one is noise. Putting the keyword DISTINCT right after SELECT tells the database to drop every repeated row from the result, so each different row appears once.

The program below builds the table in memory with Python's built-in sqlite3 module and defines a small show helper. The helper prints the query and then one line per result row. The later examples in this section reuse both db and show.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE students (id INTEGER, name TEXT, city TEXT, age INTEGER, email TEXT)')
rows = [
    (1, 'Asha', 'Pune', 20, 'asha@x.com'),
    (2, 'Ravi', 'Delhi', 22, None),
    (3, 'Meera', 'Pune', 20, None),
    (4, 'Kiran', 'Goa', 21, 'kiran@x.com'),
    (5, 'Neha', 'Delhi', 23, None),
    (6, 'Omar', 'Pune', 22, 'omar@x.com'),
]
db.executemany('INSERT INTO students VALUES (?, ?, ?, ?, ?)', rows)

def show(sql):
    print(sql)
    for r in db.execute(sql):
        print(r)

show('SELECT DISTINCT city FROM students')

Six students, three different cities

Pune appears three times in the table and Delhi twice, yet each shows up once in the result. DISTINCT works on the result, after the columns have been chosen. It never changes the stored data.

A common misunderstanding is that DISTINCT looks only at the first column after it. It does not. DISTINCT applies to the whole row of selected columns, and two rows count as duplicates only when every selected value is equal. Add a second column and the rows become harder to match, so more of them survive.

Two students live in Pune and are both 20, so those rows collapse. The third Pune student is 22, so that pair is different and stays. Delhi also has two students with different ages, so both Delhi pairs stay.

The rule of thumb is that adding columns to a DISTINCT query can only keep the row count the same or raise it, never lower it.

show('SELECT DISTINCT(city), age FROM students')

Brackets change nothing

Earlier you saw that NULL = NULL is not true, because NULL means unknown. DISTINCT makes an exception: it treats all NULLs as equal to each other for the purpose of removing duplicates. Three students have no email, and those three NULLs collapse into a single NULL row.

Python prints the SQL NULL as None. Six rows became four: three real emails and one NULL standing for all the missing ones.

You can also use DISTINCT inside an aggregate. COUNT(city) counts every non-NULL city, so it gives 6. COUNT(DISTINCT city) counts each different city once, so it gives 3. This is the usual way to answer how many different values there are.

To find duplicates, the database must be able to tell whether it has already seen a row. It does this by either sorting the rows so identical ones sit next to each other, or by hashing each row into a lookup structure. Both take extra time and memory. On six rows you will never notice, but on millions of rows DISTINCT can be a noticeably expensive step, and more selected columns make each comparison heavier.

This cost is the reason to use DISTINCT on purpose. It is the right tool when you really want the unique values, such as a list of cities for a dropdown. It is the wrong tool for hiding a problem.

Every query you have written so far starts with SELECT, so it is natural to assume the database starts there too. It does not. You write the SELECT list first because it reads like a sentence: "give me these columns from that table". Logically, though, the database must know which table you mean before it can talk about columns, so it begins with FROM.

Think of it as a pipeline. Each clause takes the rows produced by the clause before it, changes them, and passes the result on. Knowing the order of that pipeline explains a lot of behaviour that otherwise looks arbitrary, such as why some names are visible in one clause and missing in another.

GROUP BY and HAVING belong to a later chapter on aggregates, so here we only note where they sit: after WHERE has thinned the rows and before SELECT builds the output. The clauses you already know fit into the pipeline like this.

An alias is born in SELECT. Since WHERE runs before SELECT, the alias does not exist yet when the filter is checked. In standard SQL and in databases such as PostgreSQL, the query below is rejected with an error like "column boosted does not exist".

SELECT name, marks + 5 AS boosted
FROM students
WHERE boosted > 90;

Fails: WHERE runs before the alias boosted is created

The fix is to repeat the expression in WHERE, where the real column marks is available: WHERE marks + 5 > 90. A few databases, SQLite among them, are lenient and let the alias through, but do not rely on that. Code written this way breaks when moved to another database.

ORDER BY runs after SELECT, so every alias has already been created by the time sorting starts. That is why the same name that failed in WHERE works happily here. The program below builds the sample table students once, and later examples in this section reuse it.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (id INTEGER, name TEXT, city TEXT, marks INTEGER)")
db.executemany("INSERT INTO students VALUES (?, ?, ?, ?)", [
    (1, "Asha", "Pune", 82),
    (2, "Ravi", "Mumbai", 74),
    (3, "Meera", "Pune", 91),
    (4, "Karan", "Delhi", 67),
    (5, "Isha", "Chennai", 88),
])

def show(sql):
    print(sql)
    for row in db.execute(sql):
        print(" ", row)

show("SELECT name, marks + 5 AS boosted FROM students ORDER BY boosted DESC")

ORDER BY sorts by the alias boosted

LIMIT is the final step, so it never decides which rows get sorted. With ORDER BY marks DESC LIMIT 3, the database first sorts every row by marks, highest first, and only then keeps the first three. That is exactly why the query returns the top three.

show("SELECT name, marks FROM students ORDER BY marks DESC LIMIT 3")

Sorts all five rows, then keeps three

Take the query SELECT name FROM students WHERE city = 'Pune' ORDER BY name DESC LIMIT 1. Follow the rows through the pipeline: FROM supplies all five students, WHERE leaves the two in Pune, SELECT keeps only the name column, ORDER BY sorts the names from Z to A, and LIMIT 1 keeps the first one.

The program below runs each stage as its own query, so you can see the rows shrink and then get sorted and cut.

Everything above describes the logical order, the model that defines what the result must be. The database's optimizer is free to execute the steps differently, for example by using an index to read rows already sorted, as long as the result is identical to the logical order. You reason with the logical order, and the optimizer takes care of speed.

Whatever the logical order, the syntax order is fixed, and the database rejects clauses written out of place. Write them as SELECT, FROM, WHERE, ORDER BY, LIMIT.

SELECT name, marks
FROM students
WHERE city = 'Pune'
ORDER BY marks DESC
LIMIT 1;

The fixed order you write: SELECT, FROM, WHERE, ORDER BY, LIMIT

Most broken queries fail in the same ten ways. Some return an error, which is annoying but honest. The worse ones return a plausible-looking wrong answer. This section walks through each mistake, shows what it does to the result, and gives the fix. The first three are the ones that catch people most often.

The examples run on a small employees table, built with Python's built-in sqlite3 module so you can run everything yourself. Six people, four columns of facts, and a manager column that is empty (NULL) for three of them.

NULL means unknown, so comparing anything to it with = gives unknown, never true. A WHERE clause keeps only rows where the condition is true, so manager = NULL keeps nothing. No error, just zero rows.

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE employees (id INTEGER, name TEXT, city TEXT, dept TEXT, salary INTEGER, manager TEXT)')
db.executemany('INSERT INTO employees VALUES (?,?,?,?,?,?)', [
    (1, 'Asha', 'Pune', 'Sales', 50000, None),
    (2, 'Ravi', 'Mumbai', 'Sales', 42000, 'Asha'),
    (3, 'Meena', 'Pune', 'IT', 65000, None),
    (4, 'Karan', 'Delhi', 'IT', 58000, 'Meena'),
    (5, 'Isha', 'Mumbai', 'HR', 47000, None),
    (6, 'Dev', 'Pune', 'HR', 39000, 'Isha'),
])

def run(label, sql):
    rows = db.execute(sql).fetchall()
    print(f'{label}: {len(rows)} rows {rows}')

run('= NULL', 'SELECT name FROM employees WHERE manager = NULL')
run('IS NULL', 'SELECT name FROM employees WHERE manager IS NULL')

Builds the sample table and a small helper that prints the row count and rows.

SQL evaluates AND before OR, the same way multiplication comes before addition. So a AND b OR c means (a AND b) OR c. If you meant "Pune people who are in IT or HR", the unparenthesised version also lets in every HR employee in any city.

Isha works in Mumbai, yet she appears in the first result because dept = 'HR' stood on its own after the OR. Whenever a condition mixes AND with OR, add parentheses even if the default order happens to be right. It costs nothing and tells the next reader what you meant.

WHERE runs before SELECT, so an alias created in the SELECT list does not exist yet when WHERE is checked. Standard SQL databases such as PostgreSQL stop with column "bonus" does not exist. SQLite is lenient and accepts it, so do not rely on that. Two portable fixes: repeat the expression, or compute the alias in a subquery and filter in the outer query.

In standard SQL the two kinds of quote mean different things. Single quotes make a string value. Double quotes make an identifier, a name of a column or table. So city = "Pune" asks the database to compare city with a column called Pune, and PostgreSQL answers column "Pune" does not exist.

LIKE only becomes a pattern match when you add wildcards: % stands for any run of characters and _ for exactly one. Without them, LIKE 'Pune' is an exact match and behaves like =. The pattern is also matched against the whole value, so 'Mee' does not find Meena, but 'Mee%' does.

LIKE is for text. Using LIKE '%a' on a number column such as salary is an error in strict databases like PostgreSQL, and in lenient ones it silently converts the number to text first. If you want a numeric range, use BETWEEN or comparison operators instead.

x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last comparison is never true, so the whole condition can never be true and you get no rows at all. Our manager column contains NULLs, which makes it a perfect trap: "find people who manage nobody" returns nothing.

A table has no built-in order. Without ORDER BY, the database returns rows in whatever order is cheapest at that moment, and that can change after an insert, an index change or a different query plan. LIMIT 3 then gives you some three rows, not a stable three. Always say which rows you want first, and add a tie-breaker column so equal values cannot swap places.

SELECT name, salary
FROM employees
ORDER BY salary DESC, id
LIMIT 3;

Top three salaries, with id breaking ties so every run returns the same rows.

BETWEEN low AND high means >= low AND <= high. Swap the bounds and no number can be both at least 60000 and at most 40000, so the result is empty. The database does not reorder them for you.

SELECT * is great for exploring, but application code that uses it depends on the table's current shape. Add a column and the app receives data it did not expect, which may be a large or sensitive field. Reorder or rename columns and code that reads results by position quietly picks up the wrong values. It also pulls more data than needed. Name the columns you use.

SELECT id, name, city, salary
FROM employees
WHERE city = 'Pune';

Explicit columns keep the result stable when the table changes.

When a long query returns the wrong rows, do not stare at it. Rebuild it in small steps, checking the row count after each one. The step where the count jumps or drops unexpectedly is where the bug lives.

Reading a question and writing the query are two different skills. The quickest way to get better at the second is to turn every English question into the same short checklist before typing anything. The eight practice questions below all use the students table from the start of the chapter, and each one is solved by the same routine.

Notice that the checklist follows the order you would think in, which is close to the order the database actually works in. Deciding which rows you want comes before deciding how to show them. Keep this in mind as you read the solutions, and try to write each query yourself before looking at it.

The question asks for names only, so the SELECT list holds a single column. Only some rows qualify, so a WHERE clause compares the city column with a text value. Text values sit in single quotes.

"Top" means the highest marks first, so sort descending and then keep only the first two rows with LIMIT. Without the ORDER BY, LIMIT would return two arbitrary rows, because a table has no built-in order.

A missing email is stored as NULL, which means "unknown" rather than an empty text. Because NULL is not equal to anything, not even to itself, email = NULL never matches a row. The test has to be IS NULL.

Several students share a city, so a plain SELECT city would repeat Delhi. Adding DISTINCT collapses identical values into one row. ORDER BY then sorts those unique values, and ascending order is the default, so no keyword is needed.

This question has two requirements that must both hold: the city is one of two values, and the marks are over 80. The first requirement is a tidy job for IN, which saves writing two OR comparisons. The second is a plain comparison, joined to the first with AND.

The goal is every student whose name begins with a letter from the first half of the alphabet. A quick way to read the table is to list the initials that actually occur: the names begin with A, D, K, M, R and Z. So the letters we need are A, D, K and M, and each one gets its own LIKE pattern, where the percent sign stands for any run of characters. Joining the patterns with OR means a row passes if any one of them matches.

SELECT name
FROM students
WHERE name LIKE 'A%'
   OR name LIKE 'D%'
   OR name LIKE 'K%'
   OR name LIKE 'M%';

Matches Asha, Dev, Kiran and Meena; Ravi and Zoya start with later letters and are left out.

Paging is just LIMIT for the page size plus OFFSET for the number of rows to skip. With two rows per page, page one skips nothing and page two skips two. The ages in descending order are 23, 22, 21, 20, 20 and 19, and two students share the age 20. Adding id as a second sort key breaks that tie, so the page boundaries never shift between runs.

An alias renames a column in the output, and AS score is all it takes. The filter keeps students with at least 80 marks. Because ORDER BY runs after SELECT, it is allowed to use the alias, whereas WHERE, which runs earlier, is not.

SELECT name, marks AS score
FROM students
WHERE marks >= 80
ORDER BY score DESC;

Meena (95) is first and Asha (88) is second; the heading of the marks column now reads score.

Almost every read query you write is a trimmed version of one shape. Clauses must appear in this order, and only SELECT and FROM are required. Everything else narrows, cleans or arranges the rows you get back.

SELECT [DISTINCT] col1, col2
FROM   table_name
WHERE  condition
ORDER BY col [DESC]
LIMIT  n OFFSET k;

The full skeleton for reading rows

The examples on this page use a tiny students table, built here in an in-memory SQLite database so you can run it as is. Notice that Bilal has no score and Ravi has no city: those two gaps are NULLs, and they cause most of the surprises later in this sheet.

import sqlite3

con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE students (id INTEGER, name TEXT, city TEXT, score INTEGER)')
con.executemany('INSERT INTO students VALUES (?, ?, ?, ?)', [
    (1, 'Asha', 'Pune', 82),
    (2, 'Bilal', 'Delhi', None),
    (3, 'Chen', 'Pune', 91),
    (4, 'Dev', 'Mumbai', 74),
    (5, 'Anya', 'Delhi', 91),
    (6, 'Ravi', None, 67),
])

def q(sql):
    return con.execute(sql).fetchall()

rows = q("""
    SELECT name, score FROM students
    WHERE city IN ('Pune', 'Delhi') AND score IS NOT NULL
    ORDER BY score DESC, name
    LIMIT 3 OFFSET 0
""")
for row in rows:
    print(row)

Filter, sort with a tiebreaker, then take the first three rows

Chen and Anya both scored 91, so name after score DESC decides who comes first. Bilal is Delhi-based but has no score, so IS NOT NULL removed him. Ravi never matched the city list at all.

The WHERE clause keeps a row only when its condition is true. These are the building blocks you will reach for most often.

NULL means unknown, and comparing anything to an unknown gives unknown, which WHERE treats as not true. That is why = NULL never matches, and why a NULL inside a NOT IN list wipes out every row. The next example shows all of it on the sample table.

print(q('SELECT name FROM students WHERE score = NULL'))
print(q('SELECT name FROM students WHERE score IS NULL'))
print(q('SELECT name, COALESCE(score, 0) FROM students WHERE id <= 2'))
print(q("SELECT name FROM students WHERE city NOT IN ('Pune', NULL)"))
print(q("SELECT name FROM students WHERE city NOT IN ('Pune')"))

Reuses the students table and the q helper from the first example

The last line also shows a quieter effect: Ravi's city is NULL, so even the plain NOT IN ('Pune') leaves him out. If you want him, add OR city IS NULL.

For ordering, put DESC after the column you want reversed and always add a tiebreaker, ideally a unique column such as id, so equal values come back in a stable order. For paging, LIMIT n is the page size and the offset is (page - 1) * n.

You write SELECT first, but the database does not run it first. It works through the clauses in the order below, and that order explains why some things work in one clause and fail in another.

Because SELECT runs after WHERE, an alias you create in SELECT does not exist yet when WHERE is evaluated. ORDER BY runs after SELECT, so it can use the alias. Keep this order in mind when you debug the five mistakes below.

Each question uses the same students table from the chapter. Work out the answer on paper first, then read the explanation.

• SELECT describes the columns you want and WHERE keeps only rows whose condition is TRUE; reading never changes the table.
• Mix AND with OR only with parentheses, because AND is evaluated before OR.
• IN, BETWEEN (inclusive, low first) and LIKE (% many, _ one) are shortcuts for lists, ranges and text patterns.
• NULL is unknown: use IS NULL and IS NOT NULL, and watch for <> and NOT IN silently dropping NULL rows.
• Row order is guaranteed only by ORDER BY; add a tiebreaker such as id before using LIMIT and OFFSET for paging.
• DISTINCT works on the whole selected row, so treat it as a tool for real duplicates, not a patch for a wrong query.
• A query runs as FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT, which is why aliases work in ORDER BY but not in WHERE.