Standard comparison operators in PostgreSQL - Time & Space Complexity

Time Complexity: Standard comparison operators

O(n)

Understanding Time Complexity

When using standard comparison operators in SQL, it is important to understand how the time to run queries changes as the data grows.

We want to know how the number of comparisons affects the total work done.

Scenario Under Consideration

Analyze the time complexity of the following SQL query using comparison operators.

SELECT *
FROM employees
WHERE salary > 50000;
    

This query selects all employees whose salary is greater than 50,000.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database compares the salary of each employee to 50,000.
• How many times: Once for each employee row in the table.

How Execution Grows With Input

As the number of employees grows, the number of comparisons grows at the same rate.

Input Size (n)	Approx. Operations
10	10 comparisons
100	100 comparisons
1000	1000 comparisons

Pattern observation: The work grows directly in proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows to check.

Common Mistake

[X] Wrong: "The comparison only happens once regardless of table size."

[OK] Correct: Each row must be checked individually, so the number of comparisons grows with the number of rows.

Interview Connect

Understanding how comparison operations scale helps you explain query performance clearly and shows you know how databases handle filtering.

Self-Check

"What if the query used an index on salary? How would the time complexity change?"

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