WHERE command in SQL to use conditions

WHERE clause is used to filter records based on a specified condition. It allows you to narrow down the data you retrieve by setting conditions. Only rows that meet the specified criteria will be included in the result.

Let’s begin by understanding the basic syntax of the WHERE statement:

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

Here, SELECT specifies the columns to be retrieved, FROM indicates the table, and WHERE sets the conditions for filtering.

Basic Examples

Example 1: Filtering by Equality

Consider a table named students:

ID	Name	Age	Grade
1	Alice	21	A
2	Bob	22	B
3	Carol	20	C

To retrieve students with the name ‘Alice,’ the WHERE statement can be used:

SELECT * FROM students
WHERE Name = 'Alice';

Output:

ID	Name	Age	Grade
1	Alice	21	A

Example 2: Using Comparison Operators

Extend your understanding with comparison operators like <, >, <=, and >=:

SELECT * FROM students
WHERE Age >= 21;

Output:

ID	Name	Age	Grade
1	Alice	21	A
2	Bob	22	B

Common Operators

Now, let’s introduce common operators like <> (not equal) and LIKE for more complex conditions.

Example 3: Filtering by Inequality

SELECT * FROM students
WHERE Grade <> 'A';

Output:

ID	Name	Age	Grade
2	Bob	22	B
3	Carol	20	C

Example 4: Using the LIKE Operator

SELECT * FROM students
WHERE Name LIKE 'Ca%';

Output:

ID	Name	Age	Grade
3	Carol	20	C

Combining Conditions

Combine multiple conditions using logical operators (AND, OR, NOT) for more precise filtering.

Example 5: Using AND

SELECT * FROM students
WHERE Age >= 20 AND Grade = 'C';

Output:

ID	Name	Age	Grade
3	Carol	20	C

Now, let’s apply the WHERE statement to a real-world scenario. Consider a more extensive database, and let’s formulate conditions and execute queries.

Imagine we have a more complex database named employees:

ID	Name	Department	Salary
1	John	Marketing	50000
2	Sarah	IT	60000
3	Michael	Finance	55000
4	Emily	HR	48000

Example 6: Filtering by Department and Salary

Let’s say we want to find employees in the IT department with a salary greater than $55,000:

SELECT * FROM employees
WHERE Department = 'IT' AND Salary > 55000

Output:

ID	Name	Department	Salary
2	Sarah	IT	60000

Consider indexing columns used in WHERE conditions for faster query performance.

Using wildcards like %value% at the start of the LIKE condition can slow down queries. Prefer %value or value% for better optimization.