CODEWITHSUNDEEP
sqlmysql
David
David

Reputation: 728

MySQL - Select All Except what is in this Table

I am wanting to select all images that a user does not already have.

I have three tables: user, image, and user_image:

Upvotes: 1

Views: 1819

Answers (5)

OMG Ponies
OMG Ponies

Reputation: 332791

Using LEFT JOIN/IS NULL

   SELECT DISTINCT
          i.data
     FROM IMAGE i
     JOIN USER_IMAGE ui ON ui.image_id = i.id
LEFT JOIN USER u ON u.id = ui.user_id
                AND u.user = ?
    WHERE u.id IS NULL

Using NOT IN

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE ui.user_id NOT IN (SELECT u.id
                            FROM USER u
                           WHERE u.user = ?)

Using NOT EXISTS

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE NOT EXISTS(SELECT NULL
                    FROM USER u
                   WHERE u.id = ui.user_id
                     AND u.user = ?)

Performance:

The LEFT JOIN/IS NULL and NOT IN provide equivalent performance - the NOT EXISTS is ~30% less efficient. See this for more details.

Upvotes: 6

Martin
Martin

Reputation: 2971

select id, date from image where id not in (select image_id from user_image where user_id =

There is a faster way but this is easier to follow.

Another way would be:

select id, data from image left join user_image on user.id=user_image.user_id where user_image.id = null

Upvotes: 0

K Prime
K Prime

Reputation: 5849

Try:

SELECT
    i.*
FROM
    _image i
    LEFT JOIN _user_image u ON u.image_id = i.id
WHERE
    u.user_id != <user-id>

Upvotes: 0

Kaleb Brasee
Kaleb Brasee

Reputation: 51965

select * from _image where id not in (select image_id from _user_image where user_id = ?)

Upvotes: 0

pib
pib

Reputation: 3323

You can do it pretty easily with a subquery:

SELECT * FROM image WHERE id NOT IN 
    (SELECT image_id FROM user_image WHERE user_id = THE_USER_ID);

Upvotes: 0

Related Questions