Why does the correlated subquery work and simple subquery doesn't?-CodePudding

Maybe I'm being stupid here but I dont understand why in the below example the correlated subquery works but the simple subquery doesnt. Can someone explain why please.

In the sample database im working with the authors are listed in the authors table and the title_authors table gives information on if the authors have written or co written any books. au_id is the PK in authors and part of a composite PK in title_authors. if the royalty_share column is set to '1' that means the author was the sole author on a book.

How come the correlated subquery is able to give me the authors who have '1' in the royalty_share column, but the simple subquery just gives me all the authors regardless of what is in the royalty_share column?

books=> select au_fname, au_lname from authors a where 1 in (select royalty_share from title_authors ta where a.au_id = ta.au_id);
 au_fname  | au_lname
----------- -----------
 Sarah     | Buchman
 Wendy     | Heydemark
 Klee      | Hull
 Christian | Kells
           | Kellsey

books=> select au_fname, au_lname from authors a where 1 in (select royalty_share from title_authors);
 au_fname  |  au_lname
----------- -------------
 Sarah     | Buchman
 Wendy     | Heydemark
 Hallie    | Hull
 Klee      | Hull
 Christian | Kells
           | Kellsey
 Paddy     | O'Furniture

CodePudding user response：

Consider this criteria outside the context of the rest of the query:

where 1 in (select royalty_share from title_authors)

This condition would be true regardless of anything else in your query, as long as there is at least one title_authors row with royalty_share set to 1 for any author. That is why your second query returns all authors.

When the subquery is correlated, the condition changes to restrict the rows in title_authors to the author from the "outer" table, which is what you are looking for.