Standard comparison operators in PostgreSQL - Deep Dive

Overview - Standard comparison operators

What is it?

Standard comparison operators are symbols used in SQL to compare two values. They help decide if one value is equal to, greater than, less than, or different from another. These operators return true, false, or unknown, which helps filter and sort data in databases. They are essential for writing queries that find specific information.

Why it matters

Without comparison operators, databases would not know how to check conditions or filter data. Imagine trying to find all customers older than 30 without being able to say 'greater than 30'. It would be impossible to get precise answers from data. These operators make searching, sorting, and decision-making in databases fast and accurate.

Where it fits

Before learning comparison operators, you should understand basic SQL syntax and data types. After mastering them, you can learn about logical operators, complex filtering with WHERE clauses, and advanced query techniques like joins and subqueries.

Mental Model

Core Idea

Comparison operators are simple questions that ask how two values relate to each other, returning true or false answers to guide data selection.

Think of it like...

It's like comparing two numbers on a ruler to see which is longer, or checking if two keys are the same to open a lock.

┌───────────────┐
│ Value A       │
├───────────────┤
│ Comparison    │───> True / False / Unknown
├───────────────┤
│ Value B       │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding equality and inequality

Concept: Learn how to check if two values are the same or different using = and <> operators.

In SQL, '=' checks if two values are equal. For example, 'age = 30' finds rows where age is exactly 30. '<>' means not equal, so 'age <> 30' finds rows where age is anything but 30. These are the simplest comparisons to filter data.

Result

Queries using '=' return rows matching the exact value; '<>' returns rows excluding that value.

Knowing how to test equality and inequality is the foundation for all data filtering in SQL.

2

Foundation

Using greater than and less than

Concept: Learn how to compare if one value is bigger or smaller than another using > and < operators.

The '>' operator finds values greater than a given number, like 'salary > 50000' finds salaries above 50,000. The '<' operator finds smaller values, like 'age < 18' finds ages under 18. These let you filter ranges of data.

Result

Queries return rows where the condition about size is true, filtering data by range.

Understanding size comparisons lets you select data within specific numeric or date ranges.

3

Intermediate

Combining greater or equal and less or equal

🤔Before reading on: do you think '>=' means strictly greater than or also equal? Commit to your answer.

Concept: Learn operators that include equality in comparisons: >= and <=.

The '>=' operator means 'greater than or equal to'. For example, 'age >= 18' finds ages 18 and older. Similarly, '<=' means 'less than or equal to', like 'score <= 100' finds scores up to 100. These are useful for inclusive ranges.

Result

Queries include boundary values in their results, not just strictly greater or smaller.

Knowing inclusive comparisons prevents off-by-one errors and ensures correct data ranges.

4

Intermediate

Handling NULL with comparison operators

🤔Before reading on: do you think 'age = NULL' returns true for rows with no age? Commit to your answer.

Concept: Understand how NULL values behave with comparison operators in SQL.

NULL means unknown or missing data. Any comparison with NULL, like 'age = NULL' or 'age > NULL', returns UNKNOWN, not true or false. To check for NULL, use 'IS NULL' or 'IS NOT NULL'. This is important because NULL breaks normal comparisons.

Result

Queries using standard operators with NULL return no rows; special syntax is needed to find NULLs.

Recognizing NULL's special behavior avoids bugs and ensures accurate query results.

5

Intermediate

Using BETWEEN for range comparisons

🤔Before reading on: does BETWEEN include the boundary values or exclude them? Commit to your answer.

Concept: Learn the BETWEEN operator to check if a value lies within a range, including boundaries.

BETWEEN checks if a value is between two others, including the ends. For example, 'age BETWEEN 18 AND 30' finds ages 18 through 30. It's shorthand for 'age >= 18 AND age <= 30'. This makes queries cleaner and easier to read.

Result

Queries return rows where values fall inside the specified inclusive range.

Using BETWEEN simplifies range queries and reduces errors in boundary conditions.

6

Advanced

Understanding three-valued logic in comparisons

🤔Before reading on: do you think SQL comparisons always return true or false? Commit to your answer.

Concept: Learn that SQL uses true, false, and unknown (NULL) in comparisons, affecting query logic.

SQL comparisons can return TRUE, FALSE, or UNKNOWN. UNKNOWN happens when NULL is involved. For example, 'age = NULL' is UNKNOWN, not false. WHERE clauses only select rows where condition is TRUE. UNKNOWN acts like false in filtering but is distinct logically.

Result

Queries may exclude rows with NULLs unless explicitly handled, affecting results.

Understanding three-valued logic helps write correct queries that handle missing data properly.

7

Expert

Performance impact of comparison operators

🤔Before reading on: do you think all comparison operators perform equally fast? Commit to your answer.

Concept: Explore how different comparison operators affect query performance and indexing.

Indexes speed up queries using comparison operators. Operators like '=' and range comparisons ('<', '>', '<=', '>=', BETWEEN) can use indexes efficiently. However, using functions or complex expressions with comparisons may prevent index use. Also, NULL comparisons require special handling that can affect speed.

Result

Well-written comparisons improve query speed; careless use can slow down database operations.

Knowing how comparisons interact with indexes helps optimize database queries for real-world performance.

Under the Hood

When a comparison operator runs, the database engine evaluates the two values based on their data types. It applies rules for equality or order, considering special cases like NULL. The result is a boolean or unknown value that guides which rows to include. Internally, the engine uses indexes and data structures to speed up these checks.

Why designed this way?

SQL was designed to handle incomplete data (NULL) and complex queries efficiently. Standard comparison operators follow mathematical logic but extend it with three-valued logic to handle unknowns. This design balances expressiveness and performance, allowing flexible yet predictable data filtering.

┌───────────────┐       ┌───────────────┐
│   Value A     │       │   Value B     │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │                       │
       └─────────┬─────────────┘
                 │
         ┌───────▼────────┐
         │ Comparison     │
         │ Operator Logic │
         └───────┬────────┘
                 │
      ┌──────────▼───────────┐
      │ Result: TRUE/FALSE/   │
      │ UNKNOWN (NULL logic)  │
      └──────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does 'age = NULL' return true for rows where age is missing? Commit yes or no.

Common Belief: People often think 'age = NULL' finds rows where age is missing.

Reality: 'age = NULL' always returns UNKNOWN, so it finds no rows. To check missing values, use 'age IS NULL'.

Why it matters: Using '=' with NULL causes queries to miss rows with missing data, leading to incomplete results.

Quick: Does BETWEEN exclude the boundary values? Commit yes or no.

Common Belief: Some believe BETWEEN excludes the start and end values, acting like a strict range.

Reality: BETWEEN includes both boundary values; it is equivalent to '>= start AND <= end'.

Why it matters: Misunderstanding BETWEEN boundaries causes off-by-one errors and wrong data selection.

Quick: Do you think all comparison operators use indexes equally? Commit yes or no.

Common Belief: Many assume all comparisons perform the same and use indexes equally.

Reality: Only simple comparisons on indexed columns use indexes efficiently; complex expressions or functions can prevent index use.

Why it matters: Ignoring index behavior can cause slow queries and poor database performance.

Quick: Does SQL comparison always return true or false? Commit yes or no.

Common Belief: People often think comparisons return only true or false.

Reality: SQL uses three-valued logic: true, false, and unknown (due to NULL).

Why it matters: Not understanding three-valued logic leads to unexpected query results and bugs.

Expert Zone

1

Comparison operators behave differently with collations and locale settings, affecting string comparisons subtly.

2

Using comparison operators on complex data types like arrays or JSON requires special functions, not standard operators.

3

The optimizer may rewrite comparison expressions internally for better performance, which can affect how queries behave with NULLs.

When NOT to use

Avoid using standard comparison operators directly on NULL values; use IS NULL or IS NOT NULL instead. For pattern matching, use LIKE or regular expressions. For complex data types, use specialized operators or functions.

Production Patterns

In production, comparison operators are combined with indexes and partitioning to optimize large data queries. They are also used in conditional expressions for data validation, access control, and dynamic filtering in applications.

Connections

Boolean Logic

Comparison operators produce boolean results that feed into boolean logic expressions.

Understanding how comparisons produce true/false values helps grasp how complex WHERE clauses combine conditions logically.

Data Types

Comparison operators behave differently depending on the data types they compare.

Knowing data types clarifies why some comparisons work as expected and others need special handling, like dates or strings.

Three-Valued Logic in Philosophy

SQL's true/false/unknown logic parallels philosophical three-valued logic systems.

Recognizing this connection explains why SQL treats NULL comparisons differently and helps understand the logic behind unknown values.

Common Pitfalls

#1 Using '=' to check for NULL values.

Wrong approach:

SELECT * FROM users WHERE age = NULL;

Correct approach:

SELECT * FROM users WHERE age IS NULL;

Root cause:

Misunderstanding that NULL is not a value but a marker for unknown, so '=' does not work with NULL.

#2 Assuming BETWEEN excludes boundary values.

Wrong approach:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; -- expecting dates after Jan 1 and before Jan 31

Correct approach:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2023-01-31'; -- includes Jan 1 and Jan 31

Root cause:

Not knowing BETWEEN includes both start and end values, causing off-by-one errors.

#3 Using functions on columns in comparisons preventing index use.

Wrong approach:

SELECT * FROM products WHERE LOWER(name) = 'apple';

Correct approach:

CREATE INDEX idx_name_lower ON products (LOWER(name));
SELECT * FROM products WHERE LOWER(name) = 'apple';

Root cause:

Applying functions in WHERE clauses without supporting indexes causes full table scans and slow queries.

Key Takeaways

Standard comparison operators let you ask simple questions about data, like equality and order, to filter and sort results.

NULL values behave specially in comparisons, requiring IS NULL or IS NOT NULL to check for missing data correctly.

BETWEEN is a convenient way to check if a value lies within an inclusive range, including the boundary values.

SQL uses three-valued logic (true, false, unknown) which affects how comparisons with NULL behave in queries.

Understanding how comparison operators interact with indexes is key to writing fast and efficient database queries.