Module 1, Session 7. SELECT and WHERE, DISTINCT, IS NULL, AND/OR/NOT and brackets, LIKE/ILIKE wildcards, BETWEEN and IN. Sorting and pagination are the next session.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Ask RetailMart precise questions: keep only the rows you need with WHERE, remove repeats with DISTINCT, find missing values with IS NULL, and combine conditions with AND, OR, NOT, LIKE, BETWEEN and IN.
Four pillars: ask RetailMart for exactly the rows you need
WHERE — the Row Sieve
DISTINCT — Remove Repeats
IS NULL vs the = NULL Trap
SELECT * vs Named Columns
AND, OR, NOT & Brackets
LIKE & ILIKE Patterns
BETWEEN & IN
From storing data correctly to asking it questions
SELECT DISTINCT title, year FROM library.books WHERE genre = 'History' AND year > 2010 AND language IN ('Hindi', 'English') AND title ILIKE '%bharat%'; -- 10 lakh cards in, 14 titles out
WHERE picks the rows, DISTINCT removes the repeats, AND/IN/ILIKE combine your wishes, and brackets stop one “or” from emptying the building.Each thing the reader asked for maps to one piece of a SELECT statement:
| The Reader’s Wish | SQL Clause | What PostgreSQL Does |
|---|---|---|
| 📚 History books only | WHERE genre = 'History' | PostgreSQL checks every row and keeps only those where the condition is TRUE. |
| 📅 Written after 2010 | AND year > 2010 | With AND, both conditions must be TRUE for a row to stay. |
| 🗣️ Hindi or English | IN ('Hindi', 'English') | One list instead of a chain of ORs, so there is no bracket to forget. |
| 🔤 “Bharat” somewhere in the title | ILIKE '%bharat%' | % means any characters; ILIKE ignores capital letters, LIKE does not. |
| 📗 Three copies of one edition | DISTINCT | Rows that repeat in every selected column are shown only once. |
| ❓ Books with no year written down | IS NULL | year = NULL finds nothing; year IS NULL finds the blanks. |
How PostgreSQL filters lakhs of tuples on the fly before delivering rows to memory.
Pick the columns you need, then keep only the rows that match
Name the columns you want after SELECT. Fewer columns means less data to read and send.
PostgreSQL tests the condition on every row. Only rows where it is TRUE stay; FALSE and UNKNOWN are dropped.
=, <>, >, <, >=, <= work on numbers, text and dates.
SELECT first_name, last_name, tier FROM customers.customers LIMIT 10;
SELECT first_name, last_name, tier FROM customers.customers WHERE tier = 'Gold' LIMIT 10;
SELECT order_id, net_total FROM sales.orders WHERE net_total >= 5000 LIMIT 10;
How PostgreSQL collapses identical records using hash aggregation or sort-unique steps.
Show each value once, and give columns readable names
SELECT DISTINCT city, state drops a row only when every selected column repeats, not just the first one.
tier AS customer_tier changes only the heading in the result. The table itself stays the same.
DISTINCT has to compare rows, which costs time on big tables. Never use it to hide repeats a wrong query made.
SELECT DISTINCT city FROM customers.addresses LIMIT 10;
SELECT DISTINCT city, state FROM customers.addresses LIMIT 10;
SELECT DISTINCT tier AS customer_tier FROM customers.customers LIMIT 10;
Why comparing with = NULL always fails and how IS NULL solves the missing data problem.
NULL means unknown, so it needs its own test
Comparing anything with NULL gives UNKNOWN, never TRUE. WHERE x = NULL keeps no rows at all.
IS NULL is the one test that finds missing values: parcels not delivered, tickets not resolved.
IS NOT NULL keeps only rows that have a value. Never write != NULL: it also returns nothing.
SELECT shipment_id, order_id FROM sales.shipments WHERE delivered_date = NULL LIMIT 10;
SELECT shipment_id, shipped_date FROM sales.shipments WHERE delivered_date IS NULL LIMIT 10;
SELECT shipment_id, delivered_date FROM sales.shipments WHERE delivered_date IS NOT NULL LIMIT 10;
Time to test your knowledge! Solve real production challenges across RetailMart customer tiers, low-cost product pricing, and missing data audits.
Core rules to commit to memory from Pillar 1.
The earlier in the query pipeline rows are eliminated, the less memory and CPU required for subsequent processing steps. Always restrict rows with WHERE.
Never use col = NULL or col != NULL. In SQL three-valued logic, comparisons with NULL evaluate to UNKNOWN. Always write IS NULL or IS NOT NULL.
DISTINCT evaluates the combination of every projected column. If you select customer_id, order_date, distinct applies to the pair together.
AS cannot be referenced in the same query's WHERE clause because WHERE executes before SELECT.
Why senior engineers and database architects reject SELECT * in production pull requests.
Fetching unneeded text, JSON, or bytea columns forces PostgreSQL to read TOAST tables from disk and transfers massive payload volumes over the network wire.
If you query SELECT first_name, email, PostgreSQL can satisfy the query entirely from a covering B-Tree index without ever visiting the table heap. SELECT * forces heap lookups.
When columns are added or reordered during migrations, application code binding columns by positional index will silently break or map data into incorrect object fields.
"In production APIs, we always specify explicit column lists. It minimizes disk I/O, enables index-only scans, reduces wire transfer latency, and decouples our application models from database schema changes."
Why operator precedence breaks queries and how parentheses prevent disastrous logic leaks.
Combine conditions, and put brackets where you mean them
A row stays only if every condition joined by AND is TRUE. NOT flips a condition.
A row stays if any condition joined by OR is TRUE.
A OR B AND C means A OR (B AND C). No error, just wrong rows. Mixing them? Add brackets.
SELECT store_id, city, square_ft FROM stores.stores WHERE city = 'Noida' AND square_ft > 3000 LIMIT 10;
SELECT store_id, city, square_ft FROM stores.stores WHERE city = 'Noida' OR city = 'Prayagraj' AND square_ft > 3000 LIMIT 10;
SELECT store_id, city, square_ft FROM stores.stores WHERE (city = 'Noida' OR city = 'Prayagraj') AND square_ft > 3000 LIMIT 10;
String matching mechanics with % (multi-character) and _ (single-character) templates.
Match patterns, ranges and lists
% = any characters, _ = exactly one. LIKE cares about capital letters; ILIKE does not.
price BETWEEN 100 AND 500 means 100 ≤ price ≤ 500, both ends included.
status IN ('Shipped', 'Delivered') says the same as two ORs, and cannot lose a bracket.
SELECT first_name, email FROM customers.customers WHERE first_name ILIKE 'a%' LIMIT 10;
SELECT product_name, price FROM products.products WHERE price BETWEEN 100 AND 500 LIMIT 10;
SELECT order_id, order_status FROM sales.orders WHERE order_status IN ('Shipped', 'Delivered') LIMIT 10;
Two subtle production defects that corrupt date reports and destroy database performance.
Writing BETWEEN '2025-01-01' AND '2025-01-31' expands to 2025-01-31 00:00:00. Any order placed after midnight on Jan 31st is silently dropped from your monthly revenue report!
Always use half-open intervals: date >= '2025-01-01' AND date < '2025-02-01'. This cleanly covers every millisecond of January without missing boundary records.
A leading wildcard like ILIKE '%sharma' cannot use a standard B-Tree index and triggers a full table scan. For high-throughput search, create a trigram index with pg_trgm.
LIKE 'Sharma%' CAN use standard B-Tree indexes with text pattern ops: CREATE INDEX idx_name ON table(col text_pattern_ops);
Time to test your knowledge! Tackle Gmail customer filters, order timestamp ranges, the live AND/OR precedence demo, and complex multi-filter investigations.
Core rules to commit to memory from Pillar 2.
Never write mixed AND and OR expressions without explicit parentheses. AND always binds tighter and will silently distort business filtering rules.
Because dates contain timestamps with time components, write half-open intervals with >= and < to prevent boundary record omission.
Prefix matching ('Sharma%') can use a B-Tree index. Leading wildcards ('%sharma') cannot, and need a Trigram GIN index for production speed.
col IN (v1, v2, v3) over chained OR predicates — it generates cleaner plans and concise code.
When to use pattern wildcards, trigram indexes, or dedicated lexical search engines.
Best for simple string matching, prefix searches (e.g. autocompleting country codes), and exact pattern checking. Low overhead but limited linguistic intelligence.
Splits words into 3-character slices. Enables sub-second ILIKE '%term%' substring searches and fuzzy typo-tolerant matching across lakhs of rows.
Parses text into normalized lexemes, strips stop words ('the', 'and'), stems word forms ('running' → 'run'), and ranks results by relevance with ts_rank.
"Use B-Tree LIKE for known prefixes. Use pg_trgm for arbitrary substring lookups. Migrate to PostgreSQL Full-Text Search (tsvector/tsquery) whenever users search free-form natural language documents requiring stemming and relevance ranking."
Questions interviewers really ask about today’s topic, with the answer and the trap.
=, !=, <, >) gives UNKNOWN, not TRUE. WHERE keeps only rows that are TRUE, so = NULL keeps nothing. Write IS NULL (or IS NOT NULL). On RetailMart’s sales.shipments, delivered_date = NULL finds 0 rows while delivered_date IS NULL finds every parcel still on the way.Close your notes. Answer from memory. Click any card to reveal the verified answer.
UNKNOWN, and WHERE keeps only TRUE rows. Write WHERE balance IS NULL.col1 and col2 match a row already in the result.___), then anything (%). “Arun” and “Ananya” match; “Ali” does not; “aditi” does not (LIKE is case-sensitive).2025-03-31 00:00:00, so everything later on 31 March is left out. Write >= '2025-03-01' AND < '2025-04-01'. (On a DATE column such as sales.orders.order_date BETWEEN is safe.)city IN ('Agra', 'Kanpur', 'Noida'). Same rows, shorter, and no chance of a missing bracket when you add an AND later.The rules to remember from today, for every WHERE you will ever write.
Never write = NULL or != NULL: both give UNKNOWN and drop every row. Write IS NULL or IS NOT NULL.
AND runs before OR. Without brackets, A OR B AND C means A OR (B AND C) and silently lets wrong rows in.
On a timestamp column, write >= start AND < next_start. BETWEEN stops at midnight of the last day and drops the rest of it.
city IN ('Agra', 'Kanpur', 'Noida') says the same as three ORs, reads better, and cannot lose a bracket when you add an AND.
LIKE is case-sensitive, ILIKE is not. % = any characters, _ = exactly one. A leading % makes PostgreSQL read every row.
SELECT DISTINCT city, state removes a row only when every selected column matches. It costs a sort or hash step, so never use it to hide a wrong result.
Every pattern from today as a real RetailMart query: run it, copy it, change it.
SELECT order_id, net_total FROM sales.orders WHERE net_total >= 5000 AND order_status IN ('Shipped', 'Delivered') LIMIT 10;
SELECT ticket_id, category FROM support.tickets WHERE resolved_date IS NULL LIMIT 10;
SELECT shipment_id, delivered_date FROM sales.shipments WHERE delivered_date IS NOT NULL LIMIT 10;
SELECT first_name, email FROM customers.customers WHERE email ILIKE '%@gmail.com' LIMIT 10;
SELECT first_name, last_name FROM customers.customers WHERE first_name LIKE 'A___%' LIMIT 10;
SELECT product_name, price FROM products.products WHERE price BETWEEN 100 AND 500 LIMIT 10;
SELECT event_id, event_timestamp FROM web_events.events WHERE event_timestamp >= '2025-03-01' AND event_timestamp < '2025-04-01' LIMIT 10;
SELECT store_id, city, square_ft FROM stores.stores WHERE (city = 'Noida' OR city = 'Prayagraj') AND square_ft > 3000 LIMIT 10;
SELECT order_id, order_status FROM sales.orders WHERE order_status NOT IN ('Cancelled', 'Returned') LIMIT 10;
SELECT DISTINCT city, state FROM customers.addresses ORDER BY state, city LIMIT 10;
Do these 4 drills on RetailMart in your own PostgreSQL before the next class.
🎯 400 practice questions for this session · Easy to Crazy hard, run and checked on the pagePRACTICE →Query customers.customers for all Gold and Silver customers who registered in 2025 (registration_date >= '2025-01-01' AND < '2026-01-01'). Then list the default addresses (is_default = TRUE) in Mumbai, Delhi or Bengaluru from customers.addresses, once with IN and once with ORs in brackets.
Find all shipments in sales.shipments where delivered_date IS NULL. Then run the same query with = NULL and write down why the two results differ.
Find every unique combination of state and city in customers.addresses (default addresses only). Then list the distinct cities in stores.stores that have a store larger than 3,000 sq ft (square_ft).
In support.tickets, find the tickets with status = 'Closed' but no resolved_date, then the DISTINCT categories they belong to. Last: the Open tickets in 'Payment Failure' or 'Login Issue', written once with IN and once with OR in brackets: both must return the same rows.
59 sessions, from zero to job-ready. Today is session 7.