0

My application generates the following SQL-request to get the records matching teamkey:

select cr.callid, t.teamname, u.userfirstname 
from callrecord cr
left join agentrecord ar on cr.callid = ar.callid
left join users u on ar.agentkey = u.userkey 
left join teams t on u.teamkey = t.teamkey 
where t.teamkey in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)

This works fine.

When I tried to get the records NOT matching teamkey, the first idea was:

select cr.callid, t.teamname, u.userfirstname 
from callrecord cr
left join agentrecord ar on cr.callid = ar.callid
left join users u on ar.agentkey = u.userkey 
left join teams t on u.teamkey = t.teamkey 
where t.teamkey not in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)

This returns no data. Seems this requires completely different SQL request. Please help to switch my mind in proper direction.

Record from callrecord table may have no matching record in agentrecord table, also record from users table may have no matching record in teams table, but I want them in the output.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vodnik
  • 119
  • 8
  • Does this answer your question? [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) – Charlieface Feb 20 '21 at 21:23
  • 1
    Your `WHERE` condition is turning all those outer joins into inner joins –  Feb 20 '21 at 22:12
  • Your query is malformed. All those `LEFT JOIN` are silently converted into `INNER JOIN` by the engine since you added a `WHERE` predicate that doesn't allow nulls. – The Impaler Feb 20 '21 at 23:16
  • Request is generated by application with user-controlled filter for teams: OFF - there will be no **where** clause at all; INCLUDE - where t.teamkey in (); EXCLUDE - where t.teamkey not in (). For my mind it's OK that left join is converted to inner join in that case. – Vodnik Feb 22 '21 at 17:11

1 Answers1

1

Your query should work, for example a team key of 17 should be returned.

The condition is not exactly the negation of the original because in SQL null values never compare as true (look up SQL three-valued logic, they evaluate as unknown). Only is null and is distinct from (standard but not supported by most RDBMS) can be used to compare nulls.

So the only rows you might be missing are those that don't have a team. If teamkey is null (in the table or because one of the join did not match), it would not be returned.

You can get those results back by changing your condition to t.teamkey not in (...) or t.teamkey is null

jods
  • 4,581
  • 16
  • 20