SQL WHERE Clause Basics: Master Filtering with Operators, BETWEEN, IN, LIKE Examples & Best Practices

1. Using the WHERE Clause

Q: What is the WHERE clause in SQL?

The WHERE clause filters rows in a SELECT, UPDATE, or DELETE statement based on specified conditions. It follows the FROM clause and evaluates each row, returning only those that satisfy the condition.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Q: Why is the WHERE clause important?

• Reduces the result set to relevant rows, improving query efficiency.
• Enables targeted updates or deletions (e.g., update specific employees).
• Supports complex filtering with operators and patterns.

Q: Can you give a basic example of the WHERE clause?

-- Create sample table
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Department VARCHAR(50),
  Salary DECIMAL(10, 2),
  HireDate DATE
);

-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES
  (1, 'John', 'Doe', 'IT', 60000.00, '2023-01-15'),
  (2, 'Jane', 'sahil', 'HR', 55000.00, '2022-06-20'),
  (3, 'kristal', 'Johnson', 'IT', 65000.00, '2021-09-10'),
  (4, 'Ram', 'Williams', 'HR', 55000.00, '2023-03-01'),
  (5, 'hari', 'Brown', 'Marketing', 70000.00, '2020-11-05');

-- Basic WHERE clause
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT';

Output:

FirstName | LastName | Salary
----------|----------|--------
John      | Doe      | 60000.00
kristal   | Johnson  | 65000.00

2. Comparison and Logical Operators

Q: What are comparison operators in SQL?

Comparison operators compare values in the WHERE clause to filter rows. Common operators include:

• = (equal)
• <> or != (not equal)
• > (greater than)
• < (less than)
• >= (greater than or equal)
• <= (less than or equal)

Q: What are logical operators in SQL?

Logical operators combine multiple conditions in the WHERE clause:

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates a condition.

Q: Can you give an example using comparison and logical operators?

-- Filter employees with salary > 56000 in IT or Marketing
SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Salary > 56000 AND (Department = 'IT' OR Department = 'Marketing');

Output:

FirstName | LastName | Department | Salary
----------|----------|------------|--------
John      | Doe      | IT         | 60000.00
kristal   | Johnson  | IT         | 65000.00
hari      | Brown    | Marketing  | 70000.00

3. Using BETWEEN, IN, and LIKE

Q: What is the BETWEEN operator?

The BETWEEN operator filters rows where a column's value lies within a range (inclusive). It is used for numeric, date, or string ranges.

Syntax: column BETWEEN value1 AND value2

Equivalent to: column >= value1 AND column <= value2

Q: What is the IN operator?

The IN operator filters rows where a column's value matches any value in a specified list.

Syntax: column IN (value1, value2, ...)

Equivalent to: column = value1 OR column = value2 OR ...

Q: What is the LIKE operator?

The LIKE operator filters rows based on pattern matching for strings, using wildcards:

• %: Matches any sequence of characters (including none).
• _: Matches a single character.

Syntax: column LIKE 'pattern'

Q: Can you give examples of BETWEEN, IN, and LIKE?

-- BETWEEN: Employees with salary between 55000 and 65000
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary BETWEEN 55000 AND 65000;

-- IN: Employees in HR or Marketing
SELECT FirstName, LastName, Department
FROM Employees
WHERE Department IN ('HR', 'Marketing');

-- LIKE: Employees with FirstName starting with 'J'
SELECT FirstName, LastName
FROM Employees
WHERE FirstName LIKE 'J%';

4. Comprehensive Example Combining All Concepts

Q: Can you provide a comprehensive example using WHERE, comparison/logical operators, BETWEEN, IN, and LIKE?

-- Comprehensive query combining WHERE, operators, BETWEEN, IN, LIKE
SELECT FirstName, LastName, Department, Salary, HireDate
FROM Employees
WHERE Salary BETWEEN 55000 AND 65000
  AND Department IN ('IT', 'HR')
  AND FirstName LIKE 'J%'
ORDER BY Salary DESC;

5. Common Mistakes and Best Practices

Q: What are common mistakes when filtering data in SQL?

• Omitting WHERE, retrieving all rows unnecessarily.
• Using incorrect operators (e.g., = instead of LIKE for strings).
• Not using parentheses with AND/OR, causing incorrect condition evaluation.

Q: What are best practices for filtering data in SQL?

• Place WHERE before ORDER BY or LIMIT for correct query structure.
• Use specific conditions to filter early, reducing data processed.
• Use parentheses to clarify AND/OR precedence.
• Index columns used in WHERE for performance.
• Test queries with small datasets and edge cases.