The table contains a NULL value that causes the notin subquery to fail.

Source: Internet
Author: User
There is a problem that the NULL value in the table causes the notin subquery to become invalid-this is the first SQL statement I wrote to the business. The data in the query was 0, and it was sent out without too much consideration. After the business showed me several pieces of test data, I found that the query results were incorrect. Br -- check whether the statement logic is correct. After troubleshooting, several rows of cust_id in BB are NULL, resulting in notin.

The table contains a NULL value that causes the not in subquery to become invalid. This is the first SQL statement I wrote to the business. The data obtained from the query is 0. At that time, it was sent out without too much consideration. After the business showed me several pieces of test data, I found that the query results were incorrect. Br -- check whether the statement logic is correct. After troubleshooting, several rows of cust_id in BB are NULL, resulting in not in


The table contains a NULL value that causes the not in subquery to fail.

-- This is the first SQL statement I wrote for my business. The data in the query is 0. At that time, I sent it without too much consideration. After the business showed me several pieces of test data, I found that the query results were incorrect.
-- Check whether the statement logic is correct. After troubleshooting, several rows of cust_id in BB are NULL, leading to the failure of the not in query result. Www.2cto.com


-- Statement before modification
Select * from AA a with (nolock)
Where a. status_code = '04'

And a. issue_date> = '2017-01-01 'and a. issue_date <= '2017-10-30'

And LEFT (a. manorg_code, 4) = '20140901'

And a. cust_id not in

(

Select aa. cust_id from BB aa with (nolock) where aa. sheet_type = 'Q' and aa. acttype_code in ('01', '02', '16', '19 ')

)

-- Modified statement

Select * from AA a with (nolock)

Where a. status_code = '04'

And a. issue_date> = '2017-01-01 'and a. issue_date <= '2017-10-30'

And LEFT (a. manorg_code, 4) = '20140901'

And a. cust_id not in

(

Select ISNULL (aa. cust_id, '') from BB aa with (nolock) where aa. sheet_type = 'Q' and aa. acttype_code in ('01', '02', '16', '19 ')

)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.