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 ')
)