Step 1: Understanding WHERE

The WHERE clause is used to filter records in a SELECT, UPDATE, or DELETE statement based on a condition.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • Only rows that satisfy the condition are returned.
  • Conditions can use comparison operators, logical operators, or special operators like IN, BETWEEN, LIKE, IS NULL.

Step 2: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 3: Filtering with comparison operators

Example: Salary greater than 5000

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 5000;

Result:

FirstName LastName Salary
Mike Brown 6000
Tom Green 5200

Other comparison operators: =, <, <=, >=, <> (not equal)

Step 4: Filtering with AND / OR

You can combine multiple conditions using AND and OR.

Example: IT department and salary > 5000

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'IT' AND Salary > 5000;

Result:

FirstName Department Salary
Mike IT 6000
Tom IT 5200

Example: HR department OR salary < 5000

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'HR' OR Salary < 5000;

Result:

FirstName Department Salary
Jane HR 4500
Sara HR 4700
John IT 5000

Step 5: Using IN

IN allows checking if a column matches any value in a list:

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

Returns all employees in either HR or IT (similar to OR conditions).

Step 6: Using BETWEEN

BETWEEN is used for ranges (inclusive):

SELECT FirstName, Salary
FROM Employees
WHERE Salary BETWEEN 4500 AND 5200;

Result:

FirstName Salary
John 5000
Jane 4500
Sara 4700
Tom 5200

Step 7: Using LIKE

LIKE is used for pattern matching with % and _:

  • % → any number of characters
  • _ → a single character

Example: Names starting with ‘J’

SELECT FirstName
FROM Employees
WHERE FirstName LIKE 'J%';

Result:

FirstName
John
Jane

Step 8: Using IS NULL / IS NOT NULL

Check for NULL values:

SELECT FirstName, Department
FROM Employees
WHERE Department IS NOT NULL;

Returns all rows where Department is not null.

Categorized in:

SQL Server,