title: How to rewrite UNION with LEFT JOIN more efficiently
body: <p>I have two tables...one that registers users and one that checks in users.  A user will always have a  single entry in the register table but a user may have 0 or multiple entries in the checkin table.  For a raffle selector, I wrote a query that is picking 1 entry from the register table and then 1 entry from the checkin table - each sub query picks a random entry so long as that userID does not exist in a 3rd table that stores the raffle winners.  After the two entries are returned than it randomly selects one of the two returned entries as the winnner.</p>
  <p>However, I believe there should be a more efficient way of writing this so its ONLY picking an entry once....not picking two entries and then picking one of the two.</p>
  <p>It took me quite a while to figure out how to correctly write the below query as I am not proficient in mysql at all.  The query works and seems to work efficiently, but I believe there should be a better way of writing it that also consolidates the amount of query code.</p>
  <p>Hoping someone here can help or advise.</p>
  <p>Table note: clubusers/clubHistory have multiple overlapping columns but the tables are not the same:</p>
  <pre><code>register = clubUsers 
  checkins = clubHistory
  winners = clubRaffleWinners
  
  SELECT * FROM (  
    (SELECT ch.user_ID,ch.clID FROM clubHistory AS ch 
      LEFT OUTER JOIN clubRaffleWinners AS cr1 ON 
        ch.user_ID=cr1.user_ID  
        AND cr1.cID=1157 
        AND cr1.rafID=18  
        AND cr1.crID=1001   
        AND cr1.ceID=1167  
        AND cr1.chDate1='2022-06-04' 
      WHERE  
        ch.cID=1157  
        AND ch.crID=1001   
        AND ch.ceID=1167 
        AND ch.chDate='2022-06-04' 
        AND cr1.user_ID IS NULL 
      GROUP BY ch.user_ID ORDER BY RAND() LIMIT 1 
    )
  UNION
    (SELECT cu.user_ID,cu.clID FROM clubUsers AS cu 
      LEFT OUTER JOIN clubRaffleWinners AS cr2 ON 
        cu.user_ID=cr2.user_ID  
        AND cr2.cID=1157 
        AND cr2.rafID=18  
        AND cr2.crID=1001   
        AND cr2.ceID=1167  
        AND cr2.chDate1='2022-06-04' 
      WHERE  
        cu.cID=1157  
        AND cu.crID=1001   
        AND cu.ceID=1167 
        AND cu.calDate&lt;='2022-06-04' 
        AND cr2.user_ID IS NULL 
      GROUP BY cu.user_ID ORDER BY RAND() LIMIT 1   
    )
  ) AS foo order by RAND() LIMIT 1 ;
  </code></pre>
  <h1>UPDATE:</h1>
  <p>As @JettoMartinez points out below, my current query could in fact randomly return the same user from each table so the final returned entry would just be the same user.  I didn't realize this in my struggles just to get the above query to work.  Thus my original OP asking for a more optimized query simply selecting a single random entry from both tables (where that user is not already in the winners table) is applicable for yet another reason.</p>