Здравейте,

По време на подготовката за изпит, проведена на 16.02.2017, с колегите стигнахме до един доста интересен казус, който обещах да разгледам и разясня какво точно се обърка тогава.

Задачата е "12. Left Users" от изпита TheNerdHerd. В нея се изисква да намерим всички съобщения за чат с номер (Id) 17 от тези потребители, които са напуснали чата. Естествения ход на задачата поверява да намерим всички съобщения в този чат, да видим кои от тях са написани от потребители, които са все още активни в него (посредством mapping таблицата UsersChats), и  накрая да ги махнем, оставяйки вече неучастващите в чата потребители и техните съобщения. Заявката може да прилича на това (заедно със сортировката):

SELECT * FROM Messages AS m
INNER JOIN UsersChats AS uc ON uc.ChatId = m.ChatId
WHERE m.ChatId = 17
ORDER BY m.Id DESC

А резултата от заявката (ползвайки примерните данни от Data.sql скрипта):

След проверка можем да остановим, че предпоследната колона съдържа всички потребители, участващи в чата (това е потребител 19), останалите двама (потребител 24 и 25) са напуснали този чат и ние трябва да визуализираме само техните съобщения. Следващите няколко подхода от видеото се оказват неуспешни, нека се спрем на последния такъв:

SELECT m.Id, m.ChatId, m.UserId FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId
WHERE m.ChatId = 17 AND (m.UserId NOT IN (SELECT UserId FROM UsersChats WHERE ChatId = 17) OR m.UserId IS NULL)
ORDER BY m.Id DESC

В по-горната заявка се разглежда един допълнителен случай: какво става, ако дадения потребител някога е участвал в този чат, а след това неговия профил е изтрит. Отговора е, че неговото съобщение ще се пази, но ще има за стойност NULL на колоната UserId. С подобрениото условие си подсигуряваме, че ще хванем и тези потребители (по-скоро съобщенията, които те са пратили).

Защо обаче горният подход не работи? Оказва се, че условието, по което ни съединяваме двете таблици е непълно. Нека да разгледаме случая, в който имаме и други потребители в този чат (спрямо горния случай потребители 7,8,9 са добавени в него):

Независимо дали тези потребители са пращали съобщения или не, те са активни в този чат. И понеже нашето условие за връзка между двете таблици е нeпълно (join-a ще се случи само по колона  ChatId), ще се случи повторение на данните. Изкараните съобщения са точно тези, от които имаме нужда, но в ненужен брой.

За да решим този проблем трябва да се върнем към нашето join условие и да съединим по още една колона "m.UserId = uc.UserId", което означава следното нещо - потребителя, изпратил съобщението, да е този, който участва в чата:

SELECT * FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId AND m.UserId = uc.ChatId
WHERE m.ChatId = 17 AND (m.UserId NOT IN (SELECT UserId FROM UsersChats WHERE ChatId = 17) OR m.UserId IS NULL)
ORDER BY m.Id DESC

Така вече нашето решение връща правилните резултати:

Какво забелязваме накрая - предпоследната колона UserId има стойности само NULL. Нормално, няма как да свържем потребител 24 или 25 с чат 17, те са го напуснали и понеже ползваме LEFT JOIN ние можем да изкараме потретибелите, които не са в таблицата с чатовете. Това може да доведе до оптимизация на нашата заявка и тя финално да изглежда ето така:

SELECT * FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId AND m.UserId = uc.UserId
WHERE m.ChatId = 17 AND uc.UserId IS NULL
ORDER BY m.Id DESC

Искрено се надявам този пост да ви е полезен в решаването на задачата! Ако някои има въпроси по тази задачи, моля да ги зададе тук. :)

Поздрави!