In my previous posts already I told, sub-query returns single or multiple values.
If you want Subquery to return single value, then you need to use comparison operators.
The list is <,>, =, >=<,<= and <>
Both numerics and strings you can compare.
If you want to retrieve data from the RETAIL_PRICES table, when R_PRICE values equal to the maximum price listed in the S_PRICE column of the SALES_PRICES table, then below is the query.

For example, the subquery’s WHERE clause might include a condition that will return only one value.
The important point to remember is ensure your subquery returns single value.
Otherwise, you will receive an error when using a comparison operator.