DISTINCT and ALIAS Clauses

Last Updated: June 12, 2026 • 12 min read

1. Introduction

DISTINCT removes duplicate rows from query results, while aliases provide readable names for columns and tables. Both are essential for writing clear, maintainable SQL queries.

2. Why It Matters

DISTINCT and aliasing are tested in SQL interviews to evaluate whether candidates understand result set deduplication, query readability, and the performance cost of DISTINCT operations. In production, unnecessary DISTINCT can slow queries, while missing DISTINCT can return duplicate data to the client.

3. Real-World Analogy

DISTINCT is like removing duplicate entries from a mailing list — you want each address only once. Aliases are like nicknames — instead of writing "Mr. Jonathan Christopher Smith III" every time, you call him "Jon" for brevity. In SQL, aliasing a long table name as a single letter makes queries much more readable.

4. How It Works

DISTINCT: Eliminates duplicate rows from the result. Applies to ALL selected columns combined. SELECT DISTINCT department FROM employees returns unique departments.
COUNT(DISTINCT col): Counts unique values. SELECT COUNT(DISTINCT user_id) FROM orders counts unique customers.
Column alias: SELECT first_name AS name — renames output column.
Table alias: FROM users AS u — short reference name for JOINs.
DISTINCT ON (PostgreSQL): Returns the first row per group. SELECT DISTINCT ON (department) * FROM employees ORDER BY department, salary DESC — highest-paid per department.

5. Internal Architecture

DISTINCT is expensive because the database must sort all rows (or build a hash table) to identify duplicates — O(n log n). If a JOIN produces duplicates, the root cause is usually a missing or incorrect join condition. Fix the JOIN rather than masking it with DISTINCT.

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Common Pitfall

Using DISTINCT as a fix for bad JOINs that produce duplicates. If a JOIN produces duplicate rows, the root cause is usually a missing join condition. Fix the JOIN — DISTINCT hides bugs and hurts performance.

Interview Insight

When asked "DISTINCT vs GROUP BY?", explain that both deduplicate, but GROUP BY allows aggregates per group and can be optimized better by the query planner using indexes.

Using alias in WHERE: WHERE is evaluated before SELECT, so aliases don't exist yet. Repeat the expression or use a subquery.
Forgetting table alias for self-joins: When joining a table to itself, aliases are mandatory — the database needs to know which instance you're referencing.

9. Quick Quiz

Q1: Can you use a column alias in the WHERE clause?

No. WHERE is evaluated before SELECT, so the alias doesn't exist yet. You can use aliases in ORDER BY and HAVING.

Q2: What does SELECT DISTINCT department, role return?

Unique department-role combinations. Two rows with the same department but different roles are both kept. DISTINCT applies to all selected columns together.

10. Scenario-Based Challenge

Scenario

Your API returns a list of "recent buyers" but shows the same user multiple times because they placed several orders. How would you fix this? Should you use DISTINCT, GROUP BY, or a subquery? What are the performance trade-offs of each?

11. Debugging Exercise

This query throws an error:

Fix: WHERE can't use aliases. Use the original expression: WHERE u.first_name || ' ' || u.last_name LIKE 'John%' or filter by first_name: WHERE u.first_name LIKE 'John%'.

12. Interview Questions

Q: When is DISTINCT unnecessary?

When querying a table with a primary key, SELECT DISTINCT id, name is pointless — ids are already unique. DISTINCT is only needed when you're selecting a subset of columns that might have duplicates.

Q: Why are table aliases mandatory for self-joins?

When a table joins to itself, the database needs to distinguish which instance each column reference belongs to. Without aliases, "employees.salary" is ambiguous — which employees?

Q: What's DISTINCT ON in PostgreSQL?

DISTINCT ON (column) returns the first row for each unique value of the specified column. Combined with ORDER BY, it's useful for "latest record per group" queries without needing window functions.

13. Production Considerations

Avoid unnecessary DISTINCT: If your JOIN conditions are correct, you won't get duplicates. Adding DISTINCT to mask bad JOINs hides bugs.
Index for GROUP BY: If you're using GROUP BY for deduplication, a matching index can make it dramatically faster than DISTINCT.
Derived table aliases: Subqueries in FROM always require an alias: FROM (SELECT ...) AS sub. Forgetting this is a common syntax error.
API response naming: Use column aliases to control API response field names: SELECT created_at AS "signupDate" for camelCase JSON responses.