Return row only if value doesn't exist

Question

I have 2 tables - reservation:

   id  | some_other_column
   ----+------------------
   1   | value
   2   | value
   3   | value

And second table - reservation_log:

   id  | reservation_id | change_type
   ----+----------------+-------------
   1   | 1              | create
   2   | 2              | create
   3   | 3              | create
   4   | 1              | cancel
   5   | 2              | cancel

I need to select only reservations NOT cancelled (it is only ID 3 in this example). I can easily select cancelled with a simple WHERE change_type = cancel condition, but I'm struggling with NOT cancelled, since the simple WHERE doesn't work here.

refer to the not equal documentation: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_not-equal — Ric, Feb 07 '14 at 16:40
Those comments are not right, since 1 and 2 have type != cancel. — Barmar, Feb 07 '14 at 16:40
if I use not equal condition `WHERE change_type != 'cancel'` it prints all reservations and it is wrong because only reservation with id 3 is not cancelled — mdr, Feb 07 '14 at 16:44
alway be careful of using **NOT IN ()** if the data you are comparing can be NULL e.g. see https://stackoverflow.com/a/129152/2067753 — Paul Maxwell, Aug 30 '19 at 23:48

Barmar · Accepted Answer · 2017-12-20T16:58:07.193

17

SELECT *
FROM reservation
WHERE id NOT IN (select reservation_id
                 FROM reservation_log
                 WHERE change_type = 'cancel')

OR:

SELECT r.*
FROM reservation r
LEFT JOIN reservation_log l ON r.id = l.reservation_id AND l.change_type = 'cancel'
WHERE l.id IS NULL

The first version is more intuitive, but I think the second version usually gets better performance (assuming you have indexes on the columns used in the join).

The second version works because LEFT JOIN returns a row for all rows in the first table. When the ON condition succeeds, those rows will include the columns from the second table, just like INNER JOIN. When the condition fails, the returned row will contain NULL for all the columns in the second table. The WHERE l.id IS NULL test then matches those rows, so it finds all the rows that don't have a match between the tables.

edited Dec 20 '17 at 16:58

answered Feb 07 '14 at 16:43

Barmar

741,623
53
500
612

@Barmar Well since you seemingly want me asking for help on this question instead of my own.... how do I do this when calling multiple columns from the subquery keeping in mind that an inner join needs a matching column, which will always drop the results that don't match, something that will not work in my situation because I need two columns both which contain usernames. – Bruce Sep 13 '16 at 20:18
@Bruce You can use `ON t1.username in (t2.blocker, t2.blocked)` to match either column when joining. – Barmar Sep 13 '16 at 20:40
I don't need to match either, I need to match both. If i match one its not going to return the results of the other. – Bruce Sep 13 '16 at 21:01
Then use `ON t1.username = t2.blocker AND t1.username = t2.blocked` to match both. This is basic SQL. – Barmar Sep 13 '16 at 21:02
Asking questions specific to my problem on a 2 year old question that doesn't really relate to my problem seems the wrong way to go about this, but sense you won't reopen my different question.... how do I select all members that are not a given user while joining on a table where we are matching where all members are the given user. In other words, who do I do a WHERE clause where t1.username <> 'user' while doing a join on the username. There needs to be a username to match else it always returns empty. – Bruce Sep 13 '16 at 21:44
I've reopened your question. But the whole point of this idiom is that you match on username, but because it's a `LEFT JOIN` it returns `NULL` for all the columns in the second table when there's no match, and then you test that in the `WHERE` clause. – Barmar Sep 13 '16 at 21:47
1

@Bruce Try to understand the **logic** of it, rather than just copying code. If you understand how it works, you'll be able to generalize to many different cases. – Barmar Sep 13 '16 at 21:48
I love how people just assume things, I am trying to understand the logic, thats why I am not understanding the code. To do a join, you have to have some data that actually matches. A join won't work if you are trying to exclude the very result you need to join. IE. if I exclude username in my whereclause, how can I join using the username as its never going to match. – Bruce Sep 13 '16 at 21:50
Read about the difference between `INNER JOIN` and `LEFT OUTER JOIN`:http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Barmar Sep 13 '16 at 21:57
@Bruce I've added a detailed explanation of how the second query works. – Barmar Sep 13 '16 at 21:59
@Barmar http://www.informit.com/articles/article.aspx?p=30875&seqNum=5 This is every type of table join, including the cross join (mysql does not have a "full table join" per say). I understand joins. I use them all the time. I cannot get the desired result for my specific question. So. I am going back to my question instead of continuing a conversational howto thread here. – Bruce Sep 13 '16 at 22:17

score 4 · Answer 2 · answered Feb 07 '14 at 17:03

4

Just for completeness (and I honestly believe it fits better), I encourage you to use a simple NOT EXISTS.

SELECT * FROM reservation R
WHERE NOT EXISTS (
  SELECT 1 FROM reservation_log
  WHERE reservation_id = R.id
    AND change_type = 'cancel'
);

answered Feb 07 '14 at 17:03

leonardo_assumpcao

684
7
17

Return row only if value doesn't exist

2 Answers2

Linked

Related