In my previous posts already I told, sub-query you can use to get single value and multiple values. When Subquery to return only one value so that you can compare the values in one column with a single subquery value. In these cases, you can use comparison operators.
The comparison operators are <,>, =, >=<,<= and <>
In addition, you’re not limited to numbers. Character strings can also be compared in comparison predicates.
Suppose you want to retrieve data from the RETAIL_PRICES table. You want the R_PRICE values to equal the maximum price listed in the S_PRICE column of the SALES_PRICES table. The following query allows you to return the necessary data:
Listen Audio for explanation:
You do not have to use an aggregate function (such as MAX) to return a single value in a subquery. For example, the subquery’s WHERE clause might include a condition that will return only one value. The important point to remember is that you must be sure that your subquery returns only one value; otherwise, you will receive an error when using a comparison operator.
Also read: Top 238 complex SQL queries for practice
Advertisements
