SQL - WHERE clause

The WHERE clause is a component of a SQL (Structured Query Language) statement used to filter data based on specified conditions. It is typically used in conjunction with the SELECT, UPDATE, DELETE, or INSERT statements to specify criteria for selecting or modifying data.

The WHERE clause allows you to define conditions that must be met for a particular row to be included in the result set of a query. It acts as a filter that restricts the rows returned by the query based on the specified conditions.

Here's the basic syntax of a SELECT statement with a WHERE clause:

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

In the above syntax, column1, column2, ... represent the columns you want to retrieve from the table, table_name is the name of the table you are querying, and condition is the expression that defines the filtering criteria.

For example, let's say you have a table called "Employees" with columns such as "EmployeeID," "FirstName," "LastName," and "Salary" with the following data:

If you want to retrieve the names of employees whose salary is greater than 50000, you can use the following query:

SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000;

Now, let's break down the SQL query with the WHERE clause:

1. SELECT FirstName, LastName: This part of the query specifies the columns you want to retrieve from the Employees table. In this case, you want to retrieve the FirstName and LastName of employees.
2. FROM Employees: This part of the query specifies the table you're querying from, which is the Employees table in this example.
3. WHERE Salary > 50000: This is the WHERE clause, which filters the rows in the Employees table. It only selects rows where the Salary column value is greater than $50,000.

Applying the WHERE clause to the example data, the query will return the following result of employees whose salary is greater than 50000:

Explanation of the result:

1. John Smith's salary is $60,000, which is greater than $50,000.
2. Jane Doe's salary is $55,000, which is also greater than $50,000.
3. Sarah Adams and Michael Brown have salaries of $70,000 and $52,000, respectively, both of which are greater than $50,000.

These are the employees who meet the condition specified in the WHERE clause, so their FirstName and LastName are selected and displayed in the result set.

The WHERE clause can include various operators such as equal to (=), not equal to (!= or <>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and logical operators such as AND, OR, and NOT to create complex conditions.

Let's explain how these operators work and provide examples using the same Employees table. This table contains employee data with columns for EmployeeID, FirstName, LastName, Salary, and Department:

AND Operator (AND):

The AND operator is used to combine multiple conditions, and all conditions must be true for a row to be included in the result.
Example: Retrieve employees whose salary is greater than $50,000 and their department is 'Sales.'

SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000 AND Department = 'Sales';

Result:

Explanation: This query selects employees whose salary is greater than $50,000 (John and Jane) and who work in the 'Sales' department.

OR Operator (OR):

The OR operator is used to combine multiple conditions, and at least one condition must be true for a row to be included in the result.
Example: Retrieve employees whose salary is greater than $60,000 or their department is 'Marketing.'

SELECT FirstName, LastName
FROM Employees
WHERE Salary > 60000 OR Department = 'Marketing';

Result:

Explanation: This query selects employees whose salary is greater than $60,000 (John and Sarah) and employees who work in the 'Marketing' department (Michael).

NOT Operator (NOT):

The NOT operator negates a condition, selecting rows where the condition is not true.
Example: Retrieve employees whose salary is NOT greater than $50,000.

SELECT FirstName, LastName
FROM Employees
WHERE NOT Salary > 50000;

Result:

Explanation: This query selects employees whose salary is not greater than $50,000 (David and Emily).

You can combine these operators to create more complex and precise filtering conditions in your SQL queries, allowing you to retrieve the data that matches your specific criteria.

It's important to note that the WHERE clause is case-insensitive by default in most database systems. However, the specific behavior may vary depending on the database you are using.