Questions

15 of 30

1What are functions and operators in MySQL, and how are they different?

2What is the difference between single-row functions and aggregate functions?

3Give examples of commonly used string functions in MySQL.

4What is the use of the CONCAT() function? How is it different from using ||?

5Explain the difference between NOW(), CURDATE(), and SYSDATE().

6What are arithmetic operators in MySQL? Give examples.

7What is the difference between the = and <=> operators?

8What does the DISTINCT keyword do when used with aggregate functions like COUNT()?

9What is the difference between IFNULL() and COALESCE() functions?

10What are logical operators in MySQL, and how do AND, OR, and NOT work?

11What is the difference between LENGTH() and CHAR_LENGTH() functions?

12How does MySQL handle type conversion when using operators on different data types?

13What is the difference between ROUND(), TRUNCATE(), and FORMAT() functions?

14Explain how CASE and IF() functions can be used for conditional logic.

15What are comparison operators in MySQL, and how are they used with NULL values?

16How do aggregate functions like AVG(), SUM(), and MAX() behave when NULLs are present?

17Explain the use of REGEXP and LIKE operators. What’s the difference between them?

18What are user-defined functions (UDFs), and how do they differ from stored procedures?

19What are the differences between DATE_ADD() and ADDDATE() functions?

20How can you use STR_TO_DATE() and DATE_FORMAT() for converting and displaying date values?

21How does MySQL internally optimize and cache results of deterministic functions?

22What are window functions in MySQL 8.0, and how do they differ from aggregate functions?

23Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() window functions.

24How does MySQL evaluate operator precedence when multiple operators are used in a single expression?

25Can functions be used in the WHERE clause? What are the performance implications?

26How can you use JSON_EXTRACT() and JSON_CONTAINS() to work with JSON data in MySQL?

27What are the performance trade-offs of using scalar functions inside JOIN or GROUP BY clauses?

28Explain how collation affects comparison operators in string functions.

29What are deterministic and non-deterministic functions? How does this affect replication and indexes?

30How would you combine multiple functions and operators to clean, transform, and aggregate data efficiently in one query?

mysql

Functions and Operators

15 / 30

What are comparison operators in MySQL, and how are they used with NULL values?

Comparison Operators in MySQL and Their Behavior with NULL

Comparison operators in MySQL are used to compare two values and return TRUE (1), FALSE (0), or NULL (unknown). When NULL values are involved, comparisons behave differently because NULL represents an unknown value.

1. Common Comparison Operators

1
= (equal to)
2
!= or <> (not equal to)
3
> (greater than)
4
< (less than)
5
>= (greater or equal)
6
<= (less or equal)
7
BETWEEN
8
IN / NOT IN
9
LIKE

Normally, comparison operators return TRUE or FALSE — except when NULL is involved.

2. How NULL Affects Comparisons

1
Any comparison with NULL returns NULL (unknown).
2
This includes expressions like: 5 = NULL, NULL <> 10, NULL > 0.
3
Even NULL = NULL returns NULL (because NULL means unknown, so two unknowns cannot be equal).

Examples of NULL Comparisons

3. The NULL-Safe Equality Operator (<=>)

1
<=> is MySQL's NULL-safe comparison operator.
2
Returns 1 if both values are equal — including when both are NULL.
3
Returns 0 otherwise.

NULL-Safe Comparison Example

4. NULL with IN / NOT IN

1
IN ignores NULL values in the list.
2
NOT IN returns NULL if the list contains a NULL — causing unexpected results.

NULL with NOT IN

Key Points

1
NULL means unknown, so comparisons involving NULL result in NULL.
2
Use <=> for NULL-safe comparisons.
3
Beware of NULL inside NOT IN — it can make the whole condition NULL.
4
NULL does not behave like 0 or empty string.

In summary: MySQL's comparison operators work normally unless NULL is present. When NULL appears, the result becomes NULL unless you use the NULL-safe operator <=>.