How do I efficiently inverse a many-to-many SQL query?

Question

Hi everyone,

I have a many-to-many relationship, in which I query all M which have a specific N, for example:

SELECT M.* FROM M INNER JOIN ManyToManyTable
ON M.Id = ManyToManyTable.M
WHERE ManyToManyTable.N = @Id

Or:

SELECT M.* FROM M INNER JOIN ManyToManyTable
ON M.Id = ManyToManyTable.M
INNER JOIN N
ON N.Id = ManyToManyTable.N
WHERE N.Id = @Id

My question is, how to efficiently inverse the query so I get all M which do not have a specific N?

That is, like a NOT IN the selects above, but without the NOT IN statement, if possible.

Answer 1

Quick and dirty:

SELECT M.* FROM M
WHERE M.id NOT IN
    (SELECT M.id FROM M INNER JOIN ManyToManyTable
     ON M.Id = ManyToManyTable.M
     WHERE ManyToManyTable.N = @Id)

Better:

SELECT M.*
FROM M LEFT JOIN ManyToManyTable
ON M.Id = ManyToManyTable.M
WHERE ManyToManyTable.M IS NULL

Answer 2

In SQL Server 'NOT EXISTS' is generally more efficient than the OUTER JOIN approach.

SELECT M.* FROM M  
WHERE NOT EXISTS
 (SELECT * FROM ManyToManyTable MMT
           WHERE MMT.M = M.Id AND N=@Id )