--- title: Conditions source: 'https://academia.sh/en/courses/sql-fundamentals/conditions' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Conditions Comparison operators, the precedence of logical connectives, range and membership checks, pattern matching with escape characters, and how case-sensitivity behavior depends on the engine. 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. ```sh 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','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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 ``` ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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.