Dacramash February 2016

Select items that doesn't have a specific value in another table

I am trying to select items that doesn't have a specific value in another table, I was able to achieve the result that I wanted by using a subquery, however it's very slow so I am wondering if I could do it differently...

SELECT
    content.*,
    (SELECT views
     FROM content_views
     WHERE content_views.content = content.record_num
    ) as views
FROM content
RIGHT JOIN watch_log ON content.record_num = watch_log.content
WHERE content.enabled = 1
    AND 24 NOT IN
        (SELECT niche
         FROM content_niches
         WHERE content_niches.content = content.record_num
        )
ORDER BY content.encoded_date
DESC LIMIT 0,6

I tried using a LEFT OUTER JOIN, but couldn't get the same result...

SELECT
    content.*,
    (SELECT content_views.views
     FROM content_views
     WHERE content_views.content = content.record_num
    ) as views
FROM content
RIGHT JOIN watch_log ON content.record_num = watch_log.content
LEFT OUTER JOIN content_niches ON content.record_num = content_niches.content AND content_niches.niche = 24
WHERE content.enabled = 1
ORDER BY content.encoded_date
DESC LIMIT 0,6

Answers


Gordon Linoff February 2016

Mixing left and right outer joins is just confusing. In fact, right join isn't really needed. It can usually be replaced by left join. In your case, it can be replaced by inner join, because the where clause turns it into an inner join. So, how about:

SELECT c.*,
       (SELECT views
        FROM content_views cv
        WHERE cv.content =  c.record_num
       ) as views
FROM content c JOIN
     watch_log wl
     ON c.record_num = wl.content
WHERE c.enabled = 1 AND
      NOT EXISTS (SELECT 1
                  FROM content_niches cn
                  WHERE cn.content = c.record_num AND
                        cn.niche = 24
                 )
ORDER BY c.encoded_date DESC
LIMIT 0, 6;

For performance you want indexes: content(enabled, encoded_date, record_num), content_views(content, views), and content_niches(content, niche).

Notes:

  • Don't mix different types of outer joins, unless you really, really understand what they are doing.
  • Use table aliases that abbreviations of the table names. This makes queries easier to write and to read.
  • Whatever your preference for formatting, don't start a line in a query with DESC (or ASC); this is a modifier on ORDER BY.
  • NOT EXISTS is better than NOT IN. The former handles NULL values the way you would expect. The latter returns nothing if there are NULL values.

Post Status

Asked in February 2016
Viewed 2,955 times
Voted 10
Answered 1 times

Search




Leave an answer