Standard comparison operators in PostgreSQL - Step-by-Step Execution

Concept Flow - Standard comparison operators

Start Query

↓

Evaluate Left Operand

↓

Evaluate Right Operand

↓

Apply Comparison Operator

↓

Result: TRUE / FALSE / NULL

↓

Use Result in WHERE or Condition

The database evaluates both sides of the comparison, applies the operator, and returns TRUE, FALSE, or NULL to filter or decide query results.

Execution Sample

PostgreSQL

SELECT * FROM employees WHERE salary > 50000;

This query selects employees whose salary is greater than 50000.

Execution Table

Step	Left Operand	Operator	Right Operand	Evaluation	Result
1	salary = 45000	>	50000	45000 > 50000	FALSE
2	salary = 52000	>	50000	52000 > 50000	TRUE
3	salary = 50000	>	50000	50000 > 50000	FALSE
4	salary = NULL	>	50000	NULL > 50000	NULL
5	End of rows				Query returns rows where result is TRUE

💡 All rows evaluated; only rows with TRUE result are returned.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
salary	N/A	45000	52000	50000	NULL	N/A
comparison_result	N/A	FALSE	TRUE	FALSE	NULL	N/A

Key Moments - 2 Insights

Why does the comparison with NULL not return TRUE or FALSE?

In row 4 of the execution_table, comparing salary NULL with 50000 results in NULL because NULL means unknown, so the comparison cannot be TRUE or FALSE.

Why is salary = 50000 > 50000 FALSE and not TRUE?

In row 3, the operator '>' means strictly greater than, so 50000 > 50000 is FALSE because they are equal, not greater.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of comparing salary = 52000 with 50000 using '>'?

A TRUE

B FALSE

C NULL

D Error

💡 Hint

Check row 2 in the execution_table where salary is 52000.

At which step does the comparison result become NULL?

A Step 1

B Step 4

C Step 2

D Step 3

💡 Hint

Look for the row with salary NULL in the execution_table.

If the operator was changed from '>' to '>=' at step 3, what would be the result?

A FALSE

B NULL

C TRUE

D Error

💡 Hint

Consider that '>=' means greater than or equal, check step 3 where salary equals 50000.

Concept Snapshot

Standard comparison operators compare two values.
Common operators: =, <>, >, <, >=, <=.
Return TRUE, FALSE, or NULL (unknown).
Used in WHERE clauses to filter rows.
NULL comparisons yield NULL, not TRUE or FALSE.

Full Transcript

This visual execution shows how standard comparison operators work in PostgreSQL. The query selects employees with salary greater than 50000. Each row's salary is compared to 50000 using the '>' operator. The result is TRUE if salary is greater, FALSE if not, and NULL if salary is unknown (NULL). Only rows with TRUE results are returned. Important points include that NULL comparisons do not return TRUE or FALSE, and that '>' means strictly greater than, so equal values return FALSE.

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