SQL WHERE Clause

 In SQL, the WHERE clause is used to filter records from a database table that satisfy a specific condition. It is used in SELECT, UPDATE, DELETE, and other SQL statements to define the criteria that must be met for rows to be included in the result set or operation.

Basic Syntax

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

Components:

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table you're querying.
• condition: The condition that must be met to filter the records.

Example 1: Basic WHERE Clause

SELECT * 
FROM Employees
WHERE Age > 30;

This query retrieves all records from the Employees table where the Age is greater than 30.

Example 2: Using AND and OR Operators

You can combine multiple conditions using the AND and OR operators.

SELECT * 
FROM Employees
WHERE Age > 30 AND Department = 'HR';

This query will retrieve records where the employee is older than 30 and works in the 'HR' department.

SELECT * 
FROM Employees
WHERE Age > 30 OR Department = 'HR';

This query will retrieve records where either the employee is older than 30 or works in the 'HR' department.

Example 3: Using IN Operator

The IN operator allows you to specify multiple values in the WHERE clause.

SELECT * 
FROM Employees
WHERE Department IN ('HR', 'Finance', 'IT');

This query will retrieve employees who work in either the HR, Finance, or IT department.

Example 4: Using BETWEEN Operator

The BETWEEN operator is used to filter records within a range.

SELECT * 
FROM Employees
WHERE Age BETWEEN 25 AND 35;

This query will return employees whose age is between 25 and 35 (inclusive).

Example 5: Using LIKE for Pattern Matching

The LIKE operator is used for pattern matching with wildcards.

SELECT * 
FROM Employees
WHERE Name LIKE 'A%';

This will return employees whose names start with the letter 'A'. The % symbol is a wildcard that matches zero or more characters.

SELECT * 
FROM Employees
WHERE Name LIKE '%son';

This will return employees whose names end with 'son'.

Example 6: Using IS NULL and IS NOT NULL

To check if a column has NULL values, you can use IS NULL or IS NOT NULL.

SELECT * 
FROM Employees
WHERE ManagerID IS NULL;

This query retrieves employees who do not have a manager (i.e., ManagerID is NULL).

SELECT * 
FROM Employees
WHERE ManagerID IS NOT NULL;

This query retrieves employees who have a manager (ManagerID is not NULL).

Example 7: Using Comparison Operators

You can use comparison operators like =, >, <, >=, <=, !=, or <> to define conditions.

SELECT * 
FROM Employees
WHERE Salary > 50000;

This query retrieves employees who have a salary greater than 50,000.

Example 8: Using Subqueries in the WHERE Clause

A subquery can be used in the WHERE clause to filter results based on the result of another query.

SELECT * 
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');

This query retrieves employees who work in the 'HR' department, where the department ID is determined by a subquery.

Conclusion

The WHERE clause is a powerful tool in SQL for filtering records based on specific conditions. It can be used with various operators like =, >, LIKE, IN, BETWEEN, and more, to refine your query results.