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.

Practice

1. Which of the following operators in PostgreSQL means "not equal to"?

easy

A. <=

B. =

C. <> or !=

D. >=

Solution

1. Step 1: Understand the meaning of operators

   The operator '=' means equal, so it cannot mean not equal.

2. Step 2: Identify the not equal operator

   In PostgreSQL, both '<>' and '!=' are used to mean not equal.

3. Final Answer:

   <> or != -> Option C

4. Quick Check:

   Not equal operator = <> or != [OK]

Hint: Remember: <> or != means not equal in SQL [OK]

Common Mistakes:

• Confusing '=' with not equal
• Using '<' or '>' as not equal
• Thinking '!=' is invalid in PostgreSQL

2. Which of the following is the correct syntax to check if column age is greater than or equal to 18 in PostgreSQL?

easy

A. age >> 18

B. age >= 18

C. age =< 18

D. age => 18

Solution

1. Step 1: Recall the correct operator for greater than or equal

   The correct operator is '>=' which means greater than or equal.

2. Step 2: Check each option's syntax

   age >= 18 uses '>=', which is correct. Options B and C use invalid operators '=>' and '=<' which are not valid in PostgreSQL. age >> 18 uses '>>' which is a bitwise operator, not comparison.

3. Final Answer:

   age >= 18 -> Option B

4. Quick Check:

   Greater or equal operator = >= [OK]

Hint: Use >= for greater or equal, not => or =< [OK]

Common Mistakes:

• Swapping operator symbols like => instead of >=
• Using bitwise operators instead of comparison
• Confusing <= and >= syntax

3. Given a table products with a column price, what will the following query return?

SELECT * FROM products WHERE price < 50;

medium

A. All products with price less than 50

B. All products with price greater than 50

C. All products with price equal to 50

D. Syntax error in query

Solution

1. Step 1: Understand the comparison operator used

   The operator '<' means less than, so the query filters rows where price is less than 50.

2. Step 2: Interpret the query result

   The query returns all rows from products where price is strictly less than 50.

3. Final Answer:

   All products with price less than 50 -> Option A

4. Quick Check:

   Operator '<' means less than [OK]

Hint: Remember: < means less than in SQL [OK]

Common Mistakes:

• Thinking '<' means less than or equal
• Confusing '<' with '>'
• Assuming syntax error due to operator

4. Identify the error in the following PostgreSQL query:

SELECT * FROM users WHERE age <> 30 AND age != 25;

medium

A. The comparison operators should be = instead of <> and !=

B. Using both <> and != together causes syntax error

C. The AND operator should be OR

D. No error; query is valid and works correctly

Solution

1. Step 1: Check the use of comparison operators

   Both '<>' and '!=' are valid not equal operators in PostgreSQL and can be used interchangeably.

2. Step 2: Analyze the logical operator usage

   The AND operator correctly combines two conditions requiring age to be not 30 and not 25 simultaneously.

3. Final Answer:

   No error; query is valid and works correctly -> Option D

4. Quick Check:

   Both <> and != are valid not equal operators [OK]

Hint: Both <> and != work for not equal in PostgreSQL [OK]

Common Mistakes:

• Assuming <> and != cannot be mixed
• Thinking AND should be OR without reason
• Confusing not equal with equal operator

5. You want to select all employees from a table employees whose salary is between 3000 and 7000 inclusive. Which of the following WHERE clauses correctly uses standard comparison operators to achieve this?

hard

A. salary >= 3000 AND salary <= 7000

B. salary > 3000 AND salary < 7000

C. salary = 3000 OR salary = 7000

D. salary <> 3000 AND salary <> 7000

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means salary can be equal to 3000 or 7000 or any value in between.

2. Step 2: Analyze each option's logic

   salary >= 3000 AND salary <= 7000 uses '>=' and '<=' which include the boundary values. salary > 3000 AND salary < 7000 excludes boundaries with '>' and '<'. salary = 3000 OR salary = 7000 only selects salaries exactly 3000 or 7000, missing values in between. salary <> 3000 AND salary <> 7000 excludes salaries equal to 3000 and 7000, opposite of requirement.

3. Final Answer:

   salary >= 3000 AND salary <= 7000 -> Option A

4. Quick Check:

   Use >= and <= for inclusive range [OK]

Hint: Use >= and <= for inclusive ranges in WHERE clause [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using not equal operators instead of range