Andy gave you the solution with IN or NOT IN. Perhaps easier to get than the solution that exists with the usual SET operations JOINS are.
Inverting the join condition doesn't lead to what you want. It's still a Join, not a non-join just because you replace = with <>. What you said was join all data with different ID, and that'll match almost all records with each other just not the ones with equal id. Mathematically, if you had nothing else but Ids that'd create all id combinations or permutations with different Ids on both sides.
If you want to check where NO record exists with the same ID, the join condition has to stay with equality, what changes is not getting matches, so no rows result with certain IDs.
Since you want to know exactly these IDs only existing on the left side, you'll need to change from INNER to LEFT join, you don't want an empty result if there is no match, you want a partial result and LEFT or RIGHT joins are for that, LEFT and RIGHT specify which part of a row you want, even if there is NO match. So
that's the essential thing to look for here.
And to see exactly only those result rows, that had No match, you look for NULLs, which are put into the result for the RIGHT columns of the joined data.
And now that becomes a little unintuitive, you check for any of the right fields to be NULL:
Code:
SELECT dbo.AspNetUsers.Id, dbo.AspNetUsers.Email, dbo.AspNetUsers.UserName
FROM dbo.AspNetUsers [highlight #FCE94F]LEFT[/highlight] JOIN
dbo.AspNetUserRoles ON dbo.AspNetUsers.Id [highlight #FCE94F]=[/highlight] dbo.AspNetUserRoles.UserId
[highlight #FCE94F]WHERE dbo.AspNetUserRoles.Id IS NULL[/highlight]
"All rows of all tables have Ids, no Id value is null, that's not even allowed, as these fields are primary keys", is what you'll say, perhaps. You have to understand the WHERE clause checks the value of the result rows. If you'd SELECT * you'd get all data of AspNetUsers rows (that's what LEFT joins means) and when there is no match in dbo.AspNetUserRoles exists, all the columns of that right side are set NULL in the result. And that's the rows you're interested in. And that even works, even though you have an explicit field list of fields only from dbo.AspNetUsers, because virtually during the query still all columns of both joined tables are accessible and no matter if they are fully scanned (bad for performance) or matched by an index, you can imagine finding no match means a pointer into the joined table ends in nowhere land where everything is null and that can be seen from a WHERE clause, no matter if really just an index is looked into whch yields no match and only contains the UserID field and no others. No match means righttable.column IS NULL becomes true for any column of the right table. That's the "secret" of SQL Sets operations.
I like to use Id as it is a stronger indicator in the code I don't really mean to search for records with NULL Id value, but NULLs (no information) resulting from no match found.
I say all this not because LEFT JOINS Are superior to NOT IN existence check queries, but because this teaches an important concept of queries.
Bye, Olaf.
Olaf Doschke Software Engineering
