블로그 이미지
LifeisSimple

calendar

 «  » 2024.5
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

Notice

'not in'에 해당되는 글 1건

  1. 2010.04.15 Not In Query
2010. 4. 15. 12:14 Brain Trainning/DataBase

Not In Query

상황에 따라 다를 수 있지만 3번이 가장 효율적이네요... 
혹시나 하고 테스트는 못해봤는데 음... 이제 되도록 Not In은 사용하지 말아야겠습니다. 후후후


r.hensbergen (1/29/2009)
souLTower (1/29/2009)
Aren't left joins and NOT EXIST statements more efficient than NOT IN statements? I don't know if I made that up or read it somewhere. I usually use a query like this:

I believe that this can cause the optimizer to search for a match for each record in myTable
-- option 1 NOT IN
select foo, bar, baz from myTable where baz not in
  (select baz from myOtherTable)

-- Option 2 NOT EXISTS
select foo, bar, baz from myTable A where not exists 
  (select baz from myOtherTable B where A.baz = B.baz)

-- Option 3 LEFT JOIN
-- I prefer this one
select foo, bar, baz from myTable A left join myOtherTable B
  ON A.baz = B.baz where B.baz IS NULL

Definitely option 3! Both 1 and 2 the inner query relates to the outer query, which means usually bad performance but at least less readability.
저작자표시 비영리 변경금지

'Brain Trainning > DataBase' 카테고리의 다른 글

Managing Max Degree of Parallelism (펌)  (0) 2010.04.28
페이징쿼리 속도 비교 (펌글)  (0) 2010.04.19
Job List 적용  (0) 2010.04.14
Partition Table 의 구현  (0) 2009.11.30
인덱스~  (0) 2009.11.24
posted by LifeisSimple
prev 1 next

티스토리툴바