DISTINCT

Tables often repeat values - many employees share one department. DISTINCT collapses duplicates so each value appears once.

What DISTINCT does

• DISTINCT goes right after SELECT: SELECT DISTINCT WORKDEPT FROM EMP.
• It removes duplicate rows from the result - each distinct value appears once.
• Without DISTINCT (or with ALL, the default), duplicates stay.
• SELECT DISTINCT applies to the whole row, not one column in isolation.

DISTINCT over multiple columns

• SELECT DISTINCT WORKDEPT, JOB returns each unique department/job pair.
• Two rows are duplicates only if all listed columns match.
• Adding a unique column like EMPNO makes DISTINCT useless - every row is already unique.
• NULLs are treated as equal to each other for DISTINCT purposes.

DISTINCT vs GROUP BY

• For simple de-duplication, DISTINCT is shorter and clearer.
• GROUP BY is needed when you also aggregate (COUNT, SUM, AVG).
• Both may sort internally, so neither guarantees row order - add ORDER BY if order matters.
• DISTINCT adds a sort/unique step, so avoid it on huge results unless you need it.

Example