4.2 WHERE clause

The WHERE clause in SQL is used to filter records based on a specified condition. It is an essential component for retrieving specific data from a table. Let's explore the WHERE clause with various examples.

4.2.1 Basic WHERE Clause

The basic syntax of the WHERE clause is as follows:

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

Example:

-- Retrieve employees with a salary greater than 50000
SELECT * 
FROM employees
WHERE salary > 50000;

Output:

employee_id	first_name	last_name	salary
101	John	Doe	60000
102	Jane	Smith	75000
...	...	...	...

4.2.2 Multiple Conditions with AND

You can use the AND operator in the WHERE clause to combine multiple conditions.

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

Example:

-- Retrieve employees with a salary between 50000 and 70000
SELECT * 
FROM employees
WHERE salary >= 50000 AND salary <= 70000;

Output:

employee_id	first_name	last_name	salary
101	John	Doe	60000
103	Bob	Johnson	65000
...	...	...	...

4.2.3 Using OR to Extend Conditions

The OR operator allows you to retrieve records that meet either of two conditions.

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

Example:

-- Retrieve employees with a salary less than 50000 or greater than 70000
SELECT * 
FROM employees
WHERE salary < 50000 OR salary > 70000;

Output:

employee_id	first_name	last_name	salary
102	Jane	Smith	75000
105	Alice	Williams	80000
...	...	...	...

4.2.4 Combining AND and OR

You can use parentheses to control the order of evaluation when combining AND and OR conditions.

SELECT column1, column2, ...
FROM table
WHERE (condition1 AND condition2) OR condition3;

Example:

-- Retrieve employees with a salary less than 50000 or (with a salary between 60000 and 70000)
SELECT * 
FROM employees
WHERE salary < 50000 OR (salary >= 60000 AND salary <= 70000);

Output:

employee_id	first_name	last_name	salary
101	John	Doe	60000
103	Bob	Johnson	65000
...	...	...	...

This concludes the tutorial on the WHERE clause in SQL queries. The WHERE clause is a powerful tool for filtering data and retrieving specific records based on specified conditions.