Inexplicable inner query

Question

Hi,

I have a nested SQL query that is exhibiting results which I can't understand. The query joins the PARTNER and USER tables via the PARTNER_USER table. A partner is basically a collection of users, and the objective of this query is to figure out when the 20th user registered with the partner that has ID 34:

select p.partner_id id,
       u.created_on launch_date
from   user u join partner_user pu
using (user_id) join partner p
using (partner_id)
where  p.partner_id = 34
and    u.user_id     =
       (select  nu.user_id
       from     user nu
       join     partner_user npu using (user_id)
       join     partner np using (partner_id)
       where    np.partner_id = 34
       order by nu.created_on limit 19, 1)

However, if I change the 2nd last line to

       where    np.partner_id = p.partner_id

The query fails with the error message "Subquery returns more than 1 row". Why does the first query work, but not the second? They look equivalent to me.

Thanks, Don

Answer 1

Whe you use the = operator to compare with the results of a subquery, your subquery may return only a single row. if you want to check for all rows that are returned by the subquery, you have to use the IN operator.

AND u.User_Id IN ( SELECT .... )

Answer 2

When you change the where clause in the sub query you're letting the floodgates open. The where clause in the main query doesn't restrict the subquery. So you're getting more than 1 result.

EDIT: What database is this? I've not come across the "using" construct before...

Answer 3

JPunyon is right. One or the other query has to run first, and then have its results trimmed after the fact.

If you look at the queries as written, the outer query has to know the result of the inner query to apply its where clause. However, when you specify

where    np.partner_id = p.partner_id

in the inner query, then you're trying to make the inner query know the result of the outer query to apply its where clause as well. That's a circular dependency.

As a human, you can read the query and you can tell that in this particular case, you're asking for one particular value in the where clause in the outer query and you're asking to use that same value in the inner query, so it seems as though the database should see that and use the same literal value from the outer query.

In reality, the inner query is simply run first without knowing the possible values of p.partner_id, hence the "multiple rows" error.

Answer 4

@Jason Punyon mysql supports the USING construct.