Meritshot Tutorials

  1. Home
  2. »
  3. SQL WHERE Clause
SQL

SQL Tutorial

SQL WHERE Clause

The WHERE clause is a crucial component of SQL used to filter records and retrieve only those rows that meet specific criteria. By applying conditions, you can narrow down the result set of your queries, making your data retrieval more precise and relevant.

Basic Syntax of the WHERE Clause

The basic syntax of the WHERE clause is as follows:

SELECT column1, column2, …

FROM table_name

WHERE condition;

  1. SELECT: Specifies the columns you want to retrieve.
  2. FROM: Specifies the table from which to retrieve the data.
  3. WHERE: Specifies the condition that must be met for the rows to be included in the result set.

Example:

Of a Simple WHERE Clause

Consider a table named employees with the following columns: employee_id, first_name, last_name, age, department, and salary. If you want to retrieve details of employees who work in the ‘Sales’ department, you would write:

SELECT employee_id, first_name, last_name

FROM employees

WHERE department = ‘Sales’;

Output:

employee_id

first_name

last_name

1

Rajesh

Sharma

3

Priya

Patel

Using Comparison Operators

The WHERE clause supports various comparison operators to filter data:

  • =: Equals
  • <> or !=: Not equal
  • <: Less than
  • >: Greater than
  • <=: Less than or equal to
  • >=: Greater than or equal to

For example:

To retrieve employees with a salary greater than 50,000:

SELECT employee_id, first_name, last_name, salary

FROM employees

WHERE salary > 50000;

For example:

To find employees who are either in the 'Sales' department or have a salary greater than 60,000:

SELECT employee_id, first_name, last_name, department, salary

FROM employees

WHERE department = ‘Sales’ OR salary > 60000;

Output:

employee_id

first_name

last_name

department

salary

1

Rajesh

Sharma

Sales

45000

2

Aarti

Mehta

Marketing

60000

4

Ravi

Kumar

HR

70000

Using Pattern Matching

The LIKE operator allows for pattern matching with wildcard characters:

  • %: Represents zero or more characters
  • _: Represents a single character

To find employees whose last name starts with ‘S’:

SELECT employee_id, first_name, last_name

FROM employees

WHERE last_name LIKE ‘S%’;

Output:

employee_id

first_name

last_name

1

Rajesh

Sharma

Using Range Conditions

The BETWEEN operator is used to filter records within a certain range:

SELECT employee_id, first_name, last_name, salary

FROM employees

WHERE salary BETWEEN 40000 AND 60000;

Output:

employee_id

first_name

last_name

salary

1

Rajesh

Sharma

45000

2

Aarti

Mehta

60000

Using NULL Values

To filter records where a column is NULL or NOT NULL, use the IS NULL or IS NOT NULL operators:

SELECT employee_id, first_name, last_name

FROM employees

WHERE department IS NULL;

Output:

employee_id

first_name

last_name

5

Anil

Kapoor

Tips to Remember

  1. Order of Conditions: SQL evaluates conditions from left to right. Combine multiple conditions using logical operators for complex queries.
  2. Be Specific: Write precise conditions to avoid retrieving excessive or irrelevant data.
  3. Test Queries: Always test your queries with different conditions to ensure they return the expected results.
  4. Performance Considerations: Using indexes on columns frequently used in WHERE clauses can improve query performance.

Power of Filtering Data

The WHERE clause is an essential tool for querying databases effectively. It allows you to:

  • Filter Records: Retrieve only the rows that meet specified conditions.
  • Combine Conditions: Use logical operators to create complex filters.
  • Handle Patterns and Ranges: Use pattern matching and range conditions for more flexible data retrieval.

Frequently Asked Questions

Q1: Can I use WHERE with aggregate functions?
A1: No, WHERE cannot be used with aggregate functions directly. Use the HAVING clause for filtering data after aggregation.

Q2: How can I check if a column contains any NULL values?
A2: Use the IS NULL condition in your WHERE clause to filter rows with NULL values.

Q3: Can WHERE be used with subqueries?
A3: Yes, WHERE can be used with subqueries to filter results based on the results of another query.

Q4: What’s the difference between WHERE and HAVING?
A4: WHERE is used to filter rows before aggregation, while HAVING is used to filter rows after aggregation.

Q5: Can I use WHERE to filter based on the results of a JOIN?
A5: Yes, you can use WHERE to filter data after performing a JOIN operation.

❮ Previous
Next ❯
ads website