Basic SQL Queries
SELECT, FROM, and WHERE Clauses
The foundation of data query operations.
Interview: SELECT, FROM, and WHERE are tested in every SQL interview round. Interviewers evaluate your ability to write correct filters, understand NULL handling, and avoid common WHERE clause mistakes.
1. Introduction
The SELECT, FROM, and WHERE clauses form the foundation of every SQL query. Mastering these three clauses enables you to retrieve exactly the data you need from any relational database.
2. Why It Matters
SELECT, FROM, and WHERE are tested in every SQL interview round. Every application query — from loading a user profile to generating reports — starts with these three clauses. Writing imprecise queries leads to performance problems, security issues (returning too much data), and incorrect results.
3. Real-World Analogy
Writing a SQL query is like ordering at a restaurant. FROM = which menu section (table), WHERE = filtering out dishes you don't want (no spicy, vegetarian only), SELECT = which specific items from the filtered list you'll actually order. You wouldn't say "give me everything on the menu" — you specify exactly what you want.
4. How It Works
The logical query processing order is critical — SQL evaluates clauses in a different order than you write them:
- 1. FROM: Identifies source table(s). Evaluated first.
- 2. WHERE: Filters individual rows before any grouping. Supports comparison (=, <>, <, >), logical (AND, OR, NOT), range (BETWEEN), set (IN), pattern (LIKE), and NULL (IS NULL) operators.
- 5. SELECT: Projects columns and expressions. Evaluated after WHERE, which is why you can't use SELECT aliases in WHERE.
Full processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET
5. Internal Architecture
When PostgreSQL executes a SELECT...WHERE query, the query planner chooses between a sequential scan (reads every row) or an index scan (jumps directly to matching rows via an index). For selective WHERE conditions on indexed columns, index scans are dramatically faster.
6. Visual Explanation
7. Practical Example
8. Common Mistakes
Common Pitfall
Using SELECT * in production code. It returns columns you may not need, wastes bandwidth, breaks when schema changes, and prevents covering index optimization. Always specify exact columns.
Interview Insight
The most common interview trap is NULL handling. WHERE column = NULL returns no rows. Always use IS NULL. Remember: NULL = NULL evaluates to UNKNOWN, not TRUE.
- Operator precedence: AND binds tighter than OR. Use explicit parentheses:
WHERE (status = 'active' OR status = 'pending') AND country = 'US'. - Date range errors: Use
>= '2026-01-01' AND < '2027-01-01'instead of BETWEEN to include the entire last day.
9. Quick Quiz
Q1: Why can't you use a SELECT alias in the WHERE clause?
Because WHERE is evaluated before SELECT in the logical processing order. The alias doesn't exist yet when WHERE runs.
Q2: What does WHERE column IN (1, 2, NULL) return?
Rows where column = 1 or column = 2. The NULL in the list is ignored because column = NULL evaluates to UNKNOWN.
10. Scenario-Based Challenge
Scenario
You need to find all orders placed in the last 30 days by customers in either the US or Canada, with a total above $100, excluding orders with status 'cancelled'. Write the query using appropriate WHERE operators.
11. Debugging Exercise
This query returns zero rows even though matching data exists:
Fix: Replace email != NULL with email IS NOT NULL. The != NULL comparison always evaluates to UNKNOWN, filtering out all rows.
12. Interview Questions
Q: What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. You can't use aggregate functions (COUNT, SUM) in WHERE, but you can in HAVING.
Q: Why is SELECT * bad in production?
It returns unnecessary columns (wasted bandwidth), breaks when schema changes (new columns), prevents covering index optimization, and can expose sensitive data to the client.
Q: How does LIKE differ from ILIKE?
LIKE is case-sensitive, ILIKE is case-insensitive (PostgreSQL only). LIKE 'John%' matches 'Johnny' but not 'johnny'. ILIKE 'John%' matches both.
13. Production Considerations
- Index your WHERE columns: Columns used frequently in WHERE conditions should be indexed. Without indexes, PostgreSQL does a sequential scan of every row.
- Prepared statements: Use parameterized queries (WHERE id = $1) instead of string concatenation to prevent SQL injection.
- EXPLAIN ANALYZE: Always check query plans before deploying. A query that works fast on 100 rows may be slow on 1 million rows.
- Partial indexes: For queries that always filter on the same condition (e.g., WHERE status = 'active'), create a partial index: CREATE INDEX idx ON users(id) WHERE status = 'active';
Use Cases
Application queries: Writing precise SELECT lists instead of SELECT * ensures your API returns only the data the client needs, reducing response size and improving performance
Data exploration: Using WHERE with various operators (BETWEEN, IN, LIKE, IS NULL) to filter and understand datasets during analysis
Production debugging: Querying specific rows with WHERE conditions to investigate data issues, verify migrations, or check application state
Common Mistakes
Using = NULL instead of IS NULL — NULL comparisons always evaluate to UNKNOWN, so WHERE column = NULL returns zero rows. Always use IS NULL or IS NOT NULL
Using SELECT * in production — returns unnecessary columns, wastes bandwidth, breaks when schema changes, and prevents index-only scans
Forgetting operator precedence — AND binds tighter than OR. Write WHERE (status = 'active' OR status = 'pending') AND country = 'US' with explicit parentheses to avoid logic errors