Chapter 2 of 2
Course Progress0 of 2 completed (0%)

SQL & Relational Database Mastery · Chapter 2 · ~15 min · Beginner

Filtering, Sorting & The NULL Trap

Master the core toolkit for finding exactly what you need in a database: multi-condition filtering with WHERE, sorting with ORDER BY, pagination with LIMIT, and avoiding SQL's #1 pitfall: three-valued NULL logic.

  • Filter records with =, !=, <, >, AND, and OR
  • Match text patterns using LIKE '%search%' and IN
  • Sort ascending (ASC) and descending (DESC) with ORDER BY
  • Safely handle missing data with IS NULL and IS NOT NULL

1Filtering Records with WHERE

In a production database with millions of records, you rarely want to read every row. The WHERE clause acts as a sieve, only letting through rows that evaluate to TRUE.

Comparison Operators

  • = Equal to: WHERE role = 'Admin'
  • != or <> Not equal: WHERE status != 'banned'
  • > / < Greater / Less than: WHERE price > 100
  • >= / <= Greater or equal: WHERE age >= 18

Combining Conditions: AND vs OR

• AND: Both conditions must be true:

SELECT * FROM users WHERE active = TRUE AND role = 'Admin';

• OR: Either condition can be true:

SELECT * FROM products WHERE price < 20 OR on_sale = TRUE;
Concept Check

Which query returns all active users who are either an Admin or an Editor?

2Searching Text with LIKE & The IN Operator

What if you don't know the exact string, or you want to find all users whose email ends in @gmail.com? SQL provides the LIKE operator and the wildcard symbol %:

-- Find all users with a Gmail address:
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Find all products starting with 'Apple':
SELECT * FROM products WHERE name LIKE 'Apple%';

And when you want to check if a value matches any option in a list, don't write endless OR statements. Use IN:

-- Clean and readable:
SELECT * FROM orders WHERE status IN ('shipped', 'delivered', 'in_transit');
Concept Check

What does 'WHERE username LIKE '%dev%' match?

3Sorting and Pagination with ORDER BY & LIMIT

Databases store rows in arbitrary order on disk. If you want results sorted alphabetically, by highest score, or by newest signup date, you must explicitly declare ORDER BY:

ORDER BY column ASC / DESC

ASC (Ascending: 0 $\rightarrow$ 9, A $\rightarrow$ Z)
DESC (Descending: highest first, newest first).

SELECT * FROM products ORDER BY price DESC;

LIMIT & OFFSET (Pagination)

LIMIT 10 returns only the top 10 rows. Add OFFSET 20 to skip the first 20 rows (Page 3 of results!).

SELECT * FROM products ORDER BY price DESC LIMIT 5;
Concept Check

Which query returns the top 3 most expensive products?

4The Classic NULL Trap: Why '=' Fails on NULLs

In SQL, NULL does not mean zero (0) or an empty string (""). It represents missing or unknown information.

Because NULL is unknown, asking salary = NULL does not return true or false — it returns UNKNOWN! In SQL, anything compared with = NULL fails.

⚠️ The Dangerous Mistake: = NULL

In SQL, comparison with NULL yields UNKNOWN, so this returns zero rows:

-- BUG: returns ZERO rows even when customers have missing phone numbers!
SELECT * FROM customers WHERE phone = NULL;
✅ The Correct SQL Way: IS NULL / IS NOT NULL

Always use IS NULL or IS NOT NULL for reliable three-valued logic:

-- CORRECT: safely finds missing and present records
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;
Concept Check

How do you check for rows where the 'discount' column is missing or unset?

5Hands-On: Build and Test Queries Live

Use the interactive builder below to filter and sort real products. Notice how the SQL query updates dynamically, and watch how IS NOT NULL filters discounts!

Interactive SQL Query Builder & Sorter

6 row(s) matched

Generated SQL Statement:

SELECT name, category, price, discount
FROM products;
namecategorypricediscountstock status
Mechanical KeyboardElectronics$120$15 OFFIn Stock
Ergonomic ChairFurniture$350$50 OFFIn Stock
USB-C HubElectronics$35NULLOut of Stock
Standing DeskFurniture$480NULLIn Stock
Wireless MouseElectronics$60$10 OFFIn Stock
Noise Cancelling HeadphonesElectronics$250NULLIn Stock

6Chapter Knowledge Challenge

Test your mastery of WHERE conditions, pattern matching, sorting, pagination, and NULL logic:

Concept Check 1 of 2
1

You want the 5 highest-rated products with an active stock count greater than 0. Which query is correct?

Concept Check 2 of 2
2

A database table has 10 users: 8 have a phone number, and 2 have NULL. What does 'SELECT COUNT(*) FROM users WHERE phone != '555-0100';' return if no one has '555-0100'?

7Recap

  • The WHERE clause filters rows before any sorting or display happens.
  • Use parentheses when combining AND and OR to guarantee correct operator precedence.
  • Use LIKE '%text%' for pattern searching and IN ('val1', 'val2') to test membership in a set.
  • Sort output with ORDER BY column ASC/DESC and control row counts with LIMIT n.
  • Never write = NULL. Always check missing data using IS NULL or IS NOT NULL.

Congratulations on finishing Chapter 2! Mark as complete to update your syllabus:

Enjoying this free lesson? Your support helps us build more free animated chapters.

Support Us