Home » SQL & PL/SQL » SQL & PL/SQL » query explanation
query explanation [message #268505] Tue, 18 September 2007 23:44 Go to next message
msafana
Messages: 31
Registered: July 2007
Member
hi all,
i have one query,i want the explanation of this query.

select * from tablename where not exists in(select 1 from table name where keycolumnname=keycolumnname);

please anybody explain this query.

Thank u
Re: query explanation [message #268508 is a reply to message #268505] Tue, 18 September 2007 23:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

This query is not valid.
Re: query explanation [message #268531 is a reply to message #268505] Wed, 19 September 2007 00:31 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
msafana wrote on Tue, 18 September 2007 23:44
select * from tablename where not exists in(select 1 from table name where keycolumnname=keycolumnname);



in will not used after exists condition. And exists conditions used to tested for existence of rows in sub query. As your sub query return all record your full query return no record as you use not.
Re: query explanation [message #268534 is a reply to message #268505] Wed, 19 September 2007 00:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Either 'IN' or 'EXISTS' should be used.
I think those are two different tables, tablename and table.
Quote:
select *
from tablename
where not exists (select 1 -- assuming in is not there
from table name
where keycolumnname=keycolumnname);
Basically this will result out the rows which are in <tablename> and not in <table>.

You can achieve the same using the following query also, if the table structure is same.
select * from tablename
 minus
select * from table;
By
Vamsi
Re: query explanation [message #268536 is a reply to message #268505] Wed, 19 September 2007 00:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

By the way IN and EXISTS are not same. EXISTS returns boolean one.
Re: query explanation [message #268537 is a reply to message #268505] Wed, 19 September 2007 00:50 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
In Addition when you use in then you must specific column(s) before in.
Re: query explanation [message #269296 is a reply to message #268534] Fri, 21 September 2007 09:37 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
vamsi kasina wrote on Wed, 19 September 2007 00:39
Either 'IN' or 'EXISTS' should be used.
I think those are two different tables, tablename and table.
Quote:
select *
from tablename
where not exists (select 1 -- assuming in is not there
from table name
where keycolumnname=keycolumnname);
Basically this will result out the rows which are in <tablename> and not in <table>.

You can achieve the same using the following query also, if the table structure is same.
select * from tablename
 minus
select * from table;
By
Vamsi


Your example was not very clear, do the following


select *
from tablename a
where not exists 
(select null
 from table name b
 where a.keycolumnname=b.keycolumnname);
Previous Topic: DECODE AND CASE
Next Topic: Get details of college leavers
Goto Forum:
  


Current Time: Mon Dec 05 19:32:50 CST 2016

Total time taken to generate the page: 0.05973 seconds