Comparison operators in MySQL

Introduction

Comparison operators help you check if values are equal, bigger, smaller, or different. This lets you find specific data in your database.

Finding all customers older than 30 years.

Checking if a product price is less than 100 dollars.

Selecting orders that were made on a specific date.

Filtering employees who are not in a certain department.

Comparing two columns to find matching records.

Syntax

MySQL

value1 = value2
value1 <> value2
value1 != value2
value1 > value2
value1 < value2
value1 >= value2
value1 <= value2

= means equal to.

<> and != both mean not equal to.

Examples

Selects employees who are exactly 25 years old.

MySQL

SELECT * FROM employees WHERE age = 25;

Finds products with a price greater than 50.

MySQL

SELECT * FROM products WHERE price > 50;

Gets orders that are not shipped yet.

MySQL

SELECT * FROM orders WHERE status <> 'shipped';

Finds users who signed up on or before January 1, 2023.

MySQL

SELECT * FROM users WHERE signup_date <= '2023-01-01';

Sample Program

This creates a table of employees, adds three people, and then selects those older than 28.

MySQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  age INT
);

INSERT INTO employees (id, name, age) VALUES
(1, 'Alice', 30),
(2, 'Bob', 25),
(3, 'Charlie', 35);

SELECT name FROM employees WHERE age > 28;

Important Notes

Use single quotes for text values in comparisons.

Comparison operators work with numbers, text, and dates.

Remember that = checks for equality, while <> and != check for inequality.

Summary

Comparison operators let you compare values to filter data.

Common operators include =, <>, !=, >, <, >=, and <=.

They are essential for searching and sorting data in databases.

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