SQL WHERE Clause

The SQL WHERE clause filters rows by a condition. With SELECT, only rows where the condition evaluates as true are returned.

SELECT id, name, class, mark
FROM student
WHERE class = 'Four';

This returns students whose class value is Four.

SQL WHERE Syntax Top ↑

SELECT column1, column2
FROM table_name
WHERE condition;

The condition is tested for each candidate row. Rows for which the condition is true are included in the result.

For example, to return students with marks greater than 70:

SELECT id, name, mark
FROM student
WHERE mark > 70;

WHERE is not limited to SELECT. It is also important with UPDATE and DELETE when only selected rows should be changed or removed.

Comparison Operators in WHERE Top ↑

Common comparison operators include:

Text, Numeric and Date Values Top ↑

String literals are normally enclosed in single quotes:

WHERE class = 'Four'

Numeric literals normally do not need quotes:

WHERE mark >= 70

Date literals are commonly written as quoted values in a format understood by the database:

WHERE join_date >= '2026-01-01'

The column datatype matters. Do not rely on automatic type conversion when the value can be supplied in the correct form.

Multiple WHERE Conditions with AND and OR Top ↑

Use AND when all conditions must be true. This query returns Class Four students with marks above 70:

SELECT id, name, class, mark
FROM student
WHERE class = 'Four'
  AND mark > 70;

Use OR when either condition can be true:

SELECT id, name, class
FROM student
WHERE class = 'Four'
   OR class = 'Five';

SELECT id, name, class, mark
FROM student
WHERE (class = 'Four' OR class = 'Five')
  AND mark >= 70;

WHERE with BETWEEN Top ↑

BETWEEN tests an inclusive range. Both boundary values are included.

SELECT id, name, mark
FROM student
WHERE mark BETWEEN 60 AND 70;

WHERE with LIKE Top ↑

LIKE performs pattern matching. The percent sign (%) matches any sequence of characters.

SELECT id, name
FROM student
WHERE name LIKE '%John%';

This matches names containing John anywhere in the value. Matching behavior such as case sensitivity can depend on the database and column collation.

WHERE with IN and NOT IN Top ↑

IN is useful when the same column can match one of several values:

SELECT id, name, class
FROM student
WHERE class IN ('Four', 'Five');

To exclude those values:

SELECT id, name, class
FROM student
WHERE class NOT IN ('Four', 'Five');

WHERE with NULL Values Top ↑

NULL represents a missing or unknown value. Do not test it with ordinary equality:

WHERE phone = NULL

Use IS NULL:

SELECT id, name
FROM student
WHERE phone IS NULL;

For rows where the value is present:

SELECT id, name
FROM student
WHERE phone IS NOT NULL;

WHERE with Aggregate Queries Top ↑

WHERE can restrict the rows used by aggregate functions such as COUNT(), AVG(), MAX() and MIN().

Maximum mark in Class Three:

SELECT MAX(mark) AS max_mark
FROM student
WHERE class = 'Three';

Number of Class Four students with marks of at least 50:

SELECT COUNT(*) AS student_count
FROM student
WHERE class = 'Four'
  AND mark >= 50;

Several aggregates can be calculated from the same filtered rows:

SELECT COUNT(*) AS student_count,
       AVG(mark) AS average_mark,
       MAX(mark) AS highest_mark,
       MIN(mark) AS lowest_mark
FROM student
WHERE class = 'Three';

WHERE vs HAVING Top ↑

WHERE filters rows before grouping. HAVING filters grouped results after GROUP BY.

SELECT class, AVG(mark) AS average_mark
FROM student
WHERE mark >= 50
GROUP BY class
HAVING AVG(mark) > 70;

Here WHERE mark >= 50 decides which student rows participate in the groups. HAVING AVG(mark) > 70 then keeps only groups whose average is above 70.

WHERE with a Subquery Top ↑

A subquery can supply a value used by the WHERE condition. To return every student tied for the highest mark:

SELECT id, name, mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

WHERE with UPDATE and DELETE Top ↑

WHERE is especially important with statements that modify data.

Increase marks by 5 for Class Five students:

UPDATE student
SET mark = mark + 5
WHERE class = 'Five';

Delete one student by ID:

DELETE FROM student
WHERE id = 10;

Use WHERE Values Safely with PHP PDO Top ↑

When the filter value comes from a form, URL or other external source, use a prepared statement instead of joining that value directly into SQL.

<?php
require 'config.php';

$class='Four';

$sql="SELECT id,name,class,mark
      FROM student
      WHERE class=:class
      ORDER BY id";

$stmt=$dbo->prepare($sql);
$stmt->bindValue(':class',$class,PDO::PARAM_STR);
$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $id=(int)$row['id'];
    $name=htmlspecialchars((string)$row['name'],ENT_QUOTES,'Windows-1252');
    $student_class=htmlspecialchars((string)$row['class'],ENT_QUOTES,'Windows-1252');
    $mark=(int)$row['mark'];

    echo "$id - $name - $student_class - $mark<br>";
}

Prepared statements handle SQL values safely. Application validation is still required when a value must follow rules such as an allowed range or known set of choices.

SQL WHERE Video Tutorial Top ↑

Common SQL WHERE Problems Top ↑

Using = NULL Top ↑

Use IS NULL or IS NOT NULL for NULL tests.

Mixing AND and OR without Parentheses Top ↑

Operator precedence can produce a different result from what the query author intended. Use parentheses to group related conditions.

Forgetting WHERE in UPDATE or DELETE Top ↑

Without WHERE, an UPDATE or DELETE can affect every row. Preview the target rows with SELECT when working with important data.

Using HAVING for an Ordinary Row Filter Top ↑

Use WHERE for normal row filtering. HAVING is primarily for conditions applied after grouping and aggregation.

Concatenating User Input into the Condition Top ↑

In application code, use prepared statements for external values. Do not build a WHERE condition by directly concatenating untrusted form or URL input.

Assuming LIKE Is Always Case Sensitive Top ↑

String comparison behavior can depend on the database and collation. Check the column/database collation when case-sensitive matching matters.

Filtering on a Calculated Aggregate with WHERE Top ↑

WHERE runs before grouped aggregate results are produced. Use HAVING when a condition depends on an aggregate result such as AVG(mark).

Related SQL WHERE Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: