SQL SELECT & Filtering: DISTINCT, LIKE, IN, BETWEEN & NULL

Selecting All Records in SQL

To retrieve every row and every column from a table, use the SELECT * statement.

Syntax

SELECT *
FROM table_name;

Example

SELECT *
FROM employees;

Understanding the Query

• SELECT → Specifies what you want to retrieve.
• * → Represents all columns.
• FROM → Specifies the table to retrieve data from.

This query returns all rows and all columns from the employees table.

When to Use SELECT *

SELECT * is useful when:

• Viewing all the data in a table.
• Testing or debugging queries.
• Exploring a table when you're unsure which columns you need.

Interview Tip: Although SELECT * is convenient, it is generally avoided in production because it retrieves unnecessary columns, which can reduce performance. It's better to select only the required columns.

Example:

SELECT id, name
FROM employees;

Fetching DISTINCT Values

The DISTINCT keyword removes duplicate values and returns only unique records.

Syntax

SELECT DISTINCT column_name
FROM table_name;

Example

SELECT DISTINCT department
FROM employees;

Explanation

This query returns each department only once, even if multiple employees belong to the same department.

Using DISTINCT with Multiple Columns

You can also retrieve unique combinations of multiple columns.

SELECT DISTINCT department, location
FROM employees;

This returns only unique combinations of department and location.

Common Use Cases

• Populating dropdown lists.
• Removing duplicate values from reports.
• Displaying unique categories or locations.

The LIKE Operator

The LIKE operator is used to search for values that match a specified pattern.

It is commonly used with the WHERE clause.

Syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

Wildcards Used with LIKE

Examples

Names Starting with "Ra"

SELECT *
FROM employees
WHERE name LIKE 'Ra%';

Returns names such as:

• Ravi
• Rahul
• Ramesh

Names Ending with "i"

SELECT *
FROM employees
WHERE name LIKE '%i';

Returns names such as:

• Ravi
• Devi

Names with Exactly Five Characters

SELECT *
FROM employees
WHERE name LIKE '_____';

Each underscore (_) represents exactly one character.

When to Use LIKE

Use LIKE when:

• You don't know the exact value.
• You need partial matching.
• You want to search using prefixes, suffixes, or patterns.

Note: LIKE is case-insensitive in databases such as MySQL, but it may be case-sensitive in other database systems depending on the collation and database configuration.

The IN Operator

The IN operator lets you specify multiple values in a WHERE clause.

It is a shorter alternative to writing multiple OR conditions.

Syntax

SELECT column_names
FROM table_name
WHERE column_name IN (value1, value2, value3);

Example

SELECT *
FROM students
WHERE class IN (5, 6);

Equivalent Query

SELECT *
FROM students
WHERE class = 5
OR class = 6;

Using IN makes the query cleaner and easier to read.

The BETWEEN Operator

The BETWEEN operator filters values that fall within a specified range.

It is inclusive, meaning it includes both boundary values.

Syntax

SELECT *
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example

SELECT *
FROM students
WHERE marks BETWEEN 70 AND 90;

This query includes students whose marks are:

• 70
• 71
• ...
• 90

Both 70 and 90 are included.

BETWEEN with Dates

SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Returns all orders placed during January 2024 — if order_date is a DATE column.

Watch out with DATETIME columns: '2024-01-31' means midnight at the start of 31 January, so orders later that day are silently left out. We tested it with orders at 31 Jan 00:00 and 31 Jan 18:30: BETWEEN returned only the first. A half-open range includes the whole day:

WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'

BETWEEN with Text

SELECT *
FROM employees
WHERE employee_name BETWEEN 'A' AND 'F';

Returns names that fall alphabetically between A and F.

NOT BETWEEN

You can also exclude a range.

SELECT *
FROM students
WHERE marks NOT BETWEEN 70 AND 90;

IS NULL vs = NULL

NULL represents a missing or unknown value.

Unlike regular values, it cannot be compared using the equals (=) operator.

Incorrect

SELECT *
FROM students
WHERE marks = NULL;

This query does not work.

Correct

SELECT *
FROM students
WHERE marks IS NULL;

This returns rows where marks has no value.

IS NOT NULL

SELECT *
FROM students
WHERE marks IS NOT NULL;

Returns rows where the marks column contains a value.

Example

Find employees whose email address is missing.

SELECT *
FROM employees
WHERE email IS NULL;

Aliases in SQL

An alias is a temporary name assigned to a column or table.

Aliases improve readability and simplify complex queries.

They exist only during query execution.

Column Alias

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Database difference: first_name || ' ' || last_name is standard SQL and works in PostgreSQL, Oracle and SQLite — but in MySQL and MariaDB (default settings) || means logical OR. We ran it on MariaDB and every "full name" came back as 0. Use CONCAT() in MySQL/MariaDB; SQL Server uses + or CONCAT().

Here:

• full_name is a temporary name for the concatenated expression.

Table Alias

Table aliases make JOIN queries shorter and easier to read.

SELECT e.employee_name, d.department_name
FROM employees AS e
JOIN departments AS d
ON e.dept_id = d.id;

Here:

• e represents the employees table.
• d represents the departments table.

Benefits of Using Aliases

• Improves readability.
• Makes complex expressions easier to understand.
• Simplifies JOIN queries.
• Reduces typing in long SQL statements.

Try It: Filtering Results From a Real Run

With four employees — one with a NULL email, one called "rahul" in lower case, and one email containing an underscore — we got:

These are exactly the mistakes that make a test's database check pass or fail for the wrong reason — for example, counting users "with no email" using = NULL and getting zero every time. Practice under interview conditions in the SQL Interview Simulator. Next: Aggregate Functions & GROUP BY.

📚 Official documentation: MySQL Reference Manual

FAQs

1. How Do You Select All Records from a Table?

Use the following query:

SELECT *
FROM table_name;

The asterisk (*) represents all columns, so the query returns every row and every column from the table. A closely related topic: SQL Performance & Optimization.

For better performance, select only the columns you actually need.

2. How Do You Fetch Only DISTINCT Values?

Use the DISTINCT keyword.

SELECT DISTINCT department
FROM employees;

This returns only unique department names.

3. What Is the LIKE Operator?

The LIKE operator searches for patterns within a column.

Wildcards include:

• % – Matches zero or more characters.
• _ – Matches exactly one character.

It is commonly used with the WHERE clause for pattern matching.

4. What Is the IN Operator?

The IN operator is a shortcut for multiple OR conditions.

Example:

SELECT *
FROM students
WHERE class IN (5, 6);

This returns students whose class is either 5 or 6.

5. Is BETWEEN Inclusive?

Yes.

The BETWEEN operator includes both the starting and ending values.

It works with:

• Numbers
• Dates
• Text values

6. What Is the Difference Between IS NULL and = NULL?

• = NULL does not work because NULL represents an unknown value.
• IS NULL is used to find rows with missing values.
• IS NOT NULL is used to find rows that contain values.

7. What Is an Alias in SQL?

An alias is a temporary name assigned to a column or table using the AS keyword.

Aliases:

• Improve query readability.
• Simplify complex expressions.
• Make JOIN queries easier to understand.
• Exist only for the duration of the query.