Найдите записи из одной таблицы, которых нет в другой
У меня есть две следующие таблицы (в MySQL):
Phone_book
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1 | John | 111111111111 |
+----+------+--------------+
| 2 | Jane | 222222222222 |
+----+------+--------------+
Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 1 | 0945 | 111111111111 |
+----+------+--------------+
| 2 | 0950 | 222222222222 |
+----+------+--------------+
| 3 | 1045 | 333333333333 |
+----+------+--------------+
Как узнать, какие звонки были сделаны людьми, чей phone_number отсутствует в Phone_book? Желаемый результат будет:
Call
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 3 | 1045 | 333333333333 |
+----+------+--------------+
Ответы 8
Есть несколько разных способов сделать это с разной эффективностью, в зависимости от того, насколько хорош ваш оптимизатор запросов, и относительного размера ваших двух таблиц:
Это самое короткое заявление, и оно может быть самым быстрым, если ваша телефонная книга очень короткая:
SELECT *
FROM Call
WHERE phone_number NOT IN (SELECT phone_number FROM Phone_book)
в качестве альтернативы (спасибо Альтерлайф)
SELECT *
FROM Call
WHERE NOT EXISTS
(SELECT *
FROM Phone_book
WHERE Phone_book.phone_number = Call.phone_number)
или (спасибо WOPR)
SELECT *
FROM Call
LEFT OUTER JOIN Phone_Book
ON (Call.phone_number = Phone_book.phone_number)
WHERE Phone_book.phone_number IS NULL
(игнорируя это, как говорили другие, обычно лучше выбирать только те столбцы, которые вы хотите, а не `` * '')
Левое внешнее соединение, вероятно, является самым быстрым в общем случае, поскольку оно предотвращает повторное выполнение подзапроса.
Не привередничать, но подзапрос по моему предложению возвращает <code> select 'x' </code>, а не <code> select * </code>
да - руководство MySQL предполагает, что это нормально для запроса EXISTS
@Alnitak: Во втором запросе вам не нужен SELECT * в подзапросе. Вместо этого, например, SELECT 1 должен быть достаточно красивым.
Re SELECT 1 в приведенном выше комментарии: AFAIK, EXISTS( SELECT .., в современных БД, оптимизируется до одного и того же результата, независимо от того, что вы поставили после SELECT. Достаточно умен, чтобы знать, что EXISTS не использует содержимое строки. Тем не менее с точки зрения удобочитаемости не помешает сделать очевидным, что вы не используете содержимое :)
ПРИМЕЧАНИЕ. LEFT OUTER JOIN (из WOPR) может возвращать несколько строк для каждой строки Call, если Phone_Book имеет несколько совпадающих записей. Каждый раз, когда у вас есть связь 1:N между двумя таблицами, вы можете получить несколько строк. В этом случае может быть предпочтительнее использовать подход NOT EXISTS Alterlife.
Потрясающие !! Это очень помогает. Благодаря тонну.
Приведенный ниже код будет немного более эффективным, чем ответы, представленные выше, при работе с большими наборами данных.
SELECT * FROM Call WHERE
NOT EXISTS (SELECT 'x' FROM Phone_book where
Phone_book.phone_number = Call.phone_number)
Как всегда, стоит профилировать производительность запросов по вашему целевому набору данных, чтобы выбрать тот, который имеет лучшую производительность. В наши дни оптимизаторы SQL достаточно хороши, так что результаты производительности часто удивляют.
Преимущество этого подхода (по сравнению с LEFT OUTER JOIN в WOPR) заключается в том, что он позволяет избежать возврата нескольких строк на строку Call, если в Phone_book есть несколько совпадающих строк. То есть, если между вашими двумя таблицами существует связь 1:N.
Я бы начал с этого - он прямо отражает намерение. Если производительность недостаточна, убедитесь, что существуют соответствующие индексы. Только после этого попробуйте менее очевидный LEFT OUTER JOIN и посмотрите, не улучшится ли его производительность.
SELECT Call.ID, Call.date, Call.phone_number
FROM Call
LEFT OUTER JOIN Phone_Book
ON (Call.phone_number=Phone_book.phone_number)
WHERE Phone_book.phone_number IS NULL
Следует удалить подзапрос, позволяя оптимизатору запросов творить чудеса.
Также избегайте «SELECT *», потому что это может нарушить ваш код, если кто-то изменит базовые таблицы или представления (а это неэффективно).
Как правило, это наиболее эффективный метод, поскольку он не выполняет несколько проходов по второй таблице ... надеюсь, что некоторые люди читают коменты.
Я бы предпочел, чтобы люди профилировали: если вы не лучший гуру производительности SQL, сказать заранее, что будет самым быстрым, довольно сложно (и зависит от используемого движка СУБД).
Обозначение Big O легко подскажет, что вы можете ожидать от самого быстрого в этом случае. Это на порядки другое.
См. Ответ загробной жизни и мой комментарий там, если есть связь 1:N между вашими двумя таблицами. ИЛИ добавьте DISTINCT, как показано в Ответ Владо
Я думаю
SELECT CALL.* FROM CALL LEFT JOIN Phone_book ON
CALL.id = Phone_book.id WHERE Phone_book.name IS NULL
Столбец id в таблице call отличается от значения столбца id в таблице Phone_book, поэтому вы не можете присоединиться к этим значениям. См. Ответ WOPR для аналогичного подхода.
SELECT DISTINCT Call.id
FROM Call
LEFT OUTER JOIN Phone_book USING (id)
WHERE Phone_book.id IS NULL
Это вернет дополнительные идентификаторы, которые отсутствуют в вашей таблице Phone_book.
SELECT t1.ColumnID,
CASE
WHEN NOT EXISTS( SELECT t2.FieldText
FROM Table t2
WHERE t2.ColumnID = t1.ColumnID)
THEN t1.FieldText
ELSE t2.FieldText
END FieldText
FROM Table1 t1, Table2 t2
Это вернет вам данные из одной таблицы, если данные для того же столбца отсутствуют в другой таблице.
SELECT name, phone_number FROM Call a
WHERE a.phone_number NOT IN (SELECT b.phone_number FROM Phone_book b)
Это не дает ответа на вопрос. Чтобы критиковать или запрашивать разъяснения у автора, оставьте комментарий под его сообщением. - Из обзора
@DennisKriechel обновил запрос, чтобы он был более конкретным для вопроса.
В качестве альтернативы,
select id from call
minus
select id from phone_number
Не уверен, что это ответ на вопрос как есть (хотя оператор MINUS) является новым дополнением. Это оказалось в очереди низкого качества - вы можете улучшить этот ответ.
избегайте IN, используйте EXISTS - подсказка находится в заголовке вопроса