CODEWITHSUNDEEP
mysqlsqldatabase
James Jeffery
James Jeffery

Reputation: 12579

Select all and excluding results. SQL join

I am struggling with MySQL joins at the moment.

I have 3 tables, users, gifts and users_gifts.

- Users
    - id
    - username
    - email
    - password

- Gifts
    - id
    - gift
    - value

- users_gifts
    - uid
    - gift_id

I want to return all gifts but do not include the gifts that the user has already sent. So, if the available gifts are:

card
heart
necklace
perfume

If the user has already sent a heart, then the only gifts that would return are

card
necklace
perfume

I have tried the following join, but for some reason it's not having the desired effect.

SELECT *
FROM   gifts
JOIN   users_gifts
ON     gifts.id = user_gifts.gift_id
WHERE  users_gifts.uid != 3

... assuming the user ID of the current user is 3.

Am I missing something here, or is there any error with my SQL?

Upvotes: 0

Views: 926

Answers (4)

JDB
JDB

Reputation: 25810

I recommend you change your approach.

Currently, you are trying to select all gifts sent by a user other than #3. In addition, you are using an inner join, meaning only gifts sent by a user other than #3 are included. Gifts which have never been sent by anyone are, therefore, excluded.

Instead, you should start with all gifts and then perform an outer join on user #3. This will return all gift columns and user columns if the user has sent the gift. If the user has not sent the gift, then user columns will simply contain NULL, but the row will still be included.

Here's an example of an outer join which will check the user columns to determine if a user has sent the gift. Only columns containin NULL are included in the final result, meaning the user has not sent the gift:

SELECT *
FROM gifts
LEFT JOIN users_gifts
  ON users_gifts.gift_id = gifts.id
  AND user_gifts.uid = 3 -- Whatever the User's ID is
WHERE users_gifts.gift_ID IS NULL -- exclude the gift if it's already been sent

This is an alternative approach using an EXISTS subquery:

SELECT *
FROM gifts
WHERE NOT Exists(
     SELECT 0
     FROM users_gifts
     WHERE users_gifts.gift_id = gifts.id
     AND user_gifts.uid = 3 -- Whatever the User's ID is
   )

Which method is faster, or whether one method is faster than the other, depends on your specific circumstances. According to this article, the NOT EXISTS approach is 30% slower, but this article suggests that NOT EXISTS is more performant than LEFT JOIN if the column contains NULLs. As Darius mentioned in the comments on his answer, "testing is the way to go."

Upvotes: 2

Darius X.
Darius X.

Reputation: 2937

Your query is looking for users other than #3.

Instead, you need to look in users_gifts, for user=3.

But, you want to exclude any gifts that exist in that sub-query:

SELECT * 
FROM   gifts 
WHERE NOT EXISTS
  (SELECT 1 
   FROM   users_gifts 
   WHERE  users_gifts.uid = 3
   AND    user_gifts.gift_id = gifts.id
  )

Upvotes: 4

meghamind
meghamind

Reputation: 96

SELECT *
FROM   gifts
WHERE  gifts.id NOT IN (SELECT gift_id
                        FROM   user_gifts
                        WHERE  uid = 3);

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You could use NOT EXISTS clause, which is not as performant as join, but more readable (in my opinion) for this kind of request.

select * from gifts g
where not exists (select null 
                  from user_gifts ug
                  where g.id= ug.gift_id
                  and ug.uid = 3)

Upvotes: 2

Related Questions