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 use of MySQL BINARY keyword while performing string comparison?
• What is the concept of a derived table concerned with MySQL subquery?
• What are Comparison Operators in JavaScript?
• Python Comparison Operators
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• What is the use of WITH ROLLUP modifier in MySQL?
• What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
• Chaining comparison operators in C#
• Chaining comparison operators in Python
• What is Practical Use of Reversed Set Operators in Python?
• What is the use of MySQL FROM_UNIXTIME() function?
• What is the use of MySQL GET_FORMAT() function?
• What is the use of NCHAR in MySQL?