Home » SQL & PL/SQL » SQL & PL/SQL » Exist Value in a table (12c)
Exist Value in a table [message #677873] Fri, 18 October 2019 07:18 Go to next message
deepakdot
Messages: 28
Registered: July 2015
Junior Member
What is the Best way to find if there is at lease one row exist in a table.

select * from TABLE1
where exists (select 1 from TABLE1 where COL1 = 'TEST');

This list down all the Rows matching this filter. I am getting thousand of rows where COL1= 'TEST'.
But I just want to see if at least 1 row exist , it should stop the execution and come out.

select 1 from TABLE1 where COL1 = 'TEST' fetch first rows only;

I can use "fetch first rows only" in oracle, But we have a restriction of using this.

Thanks
Deepak

Re: Exist Value in a table [message #677875 is a reply to message #677873] Fri, 18 October 2019 08:11 Go to previous messageGo to next message
Bill B
Messages: 1917
Registered: December 2004
Senior Member
select * from TABLE1
where rownum = 1
and col1 = 'TEST';

will return only 1 record, picked at random that has the value of TEST in col1.
Re: Exist Value in a table [message #677892 is a reply to message #677875] Mon, 21 October 2019 02:45 Go to previous messageGo to next message
deepakdot
Messages: 28
Registered: July 2015
Junior Member
Thanks Bill. This works.

But actually the same SQL will be used for Oracle, DB2, SQL Server etc. "rownum" can be used only in Oracle, but not for others. Any other thoughts where it works for all database.
Re: Exist Value in a table [message #677894 is a reply to message #677892] Mon, 21 October 2019 04:12 Go to previous messageGo to next message
cookiemonster
Messages: 13755
Registered: September 2008
Location: Rainy Manchester
Senior Member
This may work:

select min(1) from TABLE1 where COL1 = 'TEST';

It'll return a single 1 if there's any matching rows and null otherwise.
You can use any constant in place of 1.
Re: Exist Value in a table [message #677897 is a reply to message #677894] Mon, 21 October 2019 04:27 Go to previous messageGo to next message
deepakdot
Messages: 28
Registered: July 2015
Junior Member
Now we are using this which works for all.

select COUNT(*) from TABLE1 where COL1 = 'TEST';

If the output > 0, then it has row else no rows. But COUNT or MIN will scan all the data. this Table has millions of row.
Re: Exist Value in a table [message #677899 is a reply to message #677897] Mon, 21 October 2019 08:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
Registered: January 2010
Location: Connecticut, USA
Senior Member
select COUNT(*) from TABLE1 where COL1 = 'TEST' and rownum = 1

And index on COL1.

SY.
Re: Exist Value in a table [message #677900 is a reply to message #677899] Mon, 21 October 2019 09:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, missed you want database independent SQL.

SY.
Re: Exist Value in a table [message #677902 is a reply to message #677897] Mon, 21 October 2019 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

COUNT(*) is a bad solution as it means you scan ALL rows (at least with value TEST1) when you need to know if there is one.

But you want a SQL independent of the DBMS, so you will have to leave with bad performances UNLESS your client tool is smart and tests the DBMS to send the most efficient query for it.

Re: Exist Value in a table [message #677920 is a reply to message #677902] Wed, 23 October 2019 02:39 Go to previous message
deepakdot
Messages: 28
Registered: July 2015
Junior Member
I agree that the COUNT is a bad solution. But for now we need to leave with this bad performance .
Thank you all.
Previous Topic: Help with questions i'm tying to validate from interview
Next Topic: Two digit decimal
Goto Forum:
  


Current Time: Tue Dec 10 00:36:12 CST 2019