SELECT, WHERE, ORDER BY, and LIMIT: Your First Real Queries

Every analysis starts with the same four moves. You pick columns, filter rows, sort the result, and cap how much comes back. Those moves map to SELECT, WHERE, ORDER BY, and LIMIT. Together they cover most of what an analyst types in a first week with a relational database. They also decide how much data a warehouse scans, how fast a notebook loads, and whether your top-10 list is reproducible.

Quick Takeaways

• SELECT chooses columns and computes expressions. Name your columns instead of using SELECT *.
• WHERE filters rows before anything else is calculated. Use IS NULL, never = NULL.
• ORDER BY sorts the result. Add a tiebreaker column so the order is deterministic.
• LIMIT caps output rows. Without ORDER BY, it returns an arbitrary subset.

The Sample Dataset

Every example below runs against one small orders table. The script uses Python’s built-in sqlite3 module and pandas, so you need no database server. Copy it, run it, and the outputs match the ones shown.

import sqlite3
import pandas as pd

# In-memory database: nothing is written to disk
conn = sqlite3.connect(":memory:")

conn.executescript("""
CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    country       TEXT    NOT NULL,
    category      TEXT    NOT NULL,
    amount        REAL    NOT NULL,
    status        TEXT    NOT NULL,
    order_date    TEXT    NOT NULL,   -- ISO 8601 strings sort correctly
    discount_code TEXT                -- nullable on purpose
);

INSERT INTO orders VALUES
(1,101,'US','Electronics',249.99,'shipped','2026-01-05',NULL),
(2,102,'DE','Books',18.50,'shipped','2026-01-06','WELCOME10'),
(3,103,'US','Electronics',899.00,'returned','2026-01-09',NULL),
(4,104,'UK','Home',64.20,'shipped','2026-01-12','SPRING5'),
(5,101,'US','Books',22.00,'shipped','2026-01-15',NULL),
(6,105,'FR','Home',120.00,'pending','2026-01-18',NULL),
(7,106,'US','Electronics',459.50,'shipped','2026-01-20','VIP15'),
(8,107,'DE','Electronics',129.99,'cancelled','2026-01-22',NULL),
(9,102,'DE','Home',310.00,'shipped','2026-01-25',NULL),
(10,108,'UK','Books',12.99,'shipped','2026-01-28',NULL);
""")

def run(sql, params=()):
    """Execute a query and return a DataFrame. Params are bound, never concatenated."""
    return pd.read_sql_query(sql, conn, params=params)

The helper run() uses params for bound values. That habit protects you from SQL injection once queries take user input.

SELECT: Choose What Comes Back

SELECT defines the output columns. It can return raw columns, calculated expressions, or both.

# Named columns plus a computed column with an alias
print(run("""
    SELECT order_id,
           amount,
           ROUND(amount * 0.9, 2) AS net      -- alias names the expression
    FROM orders
    WHERE status = 'shipped'
    ORDER BY net DESC
    LIMIT 3;
"""))

   order_id  amount     net
0         7  459.50  413.55
1         9  310.00  279.00
2         1  249.99  224.99

Why SELECT * Costs You

SELECT * is fine for a quick peek. In production it hurts in three ways:

• Columnar warehouses such as BigQuery bill by the columns scanned, so extra columns cost money.
• Schema changes add or reorder columns and silently break downstream code.
• Network and memory overhead grows with every column you do not need.

DISTINCT Removes Duplicate Rows

# Unique countries, sorted alphabetically
print(run("SELECT DISTINCT country FROM orders ORDER BY country;"))

  country
0      DE
1      FR
2      UK
3      US

DISTINCT applies to the whole selected row, not just the first column. SELECT DISTINCT country, category returns unique country-category pairs.

WHERE: Filter Rows Early

WHERE keeps only rows for which the condition evaluates to true. Rows evaluating to false or NULL (unknown) are dropped. It runs before SELECT, so you cannot reference a column alias inside it.

Comparison and Logical Operators

# Combine IN and BETWEEN
print(run("""
    SELECT order_id, category, amount
    FROM orders
    WHERE category IN ('Books', 'Home')
      AND amount BETWEEN 15 AND 130
    ORDER BY amount;
"""))

   order_id category  amount
0         2    Books    18.5
1         5    Books    22.0
2         4     Home    64.2
3         6     Home   120.0

The NULL Trap

discount_code = NULL never returns rows. Any comparison with NULL yields unknown, and WHERE discards unknown. Use IS NULL or IS NOT NULL.

# Orders placed without a discount code
print(run("""
    SELECT order_id, discount_code
    FROM orders
    WHERE discount_code IS NULL
    ORDER BY order_id
    LIMIT 3;
"""))

   order_id discount_code
0         1          None
1         3          None
2         5          None

Parenthesize Mixed AND/OR

WHERE country = 'US' OR country = 'UK' AND amount > 100 is read as US OR (UK AND amount > 100). State your intent with parentheses every time.

ORDER BY: Control Row Order

A table has no inherent order. Without ORDER BY, the database returns rows in whatever sequence its engine finds convenient, and that can change between runs.

# Sort by country A-Z, then by amount high-to-low inside each country
print(run("""
    SELECT order_id, country, amount
    FROM orders
    ORDER BY country ASC, amount DESC
    LIMIT 4;
"""))

   order_id country  amount
0         9      DE  310.00
1         8      DE  129.99
2         2      DE   18.50
3         6      FR  120.00

Key rules:

• ASC is the default. Add DESC for largest-first.
• Each column carries its own direction.
• ORDER BY runs after SELECT, so it can use aliases like net.
• You can sort by position (ORDER BY 2), but named columns are safer when the query changes.

Where NULLs Land Depends on the Engine

If your sort touches a nullable column, test on your target engine before shipping the query.

LIMIT: Cap the Result

LIMIT n returns at most n rows. Pair it with ORDER BY to get a meaningful “top N”. Add OFFSET to skip rows.

# Rows 3-4 of shipped orders ranked by amount (skip 2, take 2)
print(run("""
    SELECT order_id, amount
    FROM orders
    WHERE status = 'shipped'
    ORDER BY amount DESC
    LIMIT 2 OFFSET 2;
"""))

   order_id  amount
0         1  249.99
1         4   64.20

Row-Limiting Syntax by Dialect

LIMIT Is Not a Cost Control Everywhere

In BigQuery, LIMIT trims returned rows but not the bytes scanned on an unpartitioned, unclustered table. Filter with WHERE on a partition column to cut scan cost. Large OFFSET values also get slower, since the engine still reads and discards the skipped rows. For deep pagination, filter on the last seen key instead (WHERE order_id > 4000 ORDER BY order_id LIMIT 50).

Add a Tiebreaker for Deterministic Results

If two rows tie on the sort column, which one lands at position 10 is undefined. Append a unique column:

-- Stable top-10: ties on amount resolve by order_id
SELECT order_id, amount
FROM orders
ORDER BY amount DESC, order_id ASC
LIMIT 10;

How the Clauses Execute

You write SELECT first. The engine evaluates it fourth. This explains most beginner errors.

So WHERE net > 300 fails when net is an alias defined in SELECT. Repeat the expression, or wrap the query in a subquery or CTE.

Binding Parameters in Python

Never build SQL with f-strings. Bind values with placeholders.

# sqlite3 uses ? placeholders; psycopg2 uses %s; SQLAlchemy uses :name
print(run(
    """
    SELECT order_id, amount
    FROM orders
    WHERE country = ? AND amount >= ?
    ORDER BY amount DESC
    LIMIT ?;
    """,
    ("DE", 100, 2),
))

   order_id  amount
0         9  310.00
1         8  129.99

Parameter binding also lets the database reuse query plans and blocks injection attacks.

Practical Examples and Real-World Use Cases

Use Case 1: Sampling a New Table Safely

You land on an unfamiliar 400-million-row table. Do not run SELECT *.

SELECT customer_id, order_date, amount, status
FROM orders
WHERE order_date >= '2026-01-01'   -- partition filter first
LIMIT 100;

You get a fast look at the columns that matter, and the date filter keeps scan costs low. Because there is no ORDER BY, the 100 rows are arbitrary, which is fine for inspection.

Use Case 2: Building a “Top Spenders” List for Marketing

SELECT customer_id, amount, order_date
FROM orders
WHERE status = 'shipped'            -- exclude returns and cancellations
ORDER BY amount DESC, order_id ASC  -- deterministic ranking
LIMIT 10;

The status filter keeps refunded revenue out of the ranking, and the tiebreaker makes the list reproducible for a weekly report.

Use Case 3: Pulling Candidates for a Churn Model Review

Data scientists often inspect edge cases before training. Pull the most recent high-value orders that ended badly:

SELECT order_id, customer_id, category, amount, status
FROM orders
WHERE status IN ('returned', 'cancelled')
  AND amount > 100
ORDER BY order_date DESC
LIMIT 25;

Review these rows for patterns such as a category or country that drives returns. That feeds feature engineering later.

Use Case 4: Finding Missing Data in Sensor Feeds

For an IoT table readings(sensor_id, ts, temperature_c), check gaps recently:

SELECT sensor_id, ts
FROM readings
WHERE temperature_c IS NULL
  AND ts >= '2026-11-01'
ORDER BY ts DESC
LIMIT 50;

This surfaces the latest dropouts so you can decide between imputation and dropping rows.

Common Errors and Fixes

Frequently Asked Questions

What is the correct order of SELECT, WHERE, ORDER BY, and LIMIT in a query?

Write them in this sequence: SELECT, FROM, WHERE, ORDER BY, LIMIT. The engine executes them in a different logical order: FROM, WHERE, SELECT, ORDER BY, LIMIT.

Can I use LIMIT without ORDER BY?

Yes, but the rows returned are arbitrary and may differ between runs. That suits quick data inspection. For rankings, reports, or tests, always add ORDER BY with a tiebreaker column.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY and aggregation. Use WHERE country = 'US' to restrict rows, and HAVING SUM(amount) > 1000 to restrict aggregated groups.

Why does WHERE column = NULL return nothing?

SQL uses three-valued logic. Comparing anything to NULL returns unknown, and WHERE only keeps rows that evaluate to true. Use column IS NULL instead.