SQL Operators and Clauses in MySQL

In the previous articles, you learned how to retrieve data using the SELECT statement and perform basic database operations.

To make SQL queries more powerful and flexible, MySQL provides operators and clauses that help filter, organise, and control how data is retrieved.

Using operators and clauses together allows you to:

• Filter rows based on conditions

• Match patterns in text

• Check value ranges

• Handle NULL values

• Combine multiple conditions

• Sort and group results

All examples below work in MySQL.

SQL Operators in MySQL

Operators are symbols or keywords used to perform comparisons or logical operations in SQL queries.

They are commonly used with the WHERE clause to filter records.

1. Comparison Operators

Comparison operators compare values in database columns.

Example:

SELECT * FROM Students WHERE age > 20; 

This query returns students whose age is greater than 20.

Another example:

SELECT * FROM Students WHERE name = 'Rahul'; 

2. Logical Operators (AND, OR, NOT)

Logical operators allow you to combine multiple conditions.

AND

Returns rows only if both conditions are true.

SELECT * FROM Students WHERE age > 18 AND city = 'Delhi'; 

OR

Returns rows if at least one condition is true.

SELECT * FROM Students WHERE city = 'Delhi' OR city = 'Mumbai'; 

NOT

Reverses a condition.

SELECT * FROM Students WHERE NOT city = 'Delhi'; 

This returns students who are not from Delhi.

3. BETWEEN – Checking Ranges

The BETWEEN operator checks whether a value lies within a specified range.

Syntax:

column BETWEEN value1 AND value2 

Example:

SELECT * FROM Students WHERE age BETWEEN 18 AND 22; 

This includes both 18 and 22 since BETWEEN is inclusive.

Equivalent to:

WHERE age >= 18 AND age <= 22 

4. IN – Matching Multiple Values

The IN operator allows you to check if a value matches any value in a list.

Example:

SELECT * FROM Students WHERE city IN ('Delhi', 'Mumbai', 'Pune'); 

Instead of writing:

WHERE city = 'Delhi' OR city = 'Mumbai' OR city = 'Pune' 

IN makes queries cleaner and easier to read.

5. LIKE – Pattern Matching

The LIKE operator is used to search for patterns in text values.

It works with wildcards.

Example: Names starting with A

SELECT * FROM Students WHERE name LIKE 'A%'; 

Example: Names ending with n

SELECT * FROM Students WHERE name LIKE '%n'; 

Example: Exactly five-letter names

SELECT * FROM Students WHERE name LIKE '_____'; 

6. IS NULL and IS NOT NULL

In SQL, NULL represents a missing or unknown value.

Important: You cannot use it = to check NULL.

❌ Incorrect

WHERE age = NULL 

✅ Correct

SELECT * FROM Students WHERE age IS NULL; 

Example:

SELECT * FROM Students WHERE age IS NOT NULL; 

7. EXISTS, Operator

The EXISTS operator checks whether a subquery returns any records.

It returns TRUE if the subquery produces at least one row.

Example:

SELECT name FROM Customers WHERE EXISTS ( SELECT * FROM Orders WHERE Orders.customer_id = Customers.customer_id ); 

This query returns customers who have placed at least one order.

The EXISTS operator is commonly used when working with subqueries and relationships between tables.

8. ANY Operator

The ANY operator compares a value with any value returned by a subquery.

Example:

SELECT name FROM Students WHERE age > ANY ( SELECT age FROM Students WHERE city = 'Delhi' ); 

This returns students whose age is greater than that of at least one student from Delhi.

9. ALL Operator

The ALL operator compares a value with all values returned by a subquery.

Example:

SELECT name FROM Students WHERE age > ALL ( SELECT age FROM Students WHERE city = 'Delhi' ); 

This returns students whose age is greater than that of every student from Delhi.

SQL Clauses in MySQL

Clauses define how the SQL query retrieves and organises data.

They are used with the SELECT statement.

1. WHERE Clause

The WHERE clause filters rows based on conditions.

Example:

SELECT * FROM Students WHERE age > 20; 

2. ORDER BY Clause

The ORDER BY clause sorts the result set.

Example:

SELECT * FROM Students ORDER BY age ASC; 

Sorting options:

• ASC → ascending order

• DESC → descending order

Example:

SELECT * FROM Students ORDER BY age DESC; 

3. LIMIT Clause

The LIMIT clause restricts the number of rows returned.

Example:

SELECT * FROM Students LIMIT 5; 

4. GROUP BY Clause

The GROUP BY clause groups rows that share the same values.

Example:

SELECT city, COUNT(*) FROM Students GROUP BY city; 

5. HAVING Clause

The HAVING clause filters grouped results.

Example:

SELECT city, COUNT(*) FROM Students GROUP BY city HAVING COUNT(*) > 2; 

Combining Operators and Clauses

Operators and clauses are often used together.

Example:

SELECT * FROM Students WHERE age BETWEEN 18 AND 25 AND city IN ('Delhi', 'Mumbai') AND name LIKE 'A%' ORDER BY age; 

This query returns students who:

• Are between 18 and 25

• Belong to Delhi or Mumbai

• Have names starting with A

Order of Execution

When combining conditions:

• AND has higher priority than OR

Example:

WHERE age > 18 OR city = 'Delhi' AND gender = 'F' 

To avoid confusion, use parentheses:

WHERE (age > 18 OR city = 'Delhi') AND gender = 'F' 

Common Beginner Mistakes

• Forgetting quotes for text values

• Using = NULL instead of IS NULL

• Writing long OR chains instead of using IN

• Not using parentheses in complex conditions.

Final Summary

MySQL operators and clauses make SQL queries powerful and flexible.

Important operators include:

• Comparison operators (=, >, <, etc.)

• Logical operators (AND, OR, NOT)

• BETWEEN

• IN

• LIKE

• IS NULL

• EXISTS

• ANY

• ALL

Important clauses include:

• WHERE

• ORDER BY

• GROUP BY

• HAVING

• LIMIT

Using these features together allows developers to filter, organise, and analyze data efficiently in MySQL queries.