SQL 3 : Filtering, Searching & Organizing Data

D

Qubits of DPK

April 11, 2026

Core DBMS

1. Recap of SQL 2

Previously we covered the four fundamental CRUD operations — INSERT to create rows, SELECT to read them, UPDATE to modify them, and DELETE to remove them. Now we go a level deeper and learn how to filter, search, and organize the data we retrieve. These are the concepts that power real backend APIs and analytics dashboards every single day.

2. DISTINCT

When you query a table, duplicate values often appear because multiple rows share the same data in a column. DISTINCT eliminates those duplicates and returns only unique values.
sql
QUBITS OF DPK
1-- Without DISTINCT: returns Engineering, Engineering, Marketing, Marketing
2SELECT department FROM employees;
3
4-- With DISTINCT: returns Engineering, Marketing
5SELECT DISTINCT department FROM employees;
A practical production use case is building filter dropdowns in a UI — if you want to show users a list of all cities your customers are from, you don't want the same city appearing fifty times:
sql
QUBITS OF DPK
1SELECT DISTINCT city FROM customers;

3. WHERE Clause

The WHERE clause is how you tell the database to return only the rows that satisfy a specific condition. Without it, every query returns the entire table — which is almost never what you want in production.
sql
QUBITS OF DPK
1SELECT *
2FROM employees
3WHERE salary > 60000;
The full set of comparison operators available inside WHERE are = (equal), != (not equal), >, <, >=, and <=. These can be combined in countless ways to express precise filtering logic.

4. AND, OR, and NOT

Real queries rarely filter on just one condition. The logical operators AND, OR, and NOT let you combine multiple conditions into a single, expressive filter.
AND requires all conditions to be true simultaneously:
sql
QUBITS OF DPK
1SELECT * FROM employees
2WHERE department = 'Engineering' AND salary > 60000;
OR requires at least one condition to be true:
sql
QUBITS OF DPK
1SELECT * FROM employees
2WHERE department = 'Engineering' OR department = 'Marketing';
NOT inverts a condition, returning everything that does not match:
sql
QUBITS OF DPK
1SELECT * FROM employees
2WHERE NOT department = 'HR';

5. IN Operator

When you find yourself writing a long chain of OR conditions checking the same column against different values, IN is the cleaner alternative. Both queries below produce identical results, but the second is far more readable:
sql
QUBITS OF DPK
1-- Verbose OR chain
2WHERE department = 'Engineering' OR department = 'Marketing' OR department = 'Sales';
3
4-- Clean IN syntax
5WHERE department IN ('Engineering', 'Marketing', 'Sales');
Think of IN as shorthand for "this column's value is one of the following options." It also makes the query easier to extend — adding a fourth department is one word, not another OR clause.

6. ORDER BY

ORDER BY sorts your result set. By default it sorts ascending (ASC), meaning smallest to largest or A to Z. Appending DESC reverses this to largest-to-smallest.
sql
QUBITS OF DPK
1SELECT * FROM employees ORDER BY salary ASC;  -- cheapest first
2SELECT * FROM employees ORDER BY salary DESC; -- highest paid first
You can also sort by multiple columns. The database sorts by the first column, then uses the second column as a tiebreaker within groups that share the same first-column value:
sql
QUBITS OF DPK
1-- Sort by department alphabetically, then by salary (highest first) within each department
2SELECT * FROM employees
3ORDER BY department ASC, salary DESC;

7. LIKE Operator

LIKE enables pattern matching on text columns — it is how you build search functionality. It uses two wildcard characters: % matches any sequence of characters (including none), and _ matches exactly one character.
sql
QUBITS OF DPK
1-- Find all employees whose name starts with "John" (matches John, Johnny, Johnston...)
2SELECT * FROM employees WHERE name LIKE 'John%';
3
4-- Find all users with a Gmail address
5SELECT * FROM users WHERE email LIKE '%gmail.com';
6
7-- Find names where the second character is 'a' (matches 'Jane', 'Mark', etc.)
8SELECT * FROM employees WHERE name LIKE '_a%';
In a real backend, when a user types into a search box, the server typically builds a query like WHERE name LIKE 'searchTerm%' to return matching results efficiently.

8. IS NULL

NULL in SQL means "value is absent" — it is not an empty string, and it is not zero. It is the complete absence of a value. This distinction matters because you cannot compare NULL using =. The following query will never return any rows, even if NULL values exist:
sql
QUBITS OF DPK
1-- Wrong: this never works
2WHERE manager = NULL
3
4-- Correct: always use IS NULL
5WHERE manager IS NULL
The reason is that NULL = NULL evaluates to NULL in SQL (not TRUE), because you can't meaningfully compare two unknown values. IS NULL exists specifically to handle this edge case correctly.

9. LIMIT and OFFSET

LIMIT restricts how many rows are returned. OFFSET skips a specified number of rows before starting to return results. Together, they are the foundation of pagination — the mechanism that lets you load data page by page rather than all at once.
sql
QUBITS OF DPK
1-- Page 1: first 10 results
2SELECT * FROM products LIMIT 10 OFFSET 0;
3
4-- Page 2: next 10 results
5SELECT * FROM products LIMIT 10 OFFSET 10;
6
7-- Page 3: results 21–30
8SELECT * FROM products LIMIT 10 OFFSET 20;
The general formula is OFFSET = (page_number - 1) × page_size. This pattern is used in virtually every API that returns a list of items — product listings, social media feeds, search results, and more.

10. UPDATE

UPDATE modifies existing rows. The WHERE clause is critical here — without it, the update applies to every single row in the table, which is almost always catastrophic in production.
sql
QUBITS OF DPK
1-- Safe: updates only employee 101
2UPDATE employees SET salary = 75000 WHERE employee_id = 101;
3
4-- Dangerous: updates every employee's salary to 75000
5UPDATE employees SET salary = 75000;
Many production databases are configured with a "safe update mode" that rejects UPDATE and DELETE statements that lack a WHERE clause, precisely to prevent this class of accident.

11. DELETE vs TRUNCATE vs DROP

These three commands all remove data, but they operate at very different levels and have very different consequences.
sql
QUBITS OF DPK
1DELETE FROM employees WHERE department = 'HR';  -- surgical removal
2TRUNCATE TABLE employees;                        -- wipe all rows, keep table
3DROP TABLE employees;                            -- table no longer exists
The mental model: DELETE is a scalpel, TRUNCATE is a reset button, and DROP is demolition.

12. Interview Questions

What is the difference between WHERE and HAVING? WHERE filters individual rows before any grouping happens — it works on raw row data. HAVING filters groups after aggregation — it works on computed results like AVG(salary) or COUNT(*). You cannot use HAVING without GROUP BY, and you cannot use aggregate functions inside WHERE.
What is the difference between DELETE and TRUNCATE? DELETE removes rows one at a time and logs each deletion, which means it can be rolled back inside a transaction and triggers can fire on each row. TRUNCATE removes all rows in a single operation by deallocating the data pages — it is much faster for large tables but cannot be rolled back in most databases and does not fire row-level triggers.
Why can't you use = NULL in a WHERE clause? Because NULL represents an unknown value, and comparing two unknowns with = produces NULL (not TRUE). The database never treats NULL = NULL as a match. You must use IS NULL or IS NOT NULL to check for the presence or absence of a value.

13. Quick Revision

sql
QUBITS OF DPK
1DISTINCT         → remove duplicate values from results
2WHERE            → filter rows by condition
3AND / OR / NOT   → combine or negate conditions
4IN               → match against a list of values (cleaner than OR chains)
5LIKE             → pattern matching with % (any chars) and _ (one char)
6IS NULL          → check for absent values (never use = NULL)
7ORDER BY         → sort results, ASC by default, DESC to reverse
8LIMIT / OFFSET   → paginate results (OFFSET = (page - 1) × page_size)
9UPDATE + WHERE   → modify specific rows (always use WHERE!)
10DELETE           → remove specific rows
11TRUNCATE         → remove all rows, keep table structure
12DROP             → remove table entirely