 | |
Why does this query do this?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Why does this query do this? -
07-16-2008
, 07:48 AM
|
Hi, Ok, I need help with this. *Here is my data: TABLE A --------------- 2468 3690 1357 TABLE B --------------- 3690 1234 NULL 1357 NULL 8900 Ok, I want to get a list of items in TABLE A which do not exist in TABLE B. *Simple right? SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b); Guess what? *It does not work. *I get nothing. *If I use IN, it returns things, but if I use NOT IN I get nothing However, if I do this: SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b WHERE id IS NOT NULL); Well, it works fine. *Why do I need a NOT NULL on my subquery??? Thanks, John. |
have yet to see a "NOT IN" that couldn't be expressed as a "MINUS" and
get much better performance as a result. So "WHERE id NOT IN (SELECT
id FROM table_b)" can be expressed as
"WHERE id IN (SELECT id FROM table_a MINUS SELECT id FROM table_b)"
which will perform vastly better than using "NOT IN".
#12
| |||
| |||
|
|
Hi, Ok, I need help with this. *Here is my data: TABLE A --------------- 2468 3690 1357 TABLE B --------------- 3690 1234 NULL 1357 NULL 8900 Ok, I want to get a list of items in TABLE A which do not exist in TABLE B. *Simple right? SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b); Guess what? *It does not work. *I get nothing. *If I use IN, it returns things, but if I use NOT IN I get nothing However, if I do this: SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b WHERE id IS NOT NULL); Well, it works fine. *Why do I need a NOT NULL on my subquery??? Thanks, John. |
have yet to see a "NOT IN" that couldn't be expressed as a "MINUS" and
get much better performance as a result. So "WHERE id NOT IN (SELECT
id FROM table_b)" can be expressed as
"WHERE id IN (SELECT id FROM table_a MINUS SELECT id FROM table_b)"
which will perform vastly better than using "NOT IN".
#13
| |||
| |||
|
|
Hi, Ok, I need help with this. *Here is my data: TABLE A --------------- 2468 3690 1357 TABLE B --------------- 3690 1234 NULL 1357 NULL 8900 Ok, I want to get a list of items in TABLE A which do not exist in TABLE B. *Simple right? SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b); Guess what? *It does not work. *I get nothing. *If I use IN, it returns things, but if I use NOT IN I get nothing However, if I do this: SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b WHERE id IS NOT NULL); Well, it works fine. *Why do I need a NOT NULL on my subquery??? Thanks, John. |
have yet to see a "NOT IN" that couldn't be expressed as a "MINUS" and
get much better performance as a result. So "WHERE id NOT IN (SELECT
id FROM table_b)" can be expressed as
"WHERE id IN (SELECT id FROM table_a MINUS SELECT id FROM table_b)"
which will perform vastly better than using "NOT IN".
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.