CODEWITHSUNDEEP
mysqlsqljoinlogic
javic
javic

Reputation: 843

How to write this MySQL Query?

Suppose we have a table Users that has only one column UserId which is the primary key.

We have a second table Events that has three columns, EventId, UserId and Status. A Status is a BOOL NOT NULL. UserId is indexed but not unique.

The table Status has the foreign key constraint UserId on Users.UserId.

Now I want to query all the UserIds for which there is no row in Events such that Event.Status = TRUE. Is there a way to do this with JOIN? (One statement only preferred)

For example:

Users
--------
1
2
3

Events
--------
EventId   UserId  Status
1         1       FALSE
2         1       TRUE
3         2       FALSE
4         2       FALSE

Then the query I'm looking for should return:

UserId
-------
2
3

Thanks!

Upvotes: 2

Views: 321

Answers (2)

T.J. Crowder
T.J. Crowder

Reputation: 1074969

Surprisingly, a subselect is a reasonably efficient way to do this on MySQL. So:

SELECT UserId
FROM   Users
WHERE  UserId NOT IN (
       SELECT DISTINCT UserID
       FROM   Events
       WHERE  Status = TRUE
       )

Note that I'm using DISTINCT there, which may not be a perfect fit for the referenced article. Mind you, you may be fine with DISTINCT, or with leaving it off (but I don't know whether that would result in a problematic interim data set, or if MySQL is smart about it).

Or you can do the LEFT JOIN / IS NULL version, which doesn't have the DISTINCT issue:

SELECT    Users.UserId
FROM      Users
LEFT JOIN Events
          ON Events.UserId = Users.UserId AND Events.Status = TRUE
WHERE     Events.UserId IS NULL

See the link above for a discussion, but MySQL provides basically the same performance in both cases (whereas using NOT EXISTS rather than NOT IN would be markedly less efficient).

I'd try both (or all three, if you try both with and without DISTINCT on the first one) and see what performs best with your real-life data.

Upvotes: 3

Alex Aza
Alex Aza

Reputation: 78477

Something like this:

select UserId
from Users
where UserId not in (
    select UserId 
    from Events 
    where Status = TRUE)

With left join:

select UserId
from Users usr
    left join Events evt on
        evt.UserId = usr.UserId and evt.Status = TRUE
where evt.UserId is null

Upvotes: 2

Related Questions