Comparison operators in MySQL - Step-by-Step Execution

Concept Flow - Comparison operators

Start with two values

↓

Choose comparison operator

↓

Compare values

↓

Result: TRUE or FALSE

↓

Use result in query condition

Comparison operators compare two values and return TRUE or FALSE, which helps filter data in queries.

Execution Sample

MySQL

SELECT * FROM employees WHERE salary > 50000;

This query selects employees whose salary is greater than 50000.

Execution Table

Step	Employee ID	Salary	Condition (salary > 50000)	Result (Include Row)
1	101	48000	48000 > 50000	FALSE
2	102	52000	52000 > 50000	TRUE
3	103	50000	50000 > 50000	FALSE
4	104	75000	75000 > 50000	TRUE
5	105	30000	30000 > 50000	FALSE
Exit	-	-	No more rows	Query ends

💡 All rows checked; query ends after last row.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Current Employee ID	-	101	102	103	104	105	-
Current Salary	-	48000	52000	50000	75000	30000	-
Condition Result	-	FALSE	TRUE	FALSE	TRUE	FALSE	-

Key Moments - 2 Insights

Why does salary = 50000 return FALSE for the condition salary > 50000?

Because > means strictly greater than, so 50000 is not greater than 50000. See execution_table row 3.

What happens if the condition is FALSE for a row?

That row is not included in the query result. See execution_table rows 1, 3, and 5 where result is FALSE.

Visual Quiz - 3 Questions

Look at the execution_table, what is the condition result for Employee ID 104?

A TRUE

B FALSE

C NULL

D Error

💡 Hint

Check the row with Employee ID 104 in the execution_table under 'Condition Result'.

At which step does the condition salary > 50000 become FALSE for the first time?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the 'Condition Result' column in execution_table for the earliest FALSE.

If the operator changed to salary >= 50000, how would the result for Employee ID 103 change?

A It would remain FALSE

B It would become TRUE

C It would cause an error

D It would be NULL

💡 Hint

Check the difference between > and >= operators in the key_moments section.

Concept Snapshot

Comparison operators compare two values and return TRUE or FALSE.
Common operators: =, !=, <, >, <=, >=.
Used in WHERE clauses to filter rows.
Example: salary > 50000 selects rows with salary greater than 50000.
Equal (=) checks exact match; greater than (>) checks if left is bigger.
Results control which rows appear in query output.

Full Transcript

Comparison operators in SQL compare two values and return TRUE or FALSE. This helps decide which rows to include in query results. For example, the operator > means 'greater than'. If we write WHERE salary > 50000, only employees with salary more than 50000 are selected. In the example, employee with salary 48000 is excluded because 48000 is not greater than 50000. Employee with salary 52000 is included because 52000 is greater than 50000. The operator = means equal, and >= means greater than or equal. Understanding these operators helps filter data effectively.

Practice

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

easy

A. =

B. <>

C. >=

D. <

Solution

1. Step 1: Understand the meaning of comparison operators

   The operator "<>" is used in SQL to mean "not equal to".

2. Step 2: Compare with other operators

   "=" means equal, ">=" means greater than or equal, and "<" means less than.

3. Final Answer:

   <> -> Option B

4. Quick Check:

   Not equal operator = <> [OK]

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

Common Mistakes:

• Confusing = with not equal
• Using != which is valid but less common in MySQL
• Mixing up > and < operators

2. Which of the following is the correct syntax to check if column age is greater than 18 in a WHERE clause?

easy

A. WHERE age > 18

B. WHERE age => 18

C. WHERE age =< 18

D. WHERE age <> 18

Solution

1. Step 1: Identify the correct greater than operator

   The correct operator for "greater than" is ">". The operator ">=" means "greater than or equal to".

2. Step 2: Check syntax correctness

   "=>" and "=<" are invalid operators in SQL syntax. "<>" means not equal, which is not what we want.

3. Final Answer:

   WHERE age > 18 -> Option A

4. Quick Check:

   Greater than operator is > [OK]

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

Common Mistakes:

• Using => or =< which are invalid
• Confusing <> with greater than
• Using = instead of >

3. Given the table Employees with a column salary, what will the query SELECT * FROM Employees WHERE salary <= 50000; return?

medium

A. All employees with salary less than or equal to 50000

B. All employees with salary exactly 50000

C. All employees with salary less than 50000

D. All employees with salary greater than 50000

Solution

1. Step 1: Understand the meaning of <= operator

   The operator <= means "less than or equal to".

2. Step 2: Interpret the query condition

   The query filters employees whose salary is either less than 50000 or exactly 50000.

3. Final Answer:

   All employees with salary less than or equal to 50000 -> Option A

4. Quick Check:

   <= means less than or equal [OK]

Hint: <= means less than or equal, includes the number [OK]

Common Mistakes:

• Thinking <= means only less than
• Confusing <= with <
• Ignoring the equal part of <= operator

4. Identify the error in this MySQL query: SELECT * FROM Products WHERE price ! 100;

medium

A. Missing comparison operator after !

B. No error, query is correct

C. Missing quotes around 100

D. Incorrect use of ! for not equal, should be != or <>

Solution

1. Step 1: Analyze the use of ! operator

   In MySQL, "!" alone is not a valid comparison operator. For "not equal", use "!=" or "<>".

2. Step 2: Check the query syntax

   The query uses "price ! 100" which is invalid syntax and will cause an error.

3. Final Answer:

   Incorrect use of ! for not equal, should be != or <> -> Option D

4. Quick Check:

   Use != or <> for not equal [OK]

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

Common Mistakes:

• Using ! alone as not equal
• Forgetting to use != or <>
• Assuming ! means not equal in SQL

5. You want to select all customers whose age is between 20 and 30 inclusive. Which WHERE clause correctly uses comparison operators?

hard

A. WHERE age > 20 AND age < 30

B. WHERE age = 20 OR age = 30

C. WHERE age >= 20 AND age <= 30

D. WHERE age <> 20 AND age <> 30

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means including 20 and 30, so use >= and <= operators.

2. Step 2: Analyze each option

   WHERE age > 20 AND age < 30 excludes 20 and 30 (strictly greater and less). WHERE age = 20 OR age = 30 only selects exactly 20 or 30, not the range. WHERE age <> 20 AND age <> 30 excludes 20 and 30, opposite of requirement.

3. Final Answer:

   WHERE age >= 20 AND age <= 30 -> Option C

4. Quick Check:

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

Hint: Use >= and <= for inclusive range checks [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using <> which excludes values