Sub-query to return single value example query

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.
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.