Recently I’ve had the joy of working with more relationship tables than I can shake a stick at - essentially, most of my data has a many to many relationship.

Unforunately, I’ve also found the need to find ‘orphan’ records - in this case, users that are not assigned to a group. I’ve run into this before, and the only way I can think of doing it is this way:

SELECT
id as theuserid,
username
FROM
users
WHERE NOT EXISTS (
SELECT
`user-id`
FROM
groups_users_relationship
WHERE
`user-id` = theuserid
)

Any ideas on how to optimize this? Is there an alternative?

An alternative might be:

SELECT
u.id as theuserid,
u.username
FROM users u
LEFT OUTER JOIN
groups_users_relationship gur
ON
u.id = gur.`user-id`
WHERE
gur.`user-id`
IS NULL