Tunned querry [message #271898] |
Wed, 03 October 2007 05:49  |
donind
Messages: 95 Registered: February 2007
|
Member |
|
|
Hi,
Below is my querry
what be the alternative efficient ways of writing the same querry
delete from emp where deptno in (select deptno from dept where deptno<=30 and deptno >=40;
Thanks in advance
[mod-edit] removed illiterate IM speak.
[Updated on: Wed, 03 October 2007 07:50] by Moderator Report message to a moderator
|
|
|
Re: Tunned querry [message #271902 is a reply to message #271898] |
Wed, 03 October 2007 05:57   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There will be no rows returned from your subquery, as there are no values of DEPTNO which are simultaneously <=30 and >=40.
If the AND in the subquery is meant to be an OR then you could use:DELETE emp
WHERE deptno <= 30
OR deptno >= 40;
|
|
|
Re: Tunned querry [message #271907 is a reply to message #271902] |
Wed, 03 October 2007 06:17   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
JRowbottom wrote on Wed, 03 October 2007 12:57 | If the AND in the subquery is meant to be an OR then you could use:DELETE emp
WHERE deptno <= 30
OR deptno >= 40;
|
In addition: This is only valid if emp.deptno is in a foreign key to dept.deptno.
[Updated on: Wed, 03 October 2007 06:18] Report message to a moderator
|
|
|
|
Re: Tunned querry [message #271917 is a reply to message #271914] |
Wed, 03 October 2007 06:40   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This is NOT the same as the original query.
The original only wanted to delete rows where deptno was found in dept
|
|
|
|
|
Re: Tunned querry [message #271933 is a reply to message #271917] |
Wed, 03 October 2007 07:02   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Sorry, Frank, I understood you are referring to JRowbottom's DELETE statement. But nevertheless: the (simplified) original statement is:
delete from emp
where deptno in (select deptno from dept where deptno <= 30)
Now, why is this only valid if emp.deptno is in a foreign key to dept.deptno? If deptno's were used in a referential integrity constraint, it wouldn't be possible to have a record in the 'EMP' table with a 'deptno' different from existing 'dept.deptno' values. Right?
If, on the other hand, there wasn't foreign key constraint there, why would this DELETE statement be invalid?
I have a feeling that I'm missing something here, but don't know what.
|
|
|
Re: Tunned querry [message #271937 is a reply to message #271933] |
Wed, 03 October 2007 07:06   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I WAS referring to JRowbottoms delete.
If there is no FK on the emp.deptno column, there could be deptno's in emp that are not in dept, right?
That would mean that JRowbottoms delete (after the and-or correction he made) would delete those rows, whereas the original query (also with a fixed and-or) would not.
|
|
|
|
|
|