Previous

SQL WHERE

The WHERE clause is used in SQL to filter records based on a specific condition. It allows you to specify which rows you want to retrieve, update, or delete from a table.

 

Basic Syntax of WHERE

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • column1, column2, ...: Specifies the columns you want to retrieve.
  • table_name: The table from which the data will be retrieved.
  • condition: The condition that must be met for the row to be included in the result set.

 

Key Features of the WHERE Clause

1. Filtering with Comparison Operators

You can use various comparison operators to filter rows based on values:

  • =: Equal to
  • != or <>: Not equal to
  • >: Greater than
  • <: Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to
SELECT * FROM employees WHERE salary > 50000;

This query will retrieve all employees with a salary greater than 50,000.

2. Using AND / OR Operators

  • AND is used to combine multiple conditions. All conditions must be true.
  • OR is used to combine multiple conditions. At least one condition must be true.
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 40000;

This retrieves all employees from the 'Sales' department whose salary is greater than 40,000.

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

This retrieves employees from either the 'Sales' or 'HR' department.

3. Using LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

  • %: Represents zero or more characters.
  • _: Represents a single character.
SELECT * FROM employees WHERE name LIKE 'J%';

This retrieves all employees whose name starts with 'J'.

SELECT * FROM employees WHERE name LIKE '_o%';

This retrieves all employees whose name has 'o' as the second character.

4. Using IN Operator

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

SELECT * FROM employees WHERE department IN ('Sales', 'HR', 'IT');

This retrieves all employees from the 'Sales', 'HR', or 'IT' departments.

5. Using BETWEEN Operator

The BETWEEN operator is used to filter the result set within a range of values.

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;

This retrieves all employees whose salary is between 30,000 and 60,000 (inclusive).

6. Using IS NULL / IS NOT NULL

The IS NULL condition is used to filter rows where a column's value is NULL. Conversely, IS NOT NULL filters rows where a column’s value is not NULL.

SELECT * FROM employees WHERE department IS NULL;

This retrieves all employees whose department value is NULL.

SELECT * FROM employees WHERE department IS NOT NULL;

This retrieves all employees who have a specified department.

7. Using Logical Operators

The NOT operator negates a condition and works with other operators like LIKE, IN, etc.

SELECT * FROM employees WHERE department NOT IN ('Sales', 'HR');

This retrieves employees who are not in the 'Sales' or 'HR' department.

 

Example Queries

1. Get Employees with Salary Greater Than 50,000

SELECT * FROM employees WHERE salary > 50000;

2. Get Employees from 'Sales' or 'IT' Department

SELECT * FROM employees WHERE department IN ('Sales', 'IT');

3. Find Employees with First Name Starting with 'A'

SELECT * FROM employees WHERE first_name LIKE 'A%';

4. Find Employees Whose Salary is Between 40,000 and 70,000

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 70000;

5. Find Employees with No Department Assigned

SELECT * FROM employees WHERE department IS NULL;

 

Summary

The WHERE clause in SQL is a powerful tool for filtering data based on specific conditions. It can be used with comparison operators, logical operators (AND, OR), and special operators like LIKE, IN, BETWEEN, and IS NULL to retrieve the desired rows. Understanding how to effectively use the WHERE clause is essential for querying databases efficiently.

Previous