What is the use of comparison operators with MySQL subquery?

The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. MySQL then compares the value that results from the subquery with the value on the other side of the comparison operator. MySQL subquery can be used before or after any of the comparison operators like =, >, >=, <, <=, !=, <>, <=>. Following is the example in which we are using a subquery with a < comparison operator.

Example

mysql> SELECT * from Cars WHERE Price < (SELECT AVG(Price) FROM Cars);
 +------+--------------+---------+
| ID   | Name         | Price   |
+------+--------------+---------+
| 1    | Nexa         | 750000  |
| 2    | Maruti Swift | 450000  |
| 5    | Alto         | 250000  |
| 6    | Skoda        | 1250000 |
| 8    | Ford         | 1100000 |
+------+--------------+---------+
5 rows in set (0.00 sec)

Similarly, the subquery can be used with other comparison operators too.

Ankith Reddy

Published on 21-Feb-2018 13:20:19

• Related Questions & Answers
• What is the use of ‘ALL’, ‘ANY’, ’SOME’, ’IN’ operators with MySQL subquery?
• What is the concept of a derived table concerned with MySQL subquery?
• What is the use of MySQL BINARY keyword while performing string comparison?
• Python Comparison Operators
• What are Comparison Operators in JavaScript?
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
• What is the use of WITH ROLLUP modifier in MySQL?
• Chaining comparison operators in C#
• Chaining comparison operators in Python
• How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
• How can we filter data with the help of MySQL subquery?
• What is Practical Use of Reversed Set Operators in Python?
• What is the use of MySQL FROM_UNIXTIME() function?