What does the answer key mean? (SQL)

Q&A DQ Courses
, ,
#1

Screen Link:

Portion of Answer Key:

    country_best_customer AS
        (
         SELECT
            cmp.country,
            cmp.max_purchase,
            (
             SELECT ccp.customer_id
             FROM customer_country_purchases ccp
             WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases
            ) customer_id
         FROM country_max_purchase cmp
        )

Question:
I would like to clarify if this same line of code

            SELECT ccp.customer_id
             FROM customer_country_purchases ccp
             WHERE ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases
            ) customer_id
         FROM country_max_purchase cmp

Does this equate to ccp and cmp doing an INNER JOIN on ccp.country = cmp.country AND cmp.max_purchase = ccp.total_purchases? But why didn’t the author do an INNER JOIN instead?

Thanks for the help!

1 Like
#2

It’s a correlated subquery, not an inner join.
For each value of outside query, the inside query is run.

Answered here for the guided project using the same technique.

Here’s a more complex article on how queries can be written differently to hack through sql syntax limitations: https://www.sqlteam.com/articles/aggregating-correlated-sub-queries