Создание запроса выборки (mysql)

Question

[SkyAleks]

Помогите составить запрос.
Есть таблица mails в которой есть два поля email и status
В Таблице есть 5 строк

Email1 status1
Email2 status1
Email3 status1
Email1 status2
Email2 status2

То есть, как мы видим у Email1 и Email2 имеется по две записи с разными статусами.
Нужно составить запрос который бы делал выборку из этой таблицы и возвращал нам только емаилы со статусом 1, которых нету в таблице со статусом 2
То есть в нашем случае это была бы единственная запись — Email3

Answer 1

[Ivan Komarov]

SELECT m1.*
FROM mails AS m1
LEFT JOIN mails AS m2 
	ON m1.email = m2.email AND m2.`status` = 'status2'
WHERE 
	m1.`status` = 'status1' 
	AND m2.email IS NULL

Answer 2

[Ivan Komarov]

А вот маленькое извращение, за которые мы все так любим SQL:

SELECT email, `status` FROM (
	SELECT email, `status`, COUNT(*) AS cnt 
	FROM mails 
	GROUP BY email
	HAVING `status` IN ('status1', 'status2')
) AS subq WHERE cnt = 1;

Comments

[mark_ablov]

Не катит, ибо никто не гарантирует отсутствие дублей :)

[Ivan Komarov]

Конечно не катит, это вариант для приера, когда email и status — первичный ключ.

[Ivan Komarov]

Специально для вас :)

SELECT email, `status` FROM (
	SELECT email, `status`
	FROM mails 
	WHERE `status` = 'status1'
	GROUP BY email
	HAVING COUNT(*) = 1

	UNION ALL

	SELECT email, `status`
	FROM mails 
	WHERE `status` = 'status2'
	GROUP BY email
	HAVING count(*) = 1
) AS subq
GROUP BY email
HAVING COUNT(*) = 1;

[Ivan Komarov]

Сорри, в подзапросе HAVING count(*) = 1 лишние.

Answer 3

[ertaquo]

Как-то так вроде:
SELECT * FROM table WHERE email NOT IN (SELECT email FROM table WHERE email NOT LIKE "status2")

Comments

[ertaquo]

Тьфу, перепутал.
SELECT * FROM table WHERE email NOT IN (SELECT email FROM table WHERE email LIKE "status2")

[mark_ablov]

забыли про первое условие — «и возвращал нам только емаилы со статусом 1»
да и зачем проверять емайл там, где нужно проверять второе поле (email LIKE «status2»)?

[Ivan Komarov]

Умоляю, никогда не пишите больше такие запросы.

[ertaquo]

Ладно-ладно, согласен. В конце концов, учиться никогда не поздно. Спасибо за примеры ниже :-)

[Ivan Komarov]

просто not in подзапрос — это достатчно неторопливая штука.
Да и вообще, IN сам по себе опасен :)
Забавно, ваш вариант мне тоже в голову не пришел, мозг уже даже не предлагает такие варианты :)

[ertaquo]

А можно поподробнее, чем он опасен? И что будет быстрее — JOIN, NOT EXISTS, HAVING?

[Ivan Komarov]

В общем, извиняюсь. Был не прав. Я не исследовал NOT IN на MySQL. Но помнится, на Microsoft SQL сервер 2005 он тормозил. Похоже в MySQL, в данном случае, NOT IN оптимизируется в тот же самый LEFT JOIN.

Я создал тестовую таблицу из двух полей с первичным ключом по обоим полям и пятидесятью тысячью записями. Все записи со статусами status1 и status3 и каждая третья с статусом status2. Попробовал все варианты. Между NOT IN и LEFT JOIN разницы по скорости нет. Но это только в случае использования индекса. Стоило удалить первичный индекс, как картина изменилась. Я оставил только 2000 записей в таблице (а то было бы слишком долго) и LEFT JOIN оказался быстрее чем NOT IN в три раза. Так что все же лучше LEFT JOIN. Это нативнее,
меньше проблем при переносе на другую базу, да и вам потом будет легче писать под любым SQL'ем, если вы уверенно используете LEFT JOIN. Опять же бывает, что подобные выборки используются во вьюхе, и потом где-нибудь в другом месте другой запрос может начать тормозить из-за этого NOT IN'а в другой вьюхе. IMHO, планы запросов, к сожалению, строить идеально не умеет ни одна БД.

NOT EXISTS же медленнее JOIN'а раза в два (тестил только с индексами). А вариант с группировкой написал исключительно ради спортивного интереса. (Уже пол года как занимаюсь совсем не SQL, немного соскучился :) ). Он вообще будет дико медленно работать. Там, кстати, ошибка, вместо HAVING должно быть WHERE перед GROUP BY с тем же условием).

Удачи.

[ertaquo]

Спасибо :-)

Answer 4

[mark_ablov]

select email from t where email not in (select email from t where status = 'status2') and status = 'status1'
Но это коряво, думайте над архитектурой БД или логикой.

Comments

[Ivan Komarov]

конечно коряво, откройте для себя мощь LEFT JOIN'а

[mark_ablov]

угу, in тормозит безбожно.

Answer 5

[Ivan Komarov]

SELECT m1.* FROM mails AS m1 
WHERE NOT EXISTS(
	SELECT * FROM mails AS m2 
	WHERE 
		m2.email = m1.email AND m2.`status` = 'status2'
)

Но первый вариант красивее.