0

I have a few tables defined like this:

CREATE TABLE `member` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
)

CREATE TABLE `members_to_delete` (
  `id` bigint(20),
  ...
)

CREATE TABLE `message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender_id` bigint(20) NOT NULL,
  `recipient_id` bigint(20) DEFAULT NULL,
  ...
  CONSTRAINT `_FK1` FOREIGN KEY (`sender_id`) REFERENCES `member` (`id`),
  CONSTRAINT `_FK2` FOREIGN KEY (`recipient_id`) REFERENCES `member` (`id`)
)

The members_to_delete table contains a subset of members.

I want to select (and eventually delete) all the rows in the message table only if neither sender_id nor recipient_id exist in the members_to_delete table. So I came up with this ugly query:

SELECT id FROM message WHERE sender_id NOT IN (SELECT id FROM members_to_delete) 
AND recipient_id NOT IN (SELECT id FROM members_to_delete);

I figure there must be a much better way to write this query, but I'm not sure what it is. What would be a more efficient way to write this sort of a query?

Bruce P
  • 19,995
  • 8
  • 63
  • 73

1 Answers1

2

You can use the technique in Return row only if value doesn't exist for multiple columns.

SELECT m.id
FROM message AS m
LEFT JOIN members_to_delete AS d
ON d.id IN (m.sender_id, m.recipient_id)
WHERE d.id IS NULL
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I was mostly concentrating on whether `IN()` is the right condition for this, or I need to do multiple joins. – Barmar Sep 26 '17 at 20:48
  • That's where I was going initially but then I saw yours and didn't want to think about what indexes may exist. – xQbert Sep 26 '17 at 20:51