How do I use comparison and logical operators in SQL?

By Wayan in Learning SQL 0 Comment

When you write SQL, every meaningful query eventually needs a condition — a boolean expression that decides which rows are returned, updated, or deleted. Conditions are built from two small but essential building blocks: comparison operators (=, <>, <, >, <=, >=) and logical operators (AND, OR, NOT).

In this tutorial you will learn what each operator does, how they combine, how operator precedence affects the result, and how NULL interacts with them through SQL’s three-valued logic. By the end, you will be able to build precise conditions with confidence.

Prerequisites

To follow along you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client that can execute SQL statements.
  • Basic familiarity with the SELECT and WHERE clauses.

If you already followed the previous tutorial on WHERE, you can reuse the same sample tables.

Sample Database

We continue with the online bookstore schema so the examples stay consistent across the series.

CREATE TABLE authors
(
    author_id   INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books
(
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country)
VALUES (1, 'Jane Austen', 'United Kingdom'),
       (2, 'Haruki Murakami', 'Japan'),
       (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
       (4, 'Gabriel Garcia Marquez', 'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock,
                   published_on)
VALUES (1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
       (2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
       (3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
       (4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
       (5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
       (6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
       (7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25,
        '1967-05-30'),
       (8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05'),
       (9, 'Unknown Title', 2, NULL, 13.00, 4, NULL);

Row 9 intentionally has NULL in category — we will use it to show how logical operators behave when a value is missing.

Comparison Operators

A comparison operator returns one of three values: true, false, or unknown (when either side is NULL).

Operator Meaning Example
= Equal to price = 12.50
<> or != Not equal to category <> 'Classic'
< Less than stock < 5
> Greater than price > 15.00
<= Less than or equal to stock <= 10
>= Greater than or equal to published_on >= DATE '2000-01-01'

<> is the ANSI-standard “not equal” operator. != is accepted by most databases but is not part of the standard.

Example: Equality and Inequality

Find every classic book:

SELECT title, category
FROM books
WHERE category = 'Classic';

Find every book that is not a classic:

SELECT title, category
FROM books
WHERE category <> 'Classic';

Notice that this second query does not return the row where category IS NULL. Comparing NULL with <> produces unknown, and WHERE keeps only rows where the condition is true. We will return to this behavior in the common mistakes section.

Example: Ordered Comparisons

Ordered operators (<, >, <=, >=) work on numbers, dates, and strings (using the column’s collation for strings).

SELECT title, price
FROM books
WHERE price >= 15.00;

Expected result:

title price
Norwegian Wood 15.75
Kafka on the Shore 18.20
Half of a Yellow Sun 16.00
One Hundred Years of Solitude 22.00
Love in the Time of Cholera 19.99

Logical Operators

Logical operators combine boolean expressions into more precise conditions.

Operator Meaning
AND True when both sides are true.
OR True when at least one side is true.
NOT Inverts a boolean: true becomes false, false becomes true, NULL stays NULL.

Example: AND

Return classic books that are currently in stock:

SELECT title, category, stock
FROM books
WHERE category = 'Classic'
  AND stock > 0;

Expected result:

title category stock
Pride and Prejudice Classic 20
One Hundred Years of Solitude Classic 25

Emma and Love in the Time of Cholera are excluded because their stock is 0.

Example: OR

Return every book that is either a Classic or a Fiction title:

SELECT title, category
FROM books
WHERE category = 'Classic'
   OR category = 'Fiction';

Example: NOT

Return every book that is not cheap (using NOT to negate a condition):

SELECT title, price
FROM books
WHERE NOT price < 15.00;

This is equivalent to WHERE price >= 15.00, but NOT is useful when the inner expression is more complex — for example, NOT (category = 'Classic' AND stock = 0).

Operator Precedence

When you combine operators in one condition, SQL evaluates them in this order (highest to lowest):

  1. Comparison operators (=, <>, <, >, <=, >=)
  2. NOT
  3. AND
  4. OR

That means AND binds tighter than OR. The following two conditions are not equivalent:

-- Interpreted as: category = 'Classic'
--                 OR (category = 'Fiction' AND price < 20.00)
WHERE category = 'Classic'
OR category = 'Fiction'
AND price < 20.00;

-- Every classic OR fiction book cheaper than 20.00
WHERE (category = 'Classic' OR category = 'Fiction')
AND price < 20.00;

When you mix AND and OR, always use parentheses. They make intent explicit and prevent subtle bugs.

Three-Valued Logic and NULL

SQL logic has three possible outcomes: true, false, and unknown. Any comparison involving NULL produces unknown.

The truth tables below explain how AND, OR, and NOT handle unknown values.

AND

Left Right Result
true true true
true false false
true unknown unknown
false anything false
unknown unknown unknown

OR

Left Right Result
true anything true
false false false
false unknown unknown
unknown unknown unknown

NOT

Operand Result
true false
false true
unknown unknown

WHERE keeps a row only when its condition evaluates to true. Rows with an unknown result are silently excluded — this is often surprising to newcomers.

Example: NULL and Inequality

You might expect the following query to return the row Unknown Title (which has category = NULL):

SELECT title, category
FROM books
WHERE category <> 'Classic';

It does not. NULL <> 'Classic' evaluates to unknown, so the row is filtered out. To include rows with missing categories, add an explicit check:

SELECT title, category
FROM books
WHERE category <> 'Classic'
   OR category IS NULL;

Use IS NULL and IS NOT NULL to test for missing values — never = NULL or <> NULL.

Combining Comparison and Logical Operators

Here is a realistic query that uses several operators together. “Show contemporary or historical books that are in stock and cost at most 16.00.”

SELECT title,
       category,
       price,
       stock
FROM books
WHERE (category = 'Contemporary' OR category = 'Historical')
  AND stock > 0
  AND price <= 16.00
ORDER BY price;

Expected result:

title category price stock
Americanah Contemporary 14.50 3
Half of a Yellow Sun Historical 16.00 8

Reading the condition clause by clause:

  1. (category = 'Contemporary' OR category = 'Historical') — restrict to two categories.
  2. AND stock > 0 — only books currently available.
  3. AND price <= 16.00 — apply the budget constraint.

The parentheses around the OR are required; without them, AND would bind first and produce a very different result.

Common Mistakes

Comparing to NULL with = or <>

Incorrect:

SELECT *
FROM books
WHERE category = NULL;

Correct:

SELECT *
FROM books
WHERE category IS NULL;

category = NULL always evaluates to unknown, so the query returns zero rows on every standard-compliant database.

Forgetting Parentheses Around OR

Incorrect (probably not what was intended):

SELECT title, category, price
FROM books
WHERE category = 'Classic'
   OR category = 'Fiction'
    AND price < 20.00;

Correct:

SELECT title, category, price
FROM books
WHERE (category = 'Classic' OR category = 'Fiction')
  AND price < 20.00;

Assuming NOT (a = b) Is the Same as a <> b When NULL Is Possible

Both NOT (category = 'Classic') and category <> 'Classic' evaluate to unknown when category is NULL, so both exclude those rows. If you want to include rows with missing values, add OR category IS NULL explicitly.

Mixing AND/OR in UPDATE and DELETE

Before running a destructive statement, verify the filter with SELECT:

SELECT *
FROM books
WHERE category = 'Classic'
  AND stock = 0;

Only after confirming the target rows should you run:

DELETE FROM books
WHERE category = 'Classic'
  AND stock = 0;

Warning: Running UPDATE or DELETE with a wrong combination of AND/OR can silently modify far more rows than expected. Practice these statements only in a disposable learning database or after taking a verified backup.

Database Compatibility

The comparison operators (=, <>, <, >, <=, >=) and the logical operators (AND, OR, NOT) are part of ANSI SQL and are supported by:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Minor differences to be aware of:

  • != versus <>. Both are widely supported, but only <> is standard. Prefer <> for portability.
  • String comparison case sensitivity. = and <> on text depend on the column collation. PostgreSQL is case-sensitive by default; MySQL and SQL Server are usually case-insensitive.
  • Boolean expressions. PostgreSQL has a native BOOLEAN type, so WHERE is_active works directly. In SQL Server and Oracle Database you typically compare against 1/0 or 'Y'/'N' flags.
  • Three-valued logic. All major systems follow it, but some client tools display unknown as an empty cell rather than NULL.

When in doubt, consult the documentation for your database and version.

Best Practices

  • Use <> instead of != for portable code.
  • Always parenthesize when mixing AND and OR. Readers should never have to remember precedence rules.
  • Handle NULL explicitly with IS NULL / IS NOT NULL when a column is nullable and those rows matter.
  • Prefer set-based operators when they read better. For fixed lists, IN (...) is clearer than a chain of ORs. For inclusive ranges, BETWEEN low AND high is clearer than two comparisons.
  • Verify destructive filters with SELECT first. This is especially important when the condition mixes AND and OR.
  • Use parameters, not string concatenation, when comparison values come from application input. This prevents SQL injection and improves plan reuse.

Conclusion

You learned how SQL comparison operators (=, <>, <, >, <=, >=) and logical operators (AND, OR, NOT) work together to form the conditions that drive every WHERE clause. You also saw how operator precedence and three-valued logic affect the outcome, and how to protect yourself with parentheses and explicit NULL checks.