Mysql SELECT DISTINCT multiple columns

Individual column distinctness

What if you want distinct values from each column, not just unique combinations? You can use subqueries for that, like an SQL ninja.

-- Now you see me, now you still see me (but just once).
(SELECT DISTINCT column1 FROM table)
UNION
(SELECT DISTINCT column2 FROM table);


This yields unique values from column1 and column2 separately, combined into one column.

When to use GROUP BY

GROUP BY results in all combinations of column values. But wait, there is more! It also allows you to aggregate other columns. If unique combos are your end game, stick to SELECT DISTINCT.

-- A group huddle, but for columns.
SELECT column1, column2, COUNT(*) 
FROM table 
GROUP BY column1, column2;


This will result in a counts of column1 and column2 value combinations.

Table design for efficiency

Oh, the joys of databases. Good tiding awaits if you trim your tables right. An efficient table structure can increase query performance. Denormalizing tables or using indexes can make your DISTINCT queries run faster—like wind, my friend.

Advanced techniques for distinctness

Use GROUP_CONCAT

If you need a list of unique column values, use GROUP_CONCAT with DISTINCT in a subquery.

-- Getting all your unique ducks in a row.
SELECT 
    GROUP_CONCAT(DISTINCT column1 ORDER BY column1) AS unique_col1,
    GROUP_CONCAT(DISTINCT column2 ORDER BY column2) AS unique_col2
FROM table;


The result? Each column contains a comma-separated list of unique values.

Avoid CONCAT for distinctness

Easy there, cowboy! CONCAT() combines values into one string. Result? Non-unique combinations. Always remember, don't concatenate for distinctness.

Use UNION for distinctness

UNION provides the fruits of distinct queries. Use UNION to get unique values across separate columns.

-- Merging all your unique skills into a superpower.
(SELECT DISTINCT column1 FROM table)
UNION
(SELECT DISTINCT column2 FROM table);


This avoids the repetition of the same value in multiple columns.

Aliases for columns

Don't leave your users guessing. Ensure to name your columns neatly with aliases for easier readability:

-- No more guesswork.
SELECT DISTINCT column1 AS unique_column1, column2 AS unique_column2 FROM table;


Omit GROUP BY when necessary

GROUP BY is meant for creating unique combinations for aggregation. Don't use it if you seek unique values per column.