SQL WHERE with AND, OR, NOT Operators

In SQL, the WHERE clause is used to filter records from a table based on certain conditions. It allows you to select only the rows that meet the specified criteria, thus providing a more targeted and specific result set. Additionally, you can use logical operators such as AND, OR, and NOT to combine multiple conditions and create more complex queries. In this article, we will discuss SQL WHERE and logical operators in detail, along with examples. Read the article about read about SELECT STATEMENT if not yet

SQL WHERE

The WHERE clause is used in conjunction with the SELECT statement to filter records based on a condition. The syntax for using WHERE clause is as follows:

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

Here, table_name is the name of the table from which you want to retrieve data, and condition is the expression that specifies the criteria for selecting rows. The condition can be any valid expression that evaluates to true or false.

Examples

Suppose we have a table named employees with the following data:

To retrieve the employees with a salary greater than or equal to 70000, we can use the following SQL query:

SELECT * FROM employees
WHERE salary >= 70000;

This will return the following result:

SQL AND, OR, NOT

In addition to the WHERE clause, you can use logical operators like AND, OR, and NOT to combine multiple conditions in a single SQL statement. These operators allow you to create more complex queries that involve multiple criteria.

SQL AND

The AND operator is used to combine two or more conditions, and both conditions must be true for the record to be included in the result set. The syntax for using the AND operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

Examples

Suppose we want to retrieve the employees with a salary greater than or equal to 70000 and age less than or equal to 40. We can use the following SQL query:

SELECT * FROM employees
WHERE salary >= 70000 AND age <= 40;

This will return the following result:

SQL OR

The OR operator is used to combine two or more conditions, and at least one of the conditions must be true for the record to be included in the result set. The syntax for using the OR operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

Examples

Suppose we want to retrieve the employees with a salary greater than or equal to 70000 or age less than or equal to 30. We can use the following SQL

SELECT *
FROM employees
WHERE age <= 30 OR salary >= 70000;

Using multiple conditions with the AND, OR, and NOT operators is a powerful way to filter data using SQL. Here are some examples:

SQL WHERE with AND

The following SQL statement returns all employees whose salary is greater than 50,000 and whose department is ‘Marketing’:

SELECT * FROM employees
WHERE salary > 50000 AND department = 'Marketing';

This will return a table of employees that meet both criteria.

SQL WHERE with OR

The following SQL statement returns all employees whose department is ‘Marketing’ or ‘Sales’:

SELECT * FROM employees
WHERE department = 'Marketing' OR department = 'Sales';

This will return a table of employees that belong to either the Marketing or Sales department.

SQL WHERE with NOT

The following SQL statement returns all employees whose salary is not greater than 50,000:

SELECT * FROM employees
WHERE NOT salary > 50000;

This will return a table of employees whose salary is less than or equal to 50,000.

Combining AND, OR, and NOT

You can also use a combination of AND, OR, and NOT operators to create complex conditions. For example:

SELECT * FROM employees
WHERE (department = 'Marketing' OR department = 'Sales')
AND NOT (salary < 50000 OR experience < 5);

This will return a table of employees who belong to either the Marketing or Sales department, but whose salary is not less than 50,000 or experience is less than 5 years.

The WHERE clause is a powerful feature of SQL that allows you to filter data based on specific conditions. By using the AND, OR, and NOT operators, you can create complex conditions that can be used to retrieve exactly the data you need.

Practice Tasks

Beginner

Input data:

Task: Write a SQL query to retrieve all customers from the ‘customers’ table who are located in the state of California.

Intermediate

Input data:

Task: Write a SQL query to retrieve all products from the ‘products’ table that have a price greater than $50 and were added to the database after January 1st, 2022. Order the results by price in ascending order.

Advanced

Input data:

Task: Retrieve the names of customers who placed orders in March 2022 and have placed at least two orders. The result should be sorted in alphabetical order by customer name.

Summary

The WHERE clause in SQL is a powerful feature that allows you to filter data based on specific conditions. By using the AND, OR, and NOT operators, you can create complex conditions that can be used to retrieve exactly the data you need.

If you’re interested in learning more about SQL, be sure to check out our articles on SQL Order By (ASC and DESC) and SQL Insert Into Statement.