SQL SELECT: Querying Data with WHERE, ORDER BY, and LIMIT

Introduction

The SELECT statement is the most fundamental and frequently used command in SQL — the Structured Query Language used by all relational Database Management Systems. Every report, every API response, every dashboard reading from a relational database uses SELECT to retrieve data. Mastering SELECT with filtering, sorting, and limiting is the first practical SQL skill every developer needs.

Explanation

A basic SELECT query has three parts: the SELECT clause (which columns to return), the FROM clause (which table to query), and the optional WHERE clause (which rows to filter). The result of a SELECT query is always a new relation — a virtual table containing the result set.

The WHERE clause uses comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), range checks (BETWEEN), list checks (IN), pattern matching (LIKE), and NULL checks (IS NULL, IS NOT NULL).

Real World Example

An e-commerce analytics dashboard runs hundreds of SELECT queries per minute — finding top-selling products, filtering orders by date range, finding customers who haven't purchased recently, and calculating revenue by region. Every one of these uses SELECT with various combinations of WHERE, ORDER BY, GROUP BY, and LIMIT to extract exactly the data needed.

Technical Breakdown

SQL query execution order differs from writing order. The database executes clauses in this order: FROM (identify tables), WHERE (filter rows), GROUP BY (aggregate), HAVING (filter groups), SELECT (choose columns), ORDER BY (sort), LIMIT (restrict output). Understanding this order explains why you cannot reference a SELECT alias in a WHERE clause — WHERE executes before SELECT.

SQL query execution flowchart

Example

-- Basic SELECT with column selection
SELECT product_id, name, price
FROM products;

-- Filtering with WHERE
SELECT name, email
FROM customers
WHERE city = 'Mumbai' AND registration_date >= '2024-01-01';

-- Range filter
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 500 AND 2000;

-- Pattern matching
SELECT name, email
FROM customers
WHERE name LIKE 'A%';  -- names starting with A

-- IN list
SELECT order_id, status, total_amount
FROM orders
WHERE status IN ('pending', 'processing');

-- Sorting and limiting
SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;  -- Top 10 most expensive products

-- DISTINCT removes duplicate values
SELECT DISTINCT category
FROM products;

Common Mistakes

• Using = to compare NULL values — NULL = NULL returns UNKNOWN, not TRUE. Always use IS NULL or IS NOT NULL.

• Forgetting that LIKE is case-sensitive in some databases — use ILIKE in PostgreSQL for case-insensitive matching.

• Using SELECT * in production code — it retrieves all columns unnecessarily, wastes bandwidth, and breaks when the table schema changes.

• Assuming ORDER BY preserves insertion order without specifying a column — SQL tables have no inherent row order.

Interview Tips

Practice writing SELECT queries that combine multiple WHERE conditions, use BETWEEN for ranges, and use LIKE with wildcards. A common tricky question is "Why can you not use a column alias in the WHERE clause?" — the answer is SQL's logical processing order: WHERE evaluates before SELECT, so the alias does not exist yet. You can use aliases in ORDER BY and HAVING because those execute after SELECT.