Decorrelate a query using semi join

Question

I am new to query optimization,how to use semi join while implementing decorrelation I can't totally understand.

Consider the query

        SELECT A, B
        FROM r
        WHERE r.B < SOME (
            SELECT B
            FROM s
            WHERE s.A = r.A
       )

Show how to decorrelate the above query using the multi-set version of the semi-join operation

Answer 1

You may write your query using an inner join as follows:

SELECT DISTINCT r.A, r.B
FROM r
INNER JOIN s
    ON r.A = s.A
WHERE r.B < s.B;

The DISTINCT clause is necessary in this version of your query, because a given record in the r table could potentially join to more than one match in the s table. In your original version, there can't be duplicates, because of the SOME clause which take a set of records any always returns a single yes/no answer.