Обсуждение: select ..... not in .....
I folks. I'm struggling with a select which should be easy. select v_d_code, v_o_number, v_vin, v_status from vista_details where v_d_code='64340' and v_o_number='C0023';v_d_code | v_o_number | v_vin | v_status ----------+------------+-------------+-------------64340 | C0023 | GCDW7D37645 | COMPOUND IN (1 row) shows that the record exists in vista. select w_vin from walon where substring(w_vin from '(.{11}$)') = 'GCDW7D37645';w_vin ------- (0 rows) shows that it doesn't exist in walon. So, who does this select not return the row? select v_d_code, v_o_number, v_vin, v_status from vista_details where v_vin not in ( goole(# select substring(w_vin from '(.{11}$)') from walon);v_d_code | v_o_number | v_vin | v_status ----------+------------+-------+---------- (0 rows) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Gary Stainburn <gary.stainburn@ringways.co.uk> writes: > So, who does this select not return the row? > select v_d_code, v_o_number, v_vin, v_status from vista_details where v_vin > not in ( > goole(# select substring(w_vin from '(.{11}$)') from walon); NOT IN with a sub-select that returns any NULL values cannot succeed; the result is either FALSE (definite match) or NULL (because of the NULL comparison results). You could work around that with a COALESCE, but I think a less klugy and better-performing answer would be to write it as a left join: select v_d_code, v_o_number, v_vin, v_status from vista_details left join walon on (v_vin = substring(w_vin from '(.{11}$)')) where walon.some-never-null-column IS NULL; The where-clause rejects any actual join matches... regards, tom lane