7.1 DISTINCT and LIMIT

Level: 7 | Version: v1.1 | Author: Meptrasoft

Overview

DISTINCT and LIMIT help control the rows returned by a SELECT query.

Clause	Purpose
`DISTINCT`	Removes duplicate result rows
`LIMIT`	Restricts the number of rows returned

DISTINCT → Remove duplicates
LIMIT → Limit rows

SQL DISTINCT vs LIMIT: Removing duplicate result rows vs restricting row counts — Figure 1: DISTINCT removes duplicate rows from a query result, while LIMIT restricts the number of rows returned.

Practice Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    country VARCHAR(50)
);

INSERT INTO customers
(customer_id, first_name, last_name, age, country)
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA'),
(3, 'David', 'Robinson', 22, 'UK'),
(4, 'John', 'Reinhardt', 25, 'UK'),
(5, 'Betty', 'Doe', 28, 'UAE');

DISTINCT

DISTINCT removes duplicate rows from the query result.

SELECT DISTINCT country
FROM customers;

Result:

country
USA
UK
UAE

There are 5 customers, but only 3 unique countries.

DISTINCT with Multiple Columns

DISTINCT considers the combination of selected columns.

SELECT DISTINCT first_name, country
FROM customers;

DISTINCT → Return unique result rows

LIMIT

LIMIT restricts the number of rows returned.

SELECT *
FROM customers
LIMIT 2;

This returns only 2 rows.

LIMIT → Return only a specified number of rows

Important: Without ORDER BY, you should not rely on which specific rows are returned as the “first” rows.

DISTINCT vs LIMIT

DISTINCT	LIMIT
Removes duplicate result rows	Restricts result size
Focuses on uniqueness	Focuses on row count
`SELECT DISTINCT country`	`SELECT * ... LIMIT 2`

Easy Memory Trick

DISTINCT → UNIQUE
LIMIT    → COUNT

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`DISTINCT` removes duplicate rows from the table	It removes duplicates from the query result
`DISTINCT` always applies to one column	It applies to the complete selected row/column combination
`LIMIT 2` always returns the same two rows	Without `ORDER BY`, row selection is not guaranteed
`LIMIT` removes data	It only limits the returned result
`DISTINCT` changes the stored table	It only affects the query result

Placement Quick Points

DISTINCT
→ REMOVE DUPLICATE RESULT ROWS

LIMIT
→ RESTRICT NUMBER OF RESULT ROWS

DISTINCT returns unique result rows.
With multiple columns, uniqueness is based on the selected combination.
LIMIT restricts the number of rows returned.
LIMIT does not delete or change stored data.
Use ORDER BY when you need a predictable subset of rows.

Interview Questions

What is DISTINCT?

DISTINCT removes duplicate rows from a query result.

What is LIMIT?

LIMIT restricts the number of rows returned by a query.

Does DISTINCT delete duplicate data from the table?

No. It only removes duplicates from the result returned by the query.

What happens with DISTINCT on multiple columns?

The database considers the combination of all selected columns when removing duplicates.

Why should ORDER BY often be used with LIMIT?

ORDER BY makes the selected rows predictable.

Example:

SELECT *
FROM customers
ORDER BY customer_id
LIMIT 2;

Practice & Hands-On Exercises

Using the customers table:

Display all unique countries:

SELECT DISTINCT country FROM customers;

Display unique first_name values:

SELECT DISTINCT first_name FROM customers;

Return only 2 customers:

SELECT * FROM customers LIMIT 2;

Return the first 3 customers ordered by customer_id:

SELECT * FROM customers ORDER BY customer_id LIMIT 3;

Explain the difference between DISTINCT and LIMIT.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

DISTINCT → UNIQUE RESULTS
LIMIT    → LIMITED ROWS

DISTINCT removes duplicate result rows, while LIMIT controls how many rows are returned.

End of DISTINCT and LIMIT