Reading rows: SELECT, WHERE, ORDER BY, LIMIT

Your first query

A query answers one question: which rows, and which of their columns, do I want? The shortest way to ask is SELECT * — every column — FROM one table.

-- Every column of every product. Run it with F9 or Ctrl/Cmd + Enter.
SELECT *
FROM products
LIMIT 20;

The * means “all columns,” and LIMIT 20 means “return at most twenty rows.” When you only need to inspect a table, avoid loading every row: products can contain thousands, all of which would need to be transferred and rendered.

Planche also caps result sets (Settings › Safety › Row limit). Still, writing the limit yourself is useful because the app-level cap protects the browser, not the database server doing the work.

Choosing columns

SELECT * is for exploring. Once you know what you are after, name the columns: the result is narrower, easier to read, and the database moves less data.

SELECT name, sku, price, stock_quantity
FROM products
LIMIT 20;

The order in the SELECT list becomes the order of the result columns; the table's own column order does not matter. Use AS to rename a result column.

Filtering rows with WHERE

SELECT chooses columns; WHERE chooses rows. It takes a condition and keeps only the rows the condition is true for.

SELECT name, price, stock_quantity
FROM products
WHERE price > 100
LIMIT 20;

The comparison operators are the ones you would guess: =, <> (or !=), <, <=, >, >=. Two of them behave differently from other languages and catch everyone once:

• Equality is a single =, not ==.
• Nothing equals NULL, not even NULL. WHERE weight_kg = NULL matches no rows at all, however many products have no weight recorded. The test you want is IS NULL / IS NOT NULL. The next lesson covers this properly.

Sorting with ORDER BY

Without an ORDER BY, row order is not defined. If the order matters, specify it; the order you happen to see can change when the data or an index changes.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 20;

DESC sorts high to low; ASC, the default, sorts low to high. ORDER BY price DESC with LIMIT 20 returns the 20 most expensive products: the database sorts first, then takes the first 20 rows.

In Planche, clicking a result-column header cycles through ascending, descending, and unsorted order. The table is sorted on the server, and the generated SQL is available to inspect.

Sorting by more than one column

When you list several sort keys, each one orders rows tied on the preceding key.

SELECT category_id, name, price
FROM products
ORDER BY category_id, price DESC
LIMIT 30;

This orders products by category and then, within each category, by price from high to low.

Expressions and aliases

The SELECT list can contain arithmetic, function calls, or any other expression that produces a value for each row. AS gives that result a name.

SELECT name,
       price,
       cost,
       ROUND(price - cost, 2) AS margin
FROM products
WHERE is_active = 1
ORDER BY margin DESC
LIMIT 15;

ROUND(price - cost, 2) AS margin computes a column that exists nowhere in the table, and the name margin is then usable in ORDER BY. It is not usable in WHERE, which trips people up regularly. The reason is the order the database evaluates the clauses in:

FROM  ->  WHERE  ->  SELECT  ->  ORDER BY  ->  LIMIT

By the time ORDER BY runs, SELECT has produced margin. WHERE runs earlier, so WHERE margin > 50 is an error. Repeat the expression or wrap the query in another query. The same order explains why WHERE filters before grouping while HAVING filters afterward.

What you can do now

These clauses cover many one-table questions: the 20 most expensive products, items out of stock, or the widest margins in an active catalogue. Questions spanning two tables, such as which customer placed an order, need joins.

Try changing numbers and column names in the examples, then run them again to see how each clause changes the result.