WHERE Clause

Introduction

In MS SQL, the WHERE clause is used to filter records based on specified conditions. It allows you to extract only those records that meet certain criteria from a table or join. The WHERE clause can work with various SQL operators, including comparison operators, logical operators, and more.

The WHERE clause is essential in SQL queries because it lets you fine-tune your data retrieval, reducing the number of records returned and allowing for more focused analysis. Whether you need to select rows where a certain condition is met or perform complex filtering, the WHERE clause is your tool.

Syntax

The basic syntax of the WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Tables

Customers

Orders

The condition can involve comparison between columns and values, or complex logical expressions.

1. Using the WHERE Clause with Comparison Operators

The most common use of the WHERE clause is to filter data based on comparison operators like =, <>, <, >, <=, and >=.

Example Filtering by Exact Match Suppose you have a table customers, and you want to retrieve all customers from the city of "New York":

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York';

This query returns all customers whose city column is exactly "New York."

Example Filtering by Numeric Condition Let's say you want to retrieve all orders where the total amount is greater than 300 from the orders table:

SELECT order_id, total_amount
FROM orders
WHERE total_amount > 300;

This will only return orders with a total_amount above 300.

2. Using the WHERE Clause with Logical Operators

Logical operators like AND, OR, and NOT allow you to combine multiple conditions in the WHERE clause.

Example Using AND If you want to filter customers who are from "New York" and have customer_id < 3., you can combine conditions with AND:

SELECT customer_id, first_name, last_name, city
FROM customers
WHERE city = 'New York' AND customer_id < 3;

This query returns only customers who live in New York and have customer_id < 3.

Example Using OR If you want to retrieve customers who live either in "New York" or "Los Angeles," you can use the OR operator:

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';

This will return customers from either city.

Example Using NOT The NOT operator can be used to exclude records that meet a specific condition. For instance, to retrieve all customers who are not from "New York":

SELECT first_name, last_name, city
FROM customers
WHERE city <> 'New York';

Alternatively, you can write this as:

SELECT first_name, last_name, city
FROM customers
WHERE NOT city = 'New York';

3. Using the WHERE Clause with Pattern Matching

In MS SQL, you can use the LIKE operator with the WHERE clause to filter rows based on pattern matching.

Example Using LIKE Suppose you want to retrieve all customers whose last name starts with the letter 'S'. You can use the LIKE operator with a wildcard:

SELECT first_name, last_name
FROM customers
WHERE last_name LIKE 'S%';

Note: We will discuss more about Wildcards in resources. In this case:

• % is a wildcard that matches any number of characters.

Example Using Wildcards You can also use LIKE with other patterns. For instance, finding customers whose phone number contains the sequence "123":

SELECT first_name, last_name, phone_number
FROM customers
WHERE phone_number LIKE '%123%';

Here, % before and after "123" allows for any characters before or after "123."

4. Using the WHERE Clause with IN and BETWEEN

The IN and BETWEEN operators are commonly used for filtering a set of specific values or a range of values.

Example Using IN You can use the IN operator to specify multiple possible values for a column:

SELECT first_name, city
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

This returns customers from any of the three cities listed.

Example Using BETWEEN The BETWEEN operator is useful for selecting values within a range. For example, retrieving orders with a total amount between 100 and 500:

SELECT order_id, total_amount
FROM orders
WHERE total_amount BETWEEN 100 AND 500;

This returns orders with total_amount within the specified range, inclusive.

Conclusion

The WHERE clause is an essential SQL tool for filtering data based on specific conditions. It allows you to retrieve only the rows that meet your criteria, enhancing the efficiency and precision of your queries. Whether you're working with basic filtering or more complex conditions involving multiple tables, understanding how to use the WHERE clause is crucial for writing efficient SQL queries.

Asked in Interview

• What is the purpose of the WHERE clause in SQL?
• How do you filter data based on multiple conditions using the WHERE clause?
• Explain how to use the LIKE operator with the WHERE clause.
• How would you filter records in a range using the WHERE clause?
• Can you use the WHERE clause with aggregate functions like COUNT or SUM?