DISTINCT and Aliases

DISTINCT removes duplicate selected rows. Double quotes are standard for identifiers and work in PostgreSQL; default MySQL commonly uses backticks for quoted identifiers unless ANSI_QUOTES is enabled.

1. Introduction

Once you know how to select, filter, sort, and limit data, the next step is making the result cleaner and easier to read.

In real projects, raw SQL output is often:

• repetitive
• noisy
• hard to read
• too technical for business users

Two SQL tools help solve this:

• DISTINCT
• AS

These make your query results cleaner, more readable, and more professional.

2. What is DISTINCT?

DISTINCT is used to return only unique values.

It removes duplicate values from the result set.

This is useful when the same value appears many times and you only want to see it once.

Simple Definition

DISTINCT means:

Show each value only once.

3. What is AS?

AS is used to give a temporary name (alias) to a column or table.

This does not change the real column name in the database.

It only changes how the column appears in the result.

This makes results easier to read and more business-friendly.

Simple Definition

AS means:

Show this column with a better temporary name.

4. Basic Example

Find all unique regions from the countries table and rename the column.

SELECT DISTINCT region AS unique_region
FROM countries;

Output

unique_region

Asia

South America

Oceania

North America

Explanation

• DISTINCT removes duplicate regions
• AS unique_region renames the output column

5. Deep Dive — Cleaning and Labeling Data

In real-world systems, raw data is often repetitive.

Example:

A sales table may have 10,000 rows, but only 20 unique cities.

If you only want to know which cities had sales, seeing all 10,000 rows is unnecessary.

This is where DISTINCT helps.

It compresses repeated values and shows only unique ones.

AS helps with readability.

Databases often use technical column names like:

• prod_id
• qty_on_hand
• cust_amt

These names are fine for systems, but not ideal for reports.

AS lets you rename them into something more readable like:

• Product_ID
• Quantity
• Customer_Amount

This makes SQL output easier for humans to understand.

6. Real-World Example — products Table

We will use the products table.

7. Case 1 — DISTINCT (Unique Values)

Question

What unique product categories do we currently have?

Query

SELECT DISTINCT category
FROM products;

Explanation

Without DISTINCT, SQL would show:

• Accessories
• Accessories
• Electronics
• Furniture
• Cables

With DISTINCT, repeated values are removed.

Each category appears only once.

Output

category

Accessories

Electronics

Furniture

Cables

Why This Matters

This is useful when you want:

• unique categories
• unique cities
• unique countries
• unique departments
• unique statuses

8. Case 2 — AS (Renaming Columns)

Question

Show product names and prices, but rename them for a business report.

Query

SELECT product_name AS Item, price AS Price_USD
FROM products;

Explanation

The real column names are:

• product_name
• price

But for reporting, we rename them to:

• Item
• Price_USD

This makes the result cleaner and more readable.

Output

Why This Matters

Aliases are heavily used in:

• reports
• dashboards
• exports
• analytics
• business queries

9. Case 3 — Naming Calculated Columns

This is one of the most important uses of AS.

When SQL calculates a value, the new column has no meaningful default name.

Without an alias, the result becomes hard to read.

Always name calculated columns.

Query

SELECT product_name, price * stock_quantity AS Inventory_Value
FROM products;

Explanation

SQL calculates:

price * stock_quantity

This creates a new derived column.

AS Inventory_Value gives that column a clean name.

Output

Why This Matters

This is extremely common in:

• inventory reports
• revenue reports
• financial metrics
• dashboards
• analytics

10. DISTINCT on Multiple Columns

This is a very important concept.

When DISTINCT is used on multiple columns, SQL checks uniqueness based on the full combination.

It does not check columns individually.

It checks the entire row combination.

Example

SELECT DISTINCT category, price
FROM products;

This means SQL returns only unique (category, price) pairs.

A row is removed only if both values are duplicated together.

Important Rule

With multiple columns:

DISTINCT checks unique combinations, not unique single values.

11. Common Mistakes to Avoid

1. Wrong Placement of DISTINCT

Incorrect:

SELECT category DISTINCT
FROM products;

Correct:

SELECT DISTINCT category
FROM products;

DISTINCT must come immediately after SELECT.

2. Using Alias in WHERE

Incorrect:

SELECT price AS cost
FROM products
WHERE cost > 100;

This fails.

Why?

Because SQL processes:

1. FROM
2. WHERE
3. SELECT

At the time WHERE runs, cost does not exist yet.

Correct Version

SELECT price AS cost
FROM products
WHERE price > 100;

3. Forgetting DISTINCT Behavior on Multiple Columns

Many beginners think:

SELECT DISTINCT col1, col2

means unique col1.

It does not.

It means unique (col1, col2) pairs.

4. Using Spaces in Aliases

If an alias contains spaces, it must be quoted.

Example:

SELECT product_name AS "Product Name"
FROM products;

This works, but using underscores is safer.

Preferred:

SELECT product_name AS Product_Name
FROM products;

12. Summary

DISTINCT removes duplicates.

AS renames columns or tables temporarily.

Together, they make SQL output:

• cleaner
• shorter
• more readable
• more business-friendly

These are essential for writing professional SQL queries.

Final Mental Model

DISTINCT asks:

Do I really need repeated values?

AS asks:

Can I make this output easier to understand?