Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 40 ยท SQL Basics

SELECT, WHERE, ORDER BY and DISTINCT in MariaDB

Query MariaDB tables with SELECT, filter rows with WHERE and comparison operators, IN, BETWEEN and IS NULL, sort with ORDER BY, remove duplicates with DISTINCT and page results with LIMIT.

SELECT is the statement you will write most often. It reads rows from one or more tables and returns a result that is itself a table. This lesson builds up a query clause by clause: choosing columns, filtering with WHERE, sorting with ORDER BY, removing duplicates with DISTINCT and limiting rows with LIMIT. All examples run on the sample shop database.

Choosing columns

SQL
SELECT name, price FROM products;
SELECT * FROM products;                      -- every column; fine for exploring, avoid in applications
SELECT name AS product, price * 1.2 AS price_with_tax FROM products;

An alias (AS) renames a column in the result. Expressions are allowed: arithmetic, functions, string concatenation with CONCAT(). Aliases can be used in ORDER BY but, in MariaDB as in standard SQL, not in WHERE.

SQL Columns and expressions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Filtering with WHERE

WHERE keeps only rows for which the condition is true.

Operator Meaning Example
= equal status = 'shipped'
<> or != not equal country <> 'Canada'
<, <=, >, >= comparison price >= 100
<=> NULL-safe equal (MariaDB) a <=> b
BETWEEN a AND b inclusive range price BETWEEN 10 AND 50
IN (...) one of a list country IN ('France','Spain')
LIKE pattern (see next lessons) name LIKE 'Mech%'
IS NULL missing value signed_up IS NULL

Combine conditions with AND, OR and NOT. AND binds tighter than OR, so add parentheses whenever you mix them.

SQL WHERE with AND, BETWEEN and IN ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL OR needs parentheses ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Remove the parentheses and run it again: AND is applied first, so you get every Canadian customer plus the recent Mexican ones. That is the number one logic bug in hand-written WHERE clauses.

The IN operator

IN is shorthand for several OR tests and also accepts a subquery:

SQL
SELECT name FROM products WHERE category_id IN (SELECT id FROM categories WHERE name LIKE 'S%');

NOT IN has a trap: if the list or subquery contains a NULL, the whole condition is never true. Use NOT EXISTS for that case (see subqueries).

Sorting with ORDER BY

Without ORDER BY, MariaDB returns rows in no guaranteed order. Ask for the order you need:

SQL
SELECT name, price FROM products ORDER BY price DESC;
SELECT name, category_id, price FROM products ORDER BY category_id ASC, price DESC;

ASC is the default. You can sort by an alias, a column position (ORDER BY 2, discouraged) or an expression. Strings sort according to the column's collation, so with a _ci collation apple and Apple are equal. In MariaDB NULL sorts first in ascending order; to put it last use ORDER BY col IS NULL, col.

SQL Sort by two columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DISTINCT

DISTINCT removes duplicate result rows. It applies to the whole combination of selected columns:

SQL Distinct values ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Distinct pairs and COUNT(DISTINCT) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DISTINCT has a cost: the server sorts or hashes the result to find duplicates. If you only need to know whether a value exists, EXISTS is cheaper.

LIMIT and OFFSET

LIMIT n returns at most n rows. LIMIT n OFFSET m (or LIMIT m, n) skips m rows first, the basis of pagination:

SQL
SELECT id, name FROM products ORDER BY id LIMIT 10;            -- page 1
SELECT id, name FROM products ORDER BY id LIMIT 10 OFFSET 20;  -- page 3

Always pair LIMIT with ORDER BY, otherwise "which rows" is undefined. Large offsets get slow because the server still reads and discards the skipped rows. For deep paging use keyset pagination: WHERE id > :last_seen_id ORDER BY id LIMIT 10. MariaDB also accepts the standard OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY syntax.

SQL Top 3 most expensive, then the next 3 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Order of evaluation

You write SELECT first, but the server thinks in a different order, which explains why an alias works in ORDER BY and not in WHERE:

flowchart LR
  A["FROM"] --> B["WHERE"] --> C["GROUP BY"] --> D["HAVING"] --> E["SELECT"] --> F["DISTINCT"] --> G["ORDER BY"] --> H["LIMIT"]

Logical order: tables, row filter, groups, group filter, columns, distinct, sort, limit.

Common mistakes

  • Comparing with NULL using =. price = NULL is never true; write price IS NULL.
  • Quoting numbers or forgetting quotes around strings and dates. Dates are strings in 'YYYY-MM-DD' format.
  • Using double quotes for strings. By default MariaDB accepts them, but in ANSI_QUOTES mode they mean identifiers; use single quotes for text.
  • Selecting * and then relying on column order.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In WHERE a = 1 OR b = 2 AND c = 3, which part is evaluated first?

    AND has higher precedence than OR. Use parentheses to make the intent clear.
  2. 2. Which condition correctly finds rows with a missing value?

    NULL means unknown, so comparisons with = give NULL (not true). Only IS NULL works.
  3. 3. What does SELECT DISTINCT country, city return?

    DISTINCT applies to the entire selected row.
  4. 4. Why should LIMIT be used together with ORDER BY?

    Row order is not guaranteed without ORDER BY, so a limited result can change between runs.
  5. 5. Why can't you use a column alias in the WHERE clause?

    Logical order puts WHERE before SELECT, so the alias does not exist yet. ORDER BY comes after SELECT.

Summary

  • SELECT picks columns and expressions; aliases rename them.
  • WHERE filters rows with comparisons, BETWEEN, IN, LIKE and IS NULL; use parentheses with AND/OR.
  • ORDER BY sorts (ascending by default); DISTINCT removes duplicate rows; LIMIT/OFFSET page results.
  • Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

Next: LIKE and regular expressions