Conditions

The previous lesson determined the result set’s columns: which columns, with which names, with which expressions. On the subject of rows, only one tool was used — a single equality condition. The clause that actually determines the number of rows a query returns is WHERE.

The WHERE clause evaluates a logical expression for every row the FROM clause produces and lets through only the rows that give a true result. This lesson builds every form that expression can take: comparison, connective, range, membership, and text pattern.

The block below sets up the schema and sample data; every query in this lesson runs against the library.db file created in this block.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

Comparison Operators

There are six comparison operators: =, <>, <, >, <=, >=. The standard syntax for the inequality operator is <>; most engines also accept !=, but <> is the portable syntax.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, publication_year FROM book WHERE publication_year > 1975;
SQL

title         publication_year
------------  ----------------
Blindness     1995            
Silent House  1983            

Two out of seven books came back. Four of the remaining five do not satisfy the condition; the fifth — Motherland Hotel — has an unknown publication year. Whether an unknown value is greater than 1975 is unknown too, so the row does not pass. How null values behave in conditions is the fifth lesson’s subject; for now the rule to keep in mind is this: a comparison that includes a null value is neither true nor false, and WHERE only lets through the true ones.

Text comparison uses the same operators.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, author FROM book WHERE author <> 'Oğuz Atay';
SQL

title             author           
----------------  -----------------
Blindness         José Saramago    
The Book of Sand  Jorge Luis Borges
Yaban             Yakup Kadri      
Silent House      Orhan Pamuk      
Motherland Hotel  Yusuf Atılgan    

In text comparison, whether two strings count as equal is decided by collation. Collation defines character order and case and accent sensitivity; the default collation depends on the engine. The same query can count the value 'oğuz atay' as equal on one engine and not on another.

Logical Connectives and Precedence

Conditions are combined with AND, OR, and NOT. The three have different precedence: NOT is highest, then AND, and OR is lowest. This is the same operator-precedence rule as in the Programming Fundamentals course, and it leads to the same mistake.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, author, publication_year FROM book
WHERE author = 'Oğuz Atay' OR author = 'Orhan Pamuk' AND publication_year > 1980;
SQL

title              author       publication_year
-----------------  -----------  ----------------
The Disconnected   Oğuz Atay    1972            
Silent House       Orhan Pamuk  1983            
Tehlikeli Oyunlar  Oğuz Atay    1973            

The result has two pre-1980 books. If the intent had been “either author’s books published after 1980,” the result would be wrong. Because AND binds more tightly, the condition parsed as “author is Oğuz Atay, or (author is Orhan Pamuk and year is greater than 1980).” Parentheses fix the intent.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, author, publication_year FROM book
WHERE (author = 'Oğuz Atay' OR author = 'Orhan Pamuk') AND publication_year > 1980;
SQL

title         author       publication_year
------------  -----------  ----------------
Silent House  Orhan Pamuk  1983            

The rule: when AND and OR appear in the same condition, parentheses are written. Relying on the precedence rule, even when it gives the correct result, makes it harder for whoever reads the query to infer the intent.

Range and Membership

A range condition written with two comparisons has a short form.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, publication_year FROM book WHERE publication_year >= 1970 AND publication_year <= 1980;
SQL

title              publication_year
-----------------  ----------------
The Disconnected   1972            
The Book of Sand   1975            
Tehlikeli Oyunlar  1973            

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, publication_year FROM book WHERE publication_year BETWEEN 1970 AND 1980;
SQL

title              publication_year
-----------------  ----------------
The Disconnected   1972            
The Book of Sand   1975            
Tehlikeli Oyunlar  1973            

The two queries return the same rows. BETWEEN is inclusive on both ends; the values 1970 and 1980 are inside the range. This point is often overlooked and causes mistakes especially with dates: when trying to select an entire month with BETWEEN, it gets confused whether the bound covering the month’s last day is a day or a day-and-time. On columns that carry a time component, the form that writes the lower bound inclusive and the upper bound exclusive (>= and <) is safer.

For a finite set of values, IN is written.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, author FROM book WHERE author IN ('Oğuz Atay','Orhan Pamuk');
SQL

title              author     
-----------------  -----------
The Disconnected   Oğuz Atay  
Silent House       Orhan Pamuk
Tehlikeli Oyunlar  Oğuz Atay  

IN is shorthand for equalities joined with OR. It improves readability in long lists and offers the engine a structure that can be planned as a single set check. Its negation is written NOT IN, and it behaves unexpectedly when the list contains a null value; this trap will be measured in the fifth lesson.

The Limit of Negation

NOT reverses a condition, but the word “reverse” is misleading when a null value is present.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, branch_id FROM book WHERE branch_id = 1;
SELECT title, branch_id FROM book WHERE NOT (branch_id = 1);
SQL

title             branch_id
----------------  ---------
Blindness         1        
The Disconnected  1        
title             branch_id
----------------  ---------
The Book of Sand  2        
Yaban             2        
Silent House      3        
Motherland Hotel  3        

The first query returned two rows, the second four. Six in total; yet the table has seven books. The missing row is Tehlikeli Oyunlar: because its branch id is null, it satisfies neither the first condition nor its negation. A condition and its negation together giving the whole table only holds when the relevant column has no null value.

Pattern Matching

Searching inside text is done with the LIKE operator. There are two wildcard characters: % matches zero or more characters, _ matches exactly one character.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title FROM book WHERE title LIKE '_li%';
SQL

title    
---------
Blindness

The pattern says “any character, followed by li, followed by anything,” and it matches only one title.

The real question is: is LIKE case-sensitive? The answer depends on the engine, and even within the same engine it can depend on whether the character is ASCII.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title FROM book WHERE title LIKE 't%';
SQL

title            
-----------------
The Disconnected 
The Book of Sand 
Tehlikeli Oyunlar

The lowercase pattern t caught three titles that start with uppercase T: on this engine, LIKE is case-insensitive for ASCII letters. The same engine is case-sensitive for non-ASCII letters.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE author LIKE '%ğ%';
SELECT author FROM book WHERE author LIKE '%Ğ%';
SQL

author   
---------
Oğuz Atay
Oğuz Atay

The second query returned no rows at all. The author Oğuz Atay contains ğ, but the pattern written with Ğ did not match — case folding applied only within the ASCII range. In Turkish text, this is a direct source of silent result loss.

Fixing the sensitivity within the query itself is not a full solution either, because case-conversion functions run into the same limit.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT UPPER('Körlük') AS upper_form, LOWER('KÖRLÜK') AS lower_form;
SQL

upper_form  lower_form
----------  ----------
KöRLüK      kÖrlÜk    

The conversion changed the ASCII letters; ö and Ö stayed as they were. The correct fix is not to patch the query, but to define a case- and accent-insensitive collation on the column, or to enable the engine’s Unicode support; both depend on the engine.

When the pattern itself needs to search for a literal % or _ character, an escape character is declared.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 'Sale %20' AS text,
       'Sale %20' LIKE '%!%%' ESCAPE '!' AS escaped,
       'Sale %20' LIKE '%%%'             AS unescaped
UNION ALL
SELECT 'Sale 20',
       'Sale 20'  LIKE '%!%%' ESCAPE '!',
       'Sale 20'  LIKE '%%%';
SQL

text      escaped  unescaped
--------  -------  ---------
Sale %20  1        1        
Sale 20   0        1        

The ESCAPE '!' declaration makes !% read as “a literal percent sign.” The pattern without escaping matches both texts, because %%% means “anything at all.” The 1 and 0 values in the check columns represent logical true and false; how a boolean value gets written also depends on the engine.

Summary

• WHERE only lets through rows that give a true result; a row that is neither true nor false gets eliminated.
• NOT is highest precedence, AND is in the middle, OR is lowest; when both appear together, parentheses are written.
• BETWEEN is inclusive on both ends; on columns with a time component, an inclusive-lower, exclusive-upper form is safer.
• IN is shorthand for equalities joined with OR.
• A condition and its negation give the whole table only when the relevant column has no null value.
• LIKE treats % and _ as wildcards, and ESCAPE turns them into literal characters; case sensitivity depends on the engine and on whether the character is ASCII.

Next Step

Up to this point, which rows will come back has been determined, but the order they come back in has never been determined. The order seen in the outputs comes from how the engine reads the table, and it is not guaranteed. The next lesson takes up the clause that sorts the result set, multi-key sorting, where null values land in the sort, and the difference between the standard and common syntax for limiting the result to the first N rows.