WHERE Clause

The WHERE Clause is an essential component of SQL used to filter data based on specific conditions. Only rows that satisfy the conditions will be returned by the query.

Basic Syntax

The basic WHERE Clause syntax is:

SELECT column1, column2
FROM table_name
WHERE kondisi;

Conditions in the WHERE Clause use operators to compare values.

Comparison Operators

SQL provides several comparison operators for comparing values in the WHERE Clause:

-- Employees with a salary above 10 million
SELECT name, salary FROM EMPLOYEE WHERE salary > 10000000;

-- Employees with a salary less than or equal to 10 million
SELECT name, salary FROM EMPLOYEE WHERE salary <= 10000000;

-- Employees who are not Managers
SELECT name, position FROM EMPLOYEE WHERE position <> 'Manager';

The LIKE Operator

LIKE is used for pattern matching in string columns. Two wildcards are available:

• % — Represents zero or more characters
• _ — Represents exactly one character

-- Names that start with the letter 'A'
SELECT name FROM EMPLOYEE WHERE name LIKE 'A%';

-- Names that contain 'man' anywhere
SELECT name FROM EMPLOYEE WHERE name LIKE '%man%';

-- Names with 4 characters where the second character is 'i'
SELECT name FROM EMPLOYEE WHERE name LIKE '_i__';

-- Emails that end with @gmail.com
SELECT name, email FROM EMPLOYEE WHERE email LIKE '%@gmail.com';

The BETWEEN Operator

BETWEEN is used to select data within a certain range. The range is inclusive (both the lower and upper bounds are included).

-- Employees with a salary between 9 million and 12 million
SELECT name, salary FROM EMPLOYEE
WHERE salary BETWEEN 9000000 AND 12000000;

-- Employees who joined in 2023
SELECT name, hire_date FROM EMPLOYEE
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

BETWEEN is very practical for filtering data within a range without writing two conditions with AND.

The IN Operator

IN is used to check whether a column value is in a specified list of values:

-- Employees with certain job titles
SELECT name, position FROM EMPLOYEE
WHERE position IN ('Software Engineer', 'Data Analyst', 'DevOps Engineer');

-- Employees with certain ids
SELECT name, id FROM EMPLOYEE
WHERE id IN (1, 3, 5, 7);

IN is more efficient and easier to read than writing many OR conditions.

IS NULL and IS NOT NULL

Used to check whether a column contains a NULL value. Keep in mind that you cannot use comparison operators (= or !=) to test for NULL.

-- Employees whose email is not filled in (NULL)
SELECT name, email FROM EMPLOYEE WHERE email IS NULL;

-- Employees whose email is filled in (not NULL)
SELECT name, email FROM EMPLOYEE WHERE email IS NOT NULL;

Note: NULL = NULL evaluates to NULL (not TRUE) in SQL. That is why we must use IS NULL and IS NOT NULL.

The AND and OR Operators

We can combine multiple conditions using AND and OR:

-- Active employees with a salary above 10 million
SELECT name, position, salary FROM EMPLOYEE
WHERE is_active = TRUE AND salary > 10000000;

-- Employees with the job title Software Engineer OR DevOps Engineer
SELECT name, position FROM EMPLOYEE
WHERE position = 'Software Engineer' OR position = 'DevOps Engineer';

-- Combining AND and OR (use parentheses for clarity)
SELECT name, position, salary FROM EMPLOYEE
WHERE (position = 'Software Engineer' OR position = 'Data Analyst')
  AND salary > 10000000
  AND is_active = TRUE;

Important: Use parentheses () to ensure the correct order of evaluation when combining AND and OR.

The NOT Operator

NOT inverts the result of a condition:

-- Employees who are not Software Engineers
SELECT name, position FROM EMPLOYEE
WHERE NOT position = 'Software Engineer';

-- Employees whose salary is not between 9 million and 12 million
SELECT name, salary FROM EMPLOYEE
WHERE salary NOT BETWEEN 9000000 AND 12000000;

-- Employees whose job title is not in the list
SELECT name, position FROM EMPLOYEE
WHERE position NOT IN ('Manager', 'Director');

Employee Data Filtering Examples

Here are comprehensive examples of using the WHERE Clause with employee data:

-- Active employees, salary > 10 million, name contains 'an'
SELECT name, position, salary FROM EMPLOYEE
WHERE is_active = TRUE
  AND salary > 10000000
  AND name LIKE '%an%'
ORDER BY salary DESC;

-- Employees who joined in the second half of 2023
SELECT name, hire_date FROM EMPLOYEE
WHERE hire_date BETWEEN '2023-07-01' AND '2023-12-31'
ORDER BY hire_date ASC;

-- Inactive employees or salary below 10 million
SELECT name, position, salary, is_active FROM EMPLOYEE
WHERE is_active = FALSE OR salary < 10000000;

Summary

The WHERE Clause lets us filter data with high precision using various operators such as comparisons, LIKE, BETWEEN, IN, and NULL checks. Mastering the WHERE Clause is very important because nearly every SELECT query we write will require data filtering. With this understanding, we now have a strong fundamental ability to work with SQL.