How to JOIN two table to get missing rows in the second table

  • In a simple voting system as

    CREATE TABLE elections (
    election_id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(255),
    
    CREATE TABLE votes (
    election_id int(11),
    user_id int(11),
    FOREIGN KEYs
    

    for getting the list of elections a user has voted, the following JOIN is used

    SELECT * FROM elections
    JOIN votes USING(election_id)
    WHERE votes.user_id='x'
    

    but how to get the list of elections a user has NOT voted?

  • Sparr

    Sparr Correct answer

    7 years ago

    Use your existing query to get the opposite of the list you want. That list can then be checked against via NOT IN to get the desired list.

    SELECT * FROM elections WHERE election_id NOT IN (
        SELECT elections.election_id from elections
        JOIN votes USING(election_id)
        WHERE votes.user_id='x'
    )
    
  • Use an outer join:

    select e.election_id, e.title, v.user_id
    from Elections e
     LEFT OUTER JOIN votes v ON v.election_id = e.election_id and v.user_id = @userid
    

    The UserId will be empty if no votes have been cast for a particular election, otherwise it will show up

    If you only want to list the elections where there aren't any cast votes you might do it like this :

    select *
    from elections e
    where election_id NOT IN 
     (select election_id
      from votes
      where user_id = @userid
     )
    
  • There are a lot of ways to achieve what you are asking for. Perhaps the most straightforward way is to use a purely set-oriented approach:

    select election_id from elections
    minus -- except is used instead of minus by some vendors
    select election_id from votes where user_id = ?
    

    From the set of elections, we remove those where the user has voted. The result can be joined with elections to get the title of the elections. Even though you have not tagged your question, there is reason to believe that you are using MySQL, and MINUS or EXCEPT is not supported there.

    Another variant is to use the NOT EXISTS predicate:

    select election_id, title 
    from elections e
    where not exists (
        select 1 
        from votes v
        where e.election_id = v.election_id
          and v.user_id = ?
    );
    

    I.e. the election where it does not exists a vote from the user. The NOT IN predicate can be used in a similar fashion. Since there may be nulls involved it is worth noting that the semantics differs between IN and EXISTS.

    Finally, you can use an outer join

    select election_id, title 
    from elections e
    left join votes v
        on e.election_id = v.election_id
       and v.user_id = ?
    where v.user_id is null;
    

    If there are no rows that match the ON predicate's, all columns from votes is replaced with null in the result. We can, therefore, check if any column from votes is null in the WHERE clause. Since both columns in votes may be null you need to be careful.

    Ideally, you should fix your tables so that you don't have to deal with the gotchas caused by nulls:

    CREATE TABLE elections 
    ( election_id int NOT NULL AUTO_INCREMENT PRIMARY KEY
    , title varchar(255) not null );
    
    CREATE TABLE votes 
    ( election_id int not null
    , user_id int not null
    ,     constraint pk_votes primary key (election_id, user_id)
    ,     constraint fk_elections foreign key (election_id)
                                  references elections (election_id)
    );   
    
  • SELECT * 
    FROM elections 
    WHERE election_id NOT IN (
        SELECT DISTINCT(election_id) from votes
    );
    

    As the accepted answer pulled elections where a specific voter did not vote, this doesn't really answer the OP's question. And, of course, it's just a minor tweak of one of the other answers, to get elections where no one voted. A comment to that effect might make this seem like a slightly better answer. Still, from the picture, pretty good for a monkey! :-)

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM

Tags used