SQL AND, OR, NOT and XOR Conditions

Use AND, OR and NOT inside a WHERE clause to combine or reverse conditions. MySQL also supports XOR when exactly one of two conditions should be true.

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

This returns only rows where both conditions are true.

SQL AND Operator Top ↑

AND requires every combined condition to be true.

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

The result contains class Four students only when their mark is also greater than 70.

More than two conditions can be combined:

SELECT id, name, class, mark, gender
FROM student
WHERE class = 'Four'
  AND mark >= 60
  AND gender = 'female';

SQL OR Operator Top ↑

OR keeps a row when at least one condition is true.

SELECT id, name, class, mark
FROM student
WHERE class = 'Five'
   OR mark > 90;

A row is returned if it belongs to class Five, has a mark above 90, or satisfies both conditions.

AND, OR and Parentheses Top ↑

Suppose the requirement is:

Return students from class Five or Six, but only when the mark is greater than 80.

This query does not express that requirement correctly:

SELECT id, name, class, mark
FROM student
WHERE class = 'Five'
   OR class = 'Six'
  AND mark > 80;

Because AND has higher precedence than OR, MySQL interprets it like this:

WHERE class = 'Five'
   OR (class = 'Six' AND mark > 80)

That means every class Five row can match regardless of its mark.

Use parentheses to group the class conditions first:

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

OR vs IN for Several Values of the Same Column Top ↑

When several OR conditions compare the same column for equality, IN is usually clearer.

Instead of:

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

write:

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

Both express the same equality logic here; IN is easier to extend when the list grows.

Different Rules for Different Groups Top ↑

Parenthesized groups are useful when different classes have different pass marks.

SELECT id, name, class, mark
FROM student
WHERE (class = 'Five' AND mark > 75)
   OR (class = 'Six' AND mark > 80)
   OR (class = 'Seven' AND mark > 85);

Each parenthesized block represents one complete rule; OR then combines the rules.

SQL NOT Operator Top ↑

NOT reverses a condition.

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

For simple comparisons, the same intent may be clearer with a comparison operator:

SELECT id, name, class
FROM student
WHERE class <> 'Five';

See SQL comparison operators.

Exclude Several Values with NOT IN Top ↑

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

For NULL values themselves, use IS NULL or IS NOT NULL, not equality comparisons with NULL.

Using NOT BETWEEN Top ↑

NOT can also be part of operators such as NOT BETWEEN.

SELECT id, name, mark
FROM student
WHERE mark NOT BETWEEN 50 AND 100;

BETWEEN is inclusive, so this returns rows outside the range 50 through 100.

MySQL XOR Operator Top ↑

MySQL supports XOR for exclusive OR. With ordinary true/false expressions, XOR is true when exactly one condition is true.

SELECT 1 XOR 0; -- 1
SELECT 0 XOR 1; -- 1
SELECT 1 XOR 1; -- 0
SELECT 0 XOR 0; -- 0

Example:

SELECT id, name, class, mark
FROM student
WHERE class = 'Five'
   XOR mark < 50;

This keeps rows where exactly one of those two conditions is true.

AND / OR Conditions with PHP PDO Top ↑

When condition values come from an application, keep them as prepared-statement parameters.

<?php
require 'config.php';

$class1='Five';
$class2='Six';
$minimum_mark=80;

$sql="SELECT id,name,class,mark
      FROM student
      WHERE (class=:class1 OR class=:class2)
        AND mark>:minimum_mark
      ORDER BY id";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':class1',
    $class1,
    PDO::PARAM_STR
);

$stmt->bindValue(
    ':class2',
    $class2,
    PDO::PARAM_STR
);

$stmt->bindValue(
    ':minimum_mark',
    $minimum_mark,
    PDO::PARAM_INT
);

$stmt->execute();

The parentheses belong to the SQL logic; prepared statements safely supply the data values.

DELETE with AND / OR Top ↑

Delete rows from either class Three or class Four:

DELETE FROM student
WHERE class = 'Three'
   OR class = 'Four';

For the same-column equality case, this is clearer:

DELETE FROM student
WHERE class IN (
    'Three',
    'Four'
);

Delete class Six or Four students only when the mark is below 80:

DELETE FROM student
WHERE (class = 'Six' OR class = 'Four')
  AND mark < 80;

See SQL DELETE for the full destructive-query workflow.

UPDATE with AND / OR Top ↑

Add five marks to class Three and Four students whose mark is above 80:

UPDATE student
SET mark = mark + 5
WHERE (class = 'Three' OR class = 'Four')
  AND mark > 80;

Again, because the class checks use equality on one column, IN can make the condition shorter:

UPDATE student
SET mark = mark + 5
WHERE class IN (
    'Three',
    'Four'
)
  AND mark > 80;

See SQL UPDATE for safe update practices.

AND / OR Compared with && / || Top ↑

MySQL has supported symbolic logical forms such as && for AND and, depending on SQL mode, || may be treated as logical OR or string concatenation.

For tutorials and portable SQL, prefer the keyword forms:

WHERE class = 'Four'
  AND mark > 70

and:

WHERE class = 'Five'
   OR class = 'Six'

The keyword forms are clearer and avoid SQL-mode ambiguity.

Common AND / OR / NOT Mistakes Top ↑

Mixing AND and OR without parentheses Top ↑

AND has higher precedence than OR in MySQL. Add parentheses to express the intended grouping explicitly.

Using many OR conditions for one column Top ↑

For repeated equality checks on the same column, IN is often shorter and easier to maintain.

Comparing NULL with = or <> Top ↑

Use IS NULL and IS NOT NULL for NULL checks.

Using NOT IN without considering NULL Top ↑

NULL values can make a NOT IN condition evaluate as unknown. Test NULL behavior explicitly, particularly when the values come from a subquery.

Assuming XOR is portable SQL Top ↑

MySQL supports XOR, but other database systems may not provide the same keyword.

Running DELETE or UPDATE before previewing the condition Top ↑

For important data, first run a SELECT using the same WHERE condition and verify the rows it matches.

Related SQL 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: