Using WHERE to Set Conditions in SQL

Welcome back to our SQL tutorial series! So far, we’ve covered the basics of SQL and how to use the SELECT and SELECT DISTINCT commands. In this article, we’re going to dive into one of the most powerful aspects of SQL: the WHERE clause. This clause allows you to filter your data based on specific conditions, making your queries more precise and meaningful.

Understanding the WHERE Clause

The WHERE clause is used to filter records that meet certain criteria. It is used in SELECT, UPDATE, DELETE, and other SQL statements to specify the conditions that must be met for the data to be included in the result set or affected by the statement.

Basic Syntax of WHERE

The basic syntax of the WHERE clause is as follows:

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

Let’s look at some practical examples to understand how to use the WHERE clause effectively.

Example Table: employees

We’ll use the same employees table as in our previous articles:

Filtering Results with WHERE

Selecting Specific Records

To select employees who are Software Engineers, you use:

SELECT * FROM employees WHERE position = 'Software Engineer';

This command returns:

Using Comparison Operators

You can use various comparison operators in the WHERE clause, such as =, >, <, >=, <=, and <> (not equal). For example, to find employees with a salary greater than $65,000:

SELECT name, salary FROM employees WHERE salary > 65000;

This returns:

Combining Conditions with AND and OR

You can combine multiple conditions using the AND and OR operators. For instance, to find employees who are Software Engineers and have a salary greater than $65,000:

SELECT name, position, salary FROM employees WHERE position = 'Software Engineer' AND salary > 65000;

This returns:

To find employees who are either Software Engineers or have a salary greater than $75,000:

SELECT name, position, salary FROM employees WHERE position = 'Software Engineer' OR salary > 75000;

This returns:

Using LIKE for Pattern Matching

The LIKE operator is used to search for a specified pattern in a column. For example, to find employees whose names start with ‘J’:

SELECT name FROM employees WHERE name LIKE 'J%';

This returns:

The % wildcard character matches any sequence of characters. Similarly, _ matches any single character.

Using IN to Match Multiple Values

The IN operator allows you to specify multiple values in a WHERE clause. For example, to find employees who are either Software Engineers or Data Analysts:

SELECT name, position FROM employees WHERE position IN ('Software Engineer', 'Data Analyst');

This returns:

Using BETWEEN for Range Filtering

The BETWEEN operator is used to filter the result set within a certain range. For example, to find employees with a salary between $60,000 and $75,000:

SELECT name, salary FROM employees WHERE salary BETWEEN 60000 AND 75000;

This returns:

Conclusion

The WHERE clause is a powerful tool in SQL that allows you to filter and refine your queries to retrieve exactly the data you need. By understanding how to use WHERE with different operators and conditions, you can perform complex and precise data retrieval tasks.

In the next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!