Как сравнить два столбца в sql

SQL-Ex blog

Недавно мы рассматривали сравнение схем с помощью Azure Data Studio. Что если нам потребуется сравнение таблиц с помощью запроса? В этой статье мы сравним использование EXCEPT, NOT IN, и NOT EXISTS для нахождения различий в двух таблицах.

Мы будем сравнивать две таблицы Comic и Comic_Copy. В Comic содержится на 48 строк больше, чем в Comic_Copy. Давайте найдем отличие.

EXCEPT

Давайте начнем сравнение с EXCEPT. При использовании EXCEPT мы захотим иметь одинаковое число и порядок столбцов, а также одинаковые типы данных. Мы удовлетворяем этим критериям в нашем примере, поэтому давайте выполним наш запрос и посмотрим на результаты:

SELECT Id,Title 
FROM Comic 
EXCEPT 
SELECT Id,Title 
FROM Comic_Copy; 
GO

Мы видим 48 строк, что правдоподобно, исходя из подсчетов, которые мы рассматривали ранее.

NOT IN

Теперь мы будем использовать NOT IN для сравнения таблиц. Выберем Id из таблицы Comic, и будем использовать подзапрос, чтобы получить идентификаторы в Comic_Copy:

SELECT Id, Title 
FROM Comic 
WHERE Id NOT IN ( 
SELECT Id 
FROM Comic_Copy 
); 
GO

Мы опять получаем наши 48 строк.

NOT EXISTS

Наконец, давайте проверим NOT EXISTS.

SELECT Id, Title 
FROM Comic AS c 
WHERE NOT EXISTS ( 
SELECT 1 
FROM Comic_Copy AS cc 
WHERE cc.Id = c.Id 
); 
GO

Что работает лучше?

Если мы выполним все три запроса вместе, какой покажет себя лучше остальных?

EXCEPT не кажется лучшим вариантом, в то время как два других варианта демонстрируют одинаковый план. Мы работаем с относительно небольшим набором данных, поэтому выполнение каждого запроса занимает менее секунды с одним и тем же числом логических чтений.

Хотя NOT IN и NOT EXISTS работают по-разному. Для NULL-значений NOT IN не вернет записей, поскольку NULL-значения не трактуются как сравниваемые. При использовании NOT EXISTS NULL также будут возвращаться.

Если мы изменим одно из значений title на NULL:

UPDATE Comic 
SET Title = NULL 
WHERE /> GO

и выполним запросы на сравнение по Title, то получим разные результаты. NOT IN даст нам 47 строк:

А NOT EXISTS по-прежнему дает 48:

Переходим на NOT EXISTS

Мы рассмотрели три разных метода нахождения различий между таблицами. Все три варианта с использованием EXCEPT, NOT IN и NOT EXISTS могут выполнить работу, но разными способами. NOT EXISTS был бы моим «выбором» для оптимального поиска различий, но имейте в виду наличие NULL при сравнении с помощью NOT IN.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Сравнить 2 столбца из разных таблиц ,в пустые ячейки null

Не могу разобраться , что я делаю не так. Знатоки посоветуйте где найти ответ или что я делаю не так.И как можно воплотить это в жизнь.

Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Сумма Null пустые ячейки
Добрый день У меня есть вопрос по подсчету суммы в таблице на sql Пример таблицы во вложении, в.

Сравнить данные из разных таблиц и добавление в третью
Есть две таблицы т1 2009 2010 2011 2012

FK от одного столбца на разные PK разных таблиц?
Здравствуйте. Подскажите пожалуйста, как можно реализовать след. функционал (мнеб на словах хоть, а.

Нужно удалить пустые ячейки из выбронного столбца
Пожалуйста, помогите. Я только учись писать макросы. Вот написала один, а он некоректно работает.

1634 / 1135 / 171
Регистрация: 23.07.2010
Сообщений: 6,713

Сообщение было отмечено ig866 как решение

Решение

left join спасет отца русской демократии
1113 / 758 / 183
Регистрация: 27.11.2009
Сообщений: 2,264

Сообщение от pincet

left join спасет отца русской демократии

Как же спасёт?
INNER JOIN по нормальным заполненным полям ind не помогает,
а что же изменит LEFT JOIN?

87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
Помогаю со студенческими работами здесь

Из столбцов D и E копировать числа в пустые ячейки этого же столбца
Помогите написать макрос. Нужно из столбца D и E копировать числа в пустые ячейки этого же.

Сравнить 2 колонки из разных таблиц
Здравствуйте. Как можно сравнить баланс пользователя из разных таблиц? Есть таблицы table1 и.

Сравнить 2 столбика из разных таблиц
Есть два файла excel. с одинаковыми таблицами. В ячейках столбика 2 каждой таблицы есть номера (.

Сравнить два столбца таблиц
Здравствуйте! Есть две таблицы: Отделы и Работники. Во второй таблице есть столбец отделы для.

Сравнение двух столбцов и вывод уникальных строк в sql

Есть правильная оценка (пусть будет к примеру) 0 и 1 (это оценка преподавателя)
Есть оценка студента также 1 и 0.

Нужно сравнить оценку студента с оценкой преподавателя в случае не совпадения нужно вывести список студентов которые хуже всего справились с конкретным вопросом и с заданием в целом. Прощу помощи.

Отслеживать
14k 13 13 золотых знаков 38 38 серебряных знаков 63 63 бронзовых знака
задан 4 фев 2017 в 7:07
5 1 1 золотой знак 1 1 серебряный знак 4 4 бронзовых знака

Решайте задачу по очереди, сначала выбираете записи которые вообще должны попадать в результат видимо проверяя на неравенство оценки. потом группируете их до студентов, вопросов, заданий. Если после этого что то еще не получается пишите сюда до чего вы уже дошли. Т.е. приводите здесь структуру таблицы с тестовыми данными, результат который необходим и тот запрос который у вас уже получился для работы с этими данными

4 фев 2017 в 7:19

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

Для ответа на Ваш вопрос пришлось набросать пример. Создаем таблицу:

CREATE TABLE Test ( `loginStudent` varchar(10), -- Логин студента `questionNumber` int, -- Номер вопроса `ratingTeacher` bit, -- оценка преподавателя `ratingStudent` bit); -- оценка студента 

Заполняем таблицу значениями:

INSERT INTO Test ( `loginStudent`, `questionNumber`, `ratingTeacher`, `ratingStudent`) VALUES ('denis', 1, 1, 1), ('denis', 2, 1, 1), ('denis', 3, 0, 1), ('denis', 4, 1, 0), ('denis', 4, 0, 1), ('mark', 1, 1, 1), ('mark', 2, 1, 1), ('mark', 3, 0, 0), ('mark', 4, 0, 1), ('mark', 4, 1, 0), ('ann', 1, 0, 1), ('ann', 1, 1, 0), ('ann', 1, 0, 1), ('ann', 2, 1, 0), ('ann', 3, 1, 0), ('ann', 4, 0, 1); 

Конечно не 50К, но этого достаточно, чтобы увидеть результат. Пишем запрос SELECT на выборку необходимых записей:

SELECT loginStudent, count(*) as 'Количество не совпавших' FROM Test WHERE ratingTeacher <> ratingStudent -- у кого не совпала оценка GROUP BY loginStudent -- группируем по студенту HAVING count(*) > 0 -- у кого кол-во не совпавших больше 0 -- (можно менять эту величину или не писать вовсе, для Вашего вопроса можно не писать) order by count(*) desc -- сортируем по убыванию. Первая строка - больше всех не совпала 

Видим соответствующий результат, кто хуже всех справился с заданием в общем:

Студентка ann больше всех ошиблась, шесть раз, это общий результат.

Напишем немного другой запрос, в котором посмотрим, на какие именно вопросы отвечают хуже всего и кто.

SELECT loginStudent, questionNumber, count(ratingTeacher <> ratingStudent) as 'Количество не совпавших' FROM Test WHERE ratingTeacher <> ratingStudent -- у кого не совпала оценка GROUP BY -- группируем loginStudent, -- по студенту questionNumber -- вопросу HAVING count(ratingTeacher <> ratingStudent) > 0 -- у кого кол-во не совпавших больше 0 order by count(ratingTeacher <> ratingStudent) desc, questionNumber, loginStudent 

Смотрим результат, кто справился хуже всех с конкретным вопросом:

На этом результате видно, что студентка Ann трижды ошиблась в первом вопросе, ну и mark с denis ошиблись в четвертом по два раза, ну и единичные ошибки.

Раз уж нам нужно все и сразу, то используем алиасы и подзапрос. Напишем вот такой запрос, который будет добавлять дополнительную информацию к нашему второму запросу:

 SELECT r.loginStudent, r.questionNumber, count(r.ratingTeacher <> r.ratingStudent) as 'Количество не совпавших', (select count(*) from Test t where t.loginStudent = r.loginStudent and t.ratingTeacher <> t.ratingStudent) as 'Всего ошибок' -- подзапрос -- в подзапросе нужны Alias-ы, так как таблицы совпадают и имена столбцов одинаковые FROM Test r -- r - псевдоним для данной таблицы (Alias) WHERE r.ratingTeacher <> r.ratingStudent -- у кого не совпала оценка GROUP BY -- группируем r.loginStudent, -- по студенту r.questionNumber -- по вопросу HAVING count(r.ratingTeacher <> r.ratingStudent) > 0 -- у кого кол-во не совпавших больше 0 order by count(r.ratingTeacher <> r.ratingStudent) desc, -- сортируем по убыванию. Первая строка - больше всех не совпала r.questionNumber, r.loginStudent 

И тут мы уже видим немного другую таблицу результатов, в которой видим сколько ошибок в конкретном вопросе и сколько ошибок всего у студента:

Количество не совпавших — это у нас количество ошибок в вопросе. То есть, сколько ошибок в конкретном вопросе у конкретного студента. Такая таблица сразу говорит, кто и сколько раз ошибся в конкретном вопросе, кто ошибся больше всех и общая статистика ошибок в последнем столбце.

Как сравнивать две произвольные таблицы?

Добрый день!
Есть две таблицы. Известно, что большая часть данных в них пересекается. Но могут быть и совсем разные данные или измененные данные в одной из таблиц. Необходимо написать алгоритм по которому бы программа находила общие записи в таблицах и отображала. Как бы для начала подойти к этой задаче?

• Вопрос задан более трёх лет назад
• 13533 просмотра

Комментировать
Решения вопроса 0
Ответы на вопрос 4

Oleg Agapov @oleg_agapov
Data analyst, Business Intelligence

Всё-таки Java или SQL? 🙂
Если таки SQL. Подразумеваю, что используется Оракл.
Во-первых, если таблицы не большие, то не должно быть проблем с обычным джойном или декартовым произведением

SELECT t1.*, t2.* FROM table1 t1, table2 t2 WHERE t1.fields = t2.fields

Если таблицы большие, тут нужно быть аккуратнее, чтоб не пошел фулл скан таблиц и все не зависло к чертям. Убедитесь, что в таблице есть индексы. Вам повезло, если индексы совпадают с теми полями, по которым нужно сравнивать.
Далее, я бы «откусывал» небольшие куски одной из таблиц и джойнил бы другую либо как в первом примере, либо вложенным подзапросом в SELECT. Если не критично для быстродействия, понемногу добавлял бы бОльшие куски первоначальной таблицы. Опять же, если не тормозит — то ок. В противном случае — писал бы процедуру для разбиения на куски и последующего джойна.

Ответ написан более трёх лет назад
Нравится 1 4 комментария

Если Оракл и если таблицы большие, то индексы не нужны — все равно нужно сравнить между собой ПОЛНОСТЬЮ всё. Лучше в этом случае распараллелить запрос на 4-32 потока, смотря — сколько ресурсов у сервера есть и сколько из них можно утилизировать.

Oleg Agapov @oleg_agapov

Борис: Совершенно верно, но я знаком с Map Reduce только в теории. Поэтому бы и использовал индекс, чтобы разбивать на куски (по сути — потоки) и прогонял бы по очереди. А искать известную часть одной таблицы все-таки лучше по индексу второй.

Oleg Agapov: ну опять-таки неоднозначно =) если, например, нужен кусок 100-миллионный кусок таблицы на 1 млрд записей («жалкие» 10%), то индексы лучше не использовать, т.к. время запроса, скорее всего, увеличится по сравнению с фуллсканом. Что касается разбиения на куски и дальнейшей параллельной обработки, то идея хорошая. Я примерно о том же и говорил: Oracle позволяет запустить запрос в несколько потоков (сессий).

Если выполнятся следующие условия:
1) нужен фул-скан;
2) таблицы огромные а ресурсы сервера слабые относительно объема таблиц;
3) таблицы разбиты на партиции (например по дате);

То можно запустить сверку в цикле по дате с загрузкой результата каждой итерации во временную таблицу, или выводом данныx в dbms_output (Oracle). Таким образом не будет эффекта зависшей БД, и не нужно будет гадать отработает ли вообще когда нибудь данный запрос и «рубить или не рубить» . Работа будет делаться маленькими порциями-партициями и даже в случае сбоя можно будет продолжить с «места разъединения».