Help with creating a rather complex sql statement.

Question

Hey everyone, I wasn't really sure how to describe this in the form of a google search, so I've decided to come here and see if you guys can help. Basically, I want to know how I can translate the following pseudo-sql into an actual mysql query:

Select one random row 
  from `posts` 
 where (the user's id, which is supplied by the script) is not found in the `votes` table 
        with a post id of (the selected post's id).

Thank you so much if you can help.

Answer 1

Something like this:

SELECT *
FROM Posts
WHERE Posts.ID NOT IN (
    SELECT Votes.PostID FROM Votes WHERE Votes.UserID=@UserID
)
ORDER BY Rand()
LIMIT 1

Answer 2

SELECT p.* 
FROM `posts`
LEFT JOIN `votes` ON (posts.post_id=votes.post_id AND votes.user_id=@UserID)
WHERE votes.user_id is null
ORDER BY rand()
LIMIT 1;

From my experience, joins are significantly faster than subselects, especially in combination with the NOT IN clause

Answer 3

NOT IN

---

  SELECT p.*,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM POSTS p
    JOIN (SELECT MAX(t.id) - 1 'm_id'
            FROM POSTS t) x
   WHERE p.id NOT IN (SELECT v.postid 
                        FROM VOTES v 
                       WHERE v.userid = @UserID)
ORDER BY rand_ind
   LIMIT 1

LEFT JOIN/IS NULL

---

   SELECT p.*,
          FLOOR(1 + RAND() * x.m_id) 'rand_ind'
     FROM POSTS p
     JOIN (SELECT MAX(t.id) - 1 'm_id'
         FROM POSTS t) x
LEFT JOIN VOTES v ON v.postid = p.id
             AND v.userid = @UserID
    WHERE v.postid IS NULL
 ORDER BY rand_ind
    LIMIT 1

NOT EXISTS

---

  SELECT p.*,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM POSTS p
    JOIN (SELECT MAX(t.id) - 1 'm_id'
            FROM POSTS t) x
   WHERE NOT EXISTS(SELECT NULL 
                      FROM VOTES v 
                     WHERE v.userid = @UserID
                       AND v.post_id = p.id)
ORDER BY rand_ind
   LIMIT 1

Summary

---

1. LEFT JOIN/IS NULL and NOT IN are equally efficient; NOT EXISTS is less efficient.
2. ORDER BY RAND() is not recommended because it does not scale as the amount of data increases (graph). Related question.