Introduction
Real-world tables often contain duplicate values in a column, such as multiple employees in the same department. DISTINCT removes duplicate rows from a result set. SQL also provides a set of basic operators — comparison, logical, and pattern-matching — that let you express precise conditions in queries.
Cricket analogy: A team roster often lists the same bowling style ('right-arm fast') for multiple players; DISTINCT collapses that list to unique styles, while comparison and pattern-matching operators let you filter for players whose name starts with 'S'.
Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
-- Common operators
-- Comparison: =, <>, !=, <, >, <=, >=
-- Logical: AND, OR, NOT
-- Range: BETWEEN low AND high
-- Set membership: IN (value1, value2, ...)
-- Pattern match: LIKE 'pattern%'
-- Null check: IS NULL / IS NOT NULLExplanation
DISTINCT applies to the entire row of selected columns: two rows are considered duplicates only if all selected columns match exactly. IN checks whether a value matches any item in a list, acting as shorthand for multiple OR comparisons. BETWEEN tests whether a value falls within an inclusive range. LIKE performs pattern matching using wildcards: % matches zero or more characters, and _ matches exactly one character.
Cricket analogy: DISTINCT on (team, ground) only collapses rows where both match exactly; IN('India','Australia') shortcuts multiple OR checks, BETWEEN 2020 AND 2024 filters match years inclusively, and LIKE 'Kohl_' with an underscore matches a five-letter surname exactly.
Example
-- employees table: id, first_name, last_name, department, salary, hire_date
-- Unique department names
SELECT DISTINCT department
FROM employees;
-- Employees in Engineering or Sales whose last name starts with 'S'
SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Engineering', 'Sales')
AND last_name LIKE 'S%';Output
The first query returns each distinct department name once, e.g. 'Engineering', 'Sales', 'Marketing', with no repeats even if many employees share a department. The second query returns only employees whose department is Engineering or Sales and whose last name begins with 'S', such as 'Maria | Silva | Sales'.
Cricket analogy: A DISTINCT query on team returns each franchise once, e.g., 'Mumbai Indians', 'Chennai Super Kings', with no repeats even though many players belong to each; a filtered query returns only bowlers on 'Mumbai' or 'Chennai' whose surname starts with 'S', like 'Ravi | Sharma | Mumbai'.
Key Takeaways
- DISTINCT removes duplicate rows based on the full combination of selected columns.
- IN is a concise alternative to chaining multiple OR conditions for the same column.
- LIKE uses % for zero-or-more characters and _ for exactly one character.
- BETWEEN is inclusive of both boundary values.
Practice what you learned
1. What does SELECT DISTINCT department FROM employees; return?
2. Which operator is a concise shorthand for multiple OR comparisons on the same column?
3. In a LIKE pattern, what does the % wildcard represent?
4. If SELECT DISTINCT first_name, department FROM employees is run, when are two rows considered duplicates?
Was this page helpful?
You May Also Like
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.
SELECT Statement Basics
Learn how to retrieve data from a table using the SELECT statement, the foundation of every SQL query.
Aggregate Functions in SQL
Learn how COUNT, SUM, AVG, MIN, and MAX summarize sets of rows into single values, and how each handles NULLs.