CertGrid CertGrid
Hands-on Lab·MySQL

MySQL SELECT, Joins and Aggregation

One query answering a real question - how many orders each customer has and what they spent - and the two choices in it that decide whether customers with no orders appear at all.

Schemas and Data Guide 9 of 45 Intermediate

Written against the versions above. `ONLY_FULL_GROUP_BY` is on by default from 8.0, so every non-aggregated column must appear in `GROUP BY`. Older servers allowed the sloppier form and returned an arbitrary row.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Create a second table and give it rows

    orders references customers through a foreign key. That relationship is the subject of the next guide; here it is just what makes a join meaningful.

    Three orders across two customers, which is deliberate: Ada gets two, Grace one, and the others none. A join that only ever sees matched rows teaches nothing.

    bash 
    sudo mysql appdb -e "CREATE TABLE orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, total DECIMAL(10,2) NOT NULL, placed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id))"sudo mysql appdb -e "INSERT INTO orders (customer_id, total) VALUES (1, 40.00),(1, 15.50),(2, 99.99)"

    Expected resultBoth statements silent, which is success.

    Success conditionYou have two related tables with an uneven distribution of rows.

  2. Answer a question that spans both tables

    Read the query in the order the server does, not left to right.

    LEFT JOIN keeps every customer whether or not an order matches. A plain JOIN would silently drop Katsuko and the Unicode row - and the result would look perfectly reasonable, which is what makes that mistake expensive.

    COUNT(o.id) counts order ids, not rows. For a customer with no orders the left join produces one row of NULLs, and COUNT of a NULL column is 0 - whereas COUNT(*) would count that placeholder row and report 1.

    COALESCE(SUM(o.total), 0) turns the NULL sum into 0.00. SUM over no rows is NULL, not zero, and NULL formats badly and breaks arithmetic downstream.

    GROUP BY c.id, c.name includes both because ONLY_FULL_GROUP_BY demands every non-aggregated column - and grouping by id rather than name alone is what keeps two customers with the same name apart.

    bash Example session 
    sudo mysql --table appdb -e "SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total),0) AS spent FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY spent DESC"+---------------------+--------+-------+| name                | orders | spent |+---------------------+--------+-------+| Grace Hopper        |      1 | 99.99 || Ada Lovelace        |      2 | 55.50 || Katsuko Saruhashi   |      0 |  0.00 || Zoë Washburne 🚀      |      0 |  0.00 |+---------------------+--------+-------+

    Expected resultFour rows: Grace 1 order / 99.99, Ada 2 / 55.50, and the two order-less customers at 0 orders and 0.00.

    Success conditionYou can write a join that keeps the rows with nothing to match.

  3. See what an inner join would have cost you

    The same relationship with a plain JOIN, and a count of the base table beside it.

    Three rows returned. customers holds four. Ada appears twice because she has two orders, so those three rows represent only two customers - the other two vanished silently.

    Nothing warned you. The result is well-formed, plausible and wrong, and if it were feeding a report of customer activity you would under-count by half. This is the single most common reporting bug in SQL, and the only defence is checking the row count against the table you meant to enumerate.

    bash Example session 
    sudo mysql --table appdb -e "SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id"+--------------+-------+| name         | total |+--------------+-------+| Ada Lovelace | 40.00 || Ada Lovelace | 15.50 || Grace Hopper | 99.99 |+--------------+-------+sudo mysql --table appdb -e "SELECT COUNT(*) AS customers FROM customers"+-----------+| customers |+-----------+|         4 |+-----------+

    Expected resultThree joined rows covering two distinct customers, against a table of four.

    Success conditionYou can spot rows a join has dropped by counting rather than by reading.

  4. Filter, order and limit

    The clauses that turn a table dump into an answer, in the order MySQL applies them.

    LIKE '%a%' matches anywhere in the string - and a leading % means no index can help, which is fine on four rows and matters at four million. BETWEEN is inclusive at both ends, which is worth knowing before you use it on dates. ORDER BY runs after filtering, and LIMIT after ordering - so this is the top three by credit among the matches, not the first three found and then sorted.

    bash Example session 
    sudo mysql --table appdb -e "SELECT id, name, credit FROM customers WHERE name LIKE '%a%' AND credit BETWEEN 0 AND 100 ORDER BY credit DESC LIMIT 3"+----+---------------------+--------+| id | name                | credit |+----+---------------------+--------+|  3 | Katsuko Saruhashi   |  75.25 ||  2 | Grace Hopper        |  10.00 ||  5 | Zoë Washburne 🚀      |   0.00 |+----+---------------------+--------+

    Expected resultUp to three matching customers, highest credit first.

    Success conditionYou can narrow, sort and cap a result deliberately.

  5. Ask a question with another question

    "Customers who have never ordered" - the inverse of the join, expressed directly. The inner SELECT produces the set of customer ids that appear in orders, and NOT IN excludes them.

    One caution worth carrying: NOT IN against a subquery that can return NULL returns no rows at all, because a comparison with NULL is unknown rather than false. customer_id is NOT NULL here so it is safe - but on a nullable column, NOT EXISTS is the form that behaves the way you expect.

    bash Example session 
    sudo mysql --table appdb -e "SELECT name, email FROM customers WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders)"+---------------------+---------------------+| name                | email               |+---------------------+---------------------+| Katsuko Saruhashi   | katsuko@example.com || Zoë Washburne 🚀      | zoe@example.com     |+---------------------+---------------------+

    Expected resultThe customers with no matching orders.

    Success conditionYou can express an absence, and know where NOT IN goes wrong.

Troubleshooting

Official sources

← All MySQL guides