Shulou(Shulou.com)06/01 Report--

Editor to share with you what kind of pit Oracle will encounter when using NOT IN conditional query, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Plot restoration:

After putting into production last week, colleagues should have added a not in condition when querying a page. As a result, some of the data in the list were not displayed. After investigation, it was found that it was the problem of not in condition.

Recurrence of the problem:

1. Basic data: a total of 10 items of data status are empty-1 has been invalidated-4 reports have been approved-5

Select * from v_safe_testengineer v where v.vst_tester = 'w07387'

two。 After the addition of the not in 'invalidated' condition: a total of 5 data reports are approved-what are the 5 items? Why is the data with an empty status missing?

Select * from v_safe_testengineer v where v.vst_tester = 'w07387' and v.vst_note not in' is invalid'

Expected result: a total of 6 pieces of data status is empty-1 report approval-5 items

Solution:

3. Improved SQL: consistent with expected results

Select * from v_safe_testengineer v where v.vst_tester = 'w07387' and (v.vst_note not in' has been invalidated'or v.vst_note is null)

Ps: this solution can also solve this problem, but we all know that Oracle in with more than 1000 parameters will report "ORA-01795: the maximum number of expressions in the list is 1000"

4. Final plan > achieve the expected results

Select * from v_safe_testengineer v where v.vst_tester = 'w07387' and not exists (select a.id from v_safe_testengineer a where a.vst_tester =' w07387' and a.vst_note = 'invalidated' and a.id = v.id)

When stitching SQL conditions, do not use not in if the field value has NULL, but it is recommended that you use not exists and exists, which is relatively efficient.

The above is all the contents of the article "what pits will be encountered when Oracle uses NOT IN conditional query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.