DISTINCT, UNION & GROUP_CONCAT

Three tools for shaping result sets

So far every query returned rows exactly as they were stored. This lesson adds three tools that reshape the result set itself: DISTINCT drops repeated rows, UNION stacks one query’s rows underneath another’s, and GROUP_CONCAT squeezes many values from a group into a single comma-separated cell. They turn raw rows into the tidy lists and combined reports real apps display.

We will use this orders table for the examples:

DISTINCT — one row per unique value

A plain SELECT city FROM orders would list Mumbai three times and Delhi twice — once per order. When you only want the list of distinct cities, you add DISTINCT right after SELECT. It keeps just one copy of each unique value (or each unique combination, if you list several columns).

SELECT DISTINCT city FROM orders;

DISTINCT looks at the city value of every row and throws away repeats, so you get a clean list of the unique cities rather than one entry per order. If you wrote SELECT DISTINCT city, user_id, MySQL would keep one row per unique pair of city and user instead.

UNION — stack two result sets vertically

A JOIN glues tables side by side (more columns). UNION does the opposite — it stacks two query results on top of each other (more rows). The only rule is that both SELECT statements must return the same number of columns, in a compatible order. It is perfect for combining lists from different tables into one column, such as merging a list of customers and a list of suppliers into a single contact list.

Imagine a separate suppliers table with a name column, and you want one combined list of every customer name and every supplier name:

SELECT name FROM users
UNION
SELECT name FROM suppliers;

Each SELECT produces a list of names; UNION places the second list under the first and returns them as one result. By default UNION also removes duplicates — if a name appears in both tables, it shows once. If you want to keep every row including duplicates (which is faster, because MySQL skips the de-duplication step), use UNION ALL instead.

GROUP_CONCAT — many values into one cell

With GROUP BY you get one row per group, but sometimes you want to see the actual values in each group, not just a count. GROUP_CONCAT is an aggregate function that joins all the values in a group into a single comma-separated string — ideal for “list the order ids each customer placed” on one line.

SELECT user_id, GROUP_CONCAT(id ORDER BY id) AS order_ids
FROM orders
GROUP BY user_id;

Reading it: GROUP BY user_id forms one group per user as usual. Inside each group, GROUP_CONCAT(id ORDER BY id) takes every order id in that group and joins them into one string, sorted by id. So instead of three separate rows for user 1’s orders, you get a single cell reading 101,103.