Home » SQL & PL/SQL » SQL & PL/SQL » Tunned querry
Tunned querry [message #271898] Wed, 03 October 2007 05:49 Go to next message
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DELETE emp
WHERE 1=2;

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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #271914 is a reply to message #271907] Wed, 03 October 2007 06:36 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why? Where does 'DEPT' table come into the story in
DELETE emp
WHERE  deptno <= 30
OR     deptno >= 40;
Re: Tunned querry [message #271917 is a reply to message #271914] Wed, 03 October 2007 06:40 Go to previous messageGo to next message
Frank
Messages: 7880
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 #271919 is a reply to message #271907] Wed, 03 October 2007 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fair point - I was assuming this was the standard Emp & Dept tables.
Re: Tunned querry [message #271920 is a reply to message #271919] Wed, 03 October 2007 06:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yeah, but my assumption was that the original poster (over)simplified the actual problem
Re: Tunned querry [message #271933 is a reply to message #271917] Wed, 03 October 2007 07:02 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Tunned querry [message #271942 is a reply to message #271937] Wed, 03 October 2007 07:11 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You were?!? LOL!

Frank
If there is no FK on the emp.deptno column, there could be deptno's in emp that are not in dept, right?
Right!

Frank
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.
Right!

Thank you, and sorry for being such a pain.
Re: Tunned querry [message #271944 is a reply to message #271942] Wed, 03 October 2007 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No pain at all.
Glad you forced me to rethink things over.
Re: Tunned querry [message #271987 is a reply to message #271944] Wed, 03 October 2007 10:19 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are being just too kind Smile
Previous Topic: JOB's grant
Next Topic: sysdate
Goto Forum:
  


Current Time: Tue Dec 06 12:23:01 CST 2016

Total time taken to generate the page: 0.08484 seconds