从Table1中选择一个随机行，其中表2中不存在id

Question

I'm trying to select one (1) random row from Table1 where the Data_ID from Table1 does not exist in Table2.

I'm building a site there you can vote on posts (images) and for this page you should only see posts that does not have any votes on it.

Here is a simple structure of my two tables:

CREATE TABLE IF NOT EXISTS `Table1` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `Data_ID` varchar(10) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`,`Data_ID`),
  UNIQUE KEY `Data_ID` (`Data_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4389 ;


CREATE TABLE IF NOT EXISTS `Table2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Data_ID` varchar(10) NOT NULL,
  `IP` varchar(20) NOT NULL,
  `Vote_ID` int(4) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2314 ;

Thanks!

EDIT:

Here is my try but I don't really get random posts.

SELECT newTable.*
FROM (    
      SELECT Table1.Id, Table1.Data_ID FROM Table1
      LEFT OUTER JOIN Table2
      ON Table1.Data_ID = Table2.Data_ID
      WHERE Table2.Data_ID IS null
     ) as newTable
WHERE newTable.Id >= ROUND( RAND() * (SELECT MAX(Id) FROM Table1)) LIMIT 1;

Answer 1

You complicating things too much: subquery with EXISTS will be enough.

SELECT
    Table1.Id,
    Table1.Data_ID
FROM
    Table1
WHERE
    NOT EXISTS(
        SELECT * FROM Table2 WHERE Table2.Data_ID = Table1.Data_ID
    )
ORDER BY
    RAND()
LIMIT
    1;

Here is a DESCRIBE results:

******************** 1. row *********************
           id: 1
  select_type: PRIMARY
        table: Table1
         type: index
possible_keys: 
          key: Data_ID
      key_len: 32
          ref: 
         rows: 1
        Extra: Using where; Using index; Using temporary; Using filesort
******************** 2. row *********************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: Table2
         type: ALL
possible_keys: 
          key:
      key_len: 
          ref: 
         rows: 1
        Extra: Using where

---

UPDv1: There is another fast way, if you like JOIN fashion:

SELECT
    Table1.Id,
    Table1.Data_ID
FROM
    Table1 LEFT OUTER JOIN Table2 USING(Data_ID)
WHERE
    Table2.Id IS NULL
ORDER BY
    RAND()
LIMIT
    1;

DESCRIBE result:

******************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: Table1
         type: index
possible_keys: 
          key: Data_ID
      key_len: 32
          ref: 
         rows: 1
        Extra: Using index; Using temporary; Using filesort
******************** 2. row *********************
           id: 1
  select_type: SIMPLE
        table: Table2
         type: ALL
possible_keys: 
          key: 
      key_len: 
          ref: 
         rows: 1
        Extra: Using where; Not exists; Using join buffer (Block Nested Loop)