Home » SQL & PL/SQL » SQL & PL/SQL » Is there any problem with this query?
Is there any problem with this query? [message #233042] Tue, 24 April 2007 07:53 Go to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
SELECT A FROM TABLE X WHERE A IN(SELECT A FROM TABLE X)

Is there any problem ?
Re: Is there any problem with this query? [message #233044 is a reply to message #233042] Tue, 24 April 2007 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Is there any problem with this query? [message #233054 is a reply to message #233044] Tue, 24 April 2007 08:26 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Michel Cadot wrote on Tue, 24 April 2007 20:55
No.

Regards
Michel


Thx!
Re: Is there any problem with this query? [message #233055 is a reply to message #233054] Tue, 24 April 2007 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be precise: maybe.
If you know exactly what's this query does.
If table has the correct storage.
If number of rows is correct.
If column is or is not indexed.
If...
There is no problem.

Syntaxically, it is correct.

Regards
Michel
Re: Is there any problem with this query? [message #233075 is a reply to message #233055] Tue, 24 April 2007 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it depends what you want it to do.
No matter what you want it to do, it is not the most effecient way of doing that thing.

If you want it to return all the data in table X, then it is wrong, as it will not return rows where A is null:
SQL> create table query_test (col_1  varchar2(4));

insert into query_test values (null);
insert into query_test values ('A');
insert into query_test values ('B');

select col_1 from query_test where col_1 in (select col_1 from query_test);

COL_
----
A
B


If you want it to return all the non-null values of A, then it will work, but it will be a lot slower than, say
SELECT A FROM X WHERE A IS NOT NULL;
Re: Is there any problem with this query? [message #233146 is a reply to message #233075] Wed, 25 April 2007 00:35 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Thank you all of you!
If I want only 1 A from TABLE X that equals 1 of the A in TABLE X and the value of A is unknown, how to write the query? Is that possible?

[Updated on: Wed, 25 April 2007 00:36]

Report message to a moderator

Re: Is there any problem with this query? [message #233152 is a reply to message #233146] Wed, 25 April 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select distinct a from x;

Regards
Michel
Re: Is there any problem with this query? [message #233179 is a reply to message #233152] Wed, 25 April 2007 02:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why the distinct? Surely the original query would return all the rows?

[Ignore this - I didn't see the OP's new requirement that you were replying to]

[Updated on: Wed, 25 April 2007 02:28]

Report message to a moderator

Re: Is there any problem with this query? [message #233184 is a reply to message #233179] Wed, 25 April 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The distinct is for the last requirement from the OP:
Quote:
If I want only 1 A from TABLE X that equals 1 of the A in TABLE X

Notice the "only 1".

Regards
Michel

[edit: ignore my message, I started to write it before your edit and I didn't see it]

[Updated on: Wed, 25 April 2007 02:44]

Report message to a moderator

Re: Is there any problem with this query? [message #233205 is a reply to message #233184] Wed, 25 April 2007 03:38 Go to previous message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Thank you everyone of you.
Previous Topic: Oracle 10G - Uning SQL Plus
Next Topic: Calling stored Procedures
Goto Forum:
  


Current Time: Sun Dec 04 18:48:17 CST 2016

Total time taken to generate the page: 0.14912 seconds