MySQL Left Outer Join, Exclude Items in Second Table Belonging to User

Question

I have two tables in my MySQL database, one is a library of all of the books in the database, and the other is containing individual rows corresponding to which books are in a user's library.

For example:

Library Table

 `id`        `title`...
=====        ===========
  1          Moby Dick
  2          Harry Potter

Collection Table

 `id`        `user`      `book`
=====        ======      =======
  1            1            2
  2            2            2
  3            1            1

What I want to do is run a query that will show all the books that are not in a user's collection. I can run this query to show all the books not in any user's collection:

SELECT * 
FROM  `library` 
LEFT OUTER JOIN  `collection` ON  `library`.`id` =  `collection`.`book` 
WHERE  `collection`.`book` IS NULL

This works just fine as far as I can tell. Running this in PHPMyAdmin will result in all of the books that aren't in the collection table.

However, how do I restrict that to a certain user? For example, with the above dummy data, I want book 1 to result if user 2 runs the query, and no books if user 1 runs the query.

Just adding a AND user=[id] doesn't work, and with my extremely limited knowledge of JOIN statements I'm not getting anywhere really.

Also, the ID of the results being returned (of query shown, which doesn't do what I want but does function) is 0-- how do I make sure the ID returned is that of library.id?

please show the actual table structure and give us the error (if any) you're encountering. Your query mentions a field called "article" that's not in your table descriptions. — bpeterson76, Jun 15 '12 at 21:18
Oops-- I left it as article when I copied and pasted. It should be "book". I've changed the question. — Andrew M, Jun 15 '12 at 21:20

Zane Bien · Accepted Answer · 2012-06-15T21:38:01.290

15

You'll have to narrow down your LEFT JOIN selection to only the books that a particular user has, then whatever is NULL in the joined table will be rows(books) for which the user does not have in his/her collection:

SELECT
    a.id,
    a.title
FROM
    library a
LEFT JOIN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    ) b ON a.id = b.book
WHERE 
    b.book IS NULL

An alternative is:

SELECT
    a.id,
    a.title
FROM
    library a
WHERE 
    a.id NOT IN
    (
        SELECT book
        FROM collection
        WHERE user = <userid>
    )

However, the first solution is more optimal as MySQL will execute the NOT IN subquery once for each row rather than just once for the whole query. Intuitively, you would expect MySQL to execute the subquery once and use it as a list, but MySQL is not smart enough to distinguish between correlated and non-correlated subqueries.

As stated here:

"The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery."

edited Jun 15 '12 at 21:38

answered Jun 15 '12 at 21:20

Zane Bien

22,685
6
45
57

Thank you! That worked perfectly. This `JOIN` stuff, for whatever reason, is hard for me to wrap my head around... – Andrew M Jun 15 '12 at 21:28
2

Your last paragraph points to a link where `IN` subqueries are mentioned and not `NOT IN` subqueries. So, what you imply, that queries with `NOT IN` are not well optimized is wrong. See this great blog post: [NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) – ypercubeᵀᴹ Jun 15 '12 at 21:46
I believe it does not matter whether it's `NOT IN` or `IN`. The `NOT` just simply inverses the boolean returned from `IN`. Interesting article though +1 – Zane Bien Jun 15 '12 at 22:00
@ZaneBien, The article claimed that the run took the same amount of time. I think it's an optimization [specific to MySQL](http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table#comment-12936368). Very likely it looks at the query at a conceptual level and realize that it could have been rewritten as a join. – Pacerier Apr 10 '15 at 14:11

score 4 · Answer 2 · answered Jun 15 '12 at 21:30

4

How about this? It's just off the top of my head - I don't have access to a database to test on right now. (sorry)

SELECT 
   * 
FROM 
   library lib 
WHERE 
   lib.id NOT IN (
      SELECT 
         book 
      FROM 
         collection coll 
      WHERE 
         coll.user =[id]
   )
;

answered Jun 15 '12 at 21:30

Joe Dyndale

1,073
1
15
32

Ah... I see Andrew M beat me to it :O) – Joe Dyndale Jun 15 '12 at 21:31
1

Thanks for the +1. I sorely need those, being completely new here :O) – Joe Dyndale Jun 15 '12 at 21:48

MySQL Left Outer Join, Exclude Items in Second Table Belonging to User

2 Answers2