MySql

The EXISTS clause is faster than IN when the subquery results are very large. The IN clause is faster than EXISTS when the subquery results are very small.

jaz nikoli ne uporabljam in, vedno exists, pa programiram v sql vsak dan. in je neuporaben, exists je hitrejša alternativa. to vse od takrat, ko sem prebral, da je exists hitrejši in tudi intuicija ti lahko govori, da je exists hitrejši. in vsakič zbere vse rezultate, ko izbriše eno vrstico. exists pa neha takoj, ko najde prvo.

Pomoje samo uporab aliase za tabele in stolpce v prvem sqlu in bo delal.

KAJ TAKEGA?

DELETE FROM detail D
JOIN
--WHERE EXISTS
(
SELECT MAX(id) AS id
FROM master
GROUP BY term
HAVING COUNT(term) > 1
) X ON X.ID =D.ID

težko kodirat brez tabel ampak kaj takega bo najbrž potrebno:

;WITH CTE AS
(
	SELECT MAX(id) AS id
	FROM master m
	GROUP BY m.term
	HAVING  COUNT(term) > 1
)
SELECT * 
-- DELETE d
FROM detail d
WHERE EXISTS (SELECT 1 FROM CTE c WHERE d.id = c.id)

Kje imaš pa detail tablo?

jp join bo kr pravi kle, exists/in ni potreben tu.
sem malo pobrkljal oba možna načina:

NAČIN 1:

;with cte as
(
   select key 
   from detail oi 
   where oi.product like '%z%' 
   group by key 
   having COUNT (oi.product) > 1
)
select * 
from master o 
join cte c on c.key = o.key

NAČIN 2:

select * 
from master o 
where (select COUNT (1) from detail oi2 where oi2.key = o.key and oi2.product like '%z%') > 1