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

Question

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?

Answer 1

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'
)

Answer 2

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
 )

Answer 3

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)
);   

Answer 4

SELECT * 
FROM elections 
WHERE election_id NOT IN (
    SELECT DISTINCT(election_id) from votes
);