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.

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 first_name || ' ' || last_name AS full_name
FROM employees;

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.

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.

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.

Related articles: SQL Performance & Optimization: Query Tuning and Indexing · Advanced SQL Concepts: Views, Indexes, Normalization, Procedures · Scenario-Based SQL Questions for Testers (With Sample Answers)