1

This query works for me, but I don't want to use a subquery. I tried a lot but failed can I use this query without a subquery?

select a1.active_id
from active_table a1
where a1.active_id
NOT IN ( select a2.active_id from view a2 group by a2.active_id )
AND datediff(now(),active_date)>30
Marco
  • 22,856
  • 9
  • 75
  • 124
Pankti Shah
  • 35
  • 1
  • 7
  • I suggest you set the datediff first. Only the rows that will match that will be checked against the `NOT IN()`, which should be faster. – Martijn Nov 19 '13 at 13:22

2 Answers2

4

Use LEFT JOIN:

SELECT a1.active_id FROM active_table a1 
LEFT JOIN view a2
ON a1.active_id = a2.active_id
WHERE a2.active IS NULL
AND datediff(now(),active_date)>30

LEFT JOIN/IS NULL is equivalent to NOT IN. For detailed explanation see:

What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Community
  • 1
  • 1
Kacer
  • 679
  • 3
  • 12
3

Try this:

select a1.`active_id`
 from active_table a1 
          left join view a2 on (a1.`active_id` = a2.active_id)
where a2.active_id is null
  AND datediff(now(),a1.active_date)>30
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87