OraFAQ Forum: SQL & PL/SQL » Exist Value in a table

Home » SQL & PL/SQL » SQL & PL/SQL » Exist Value in a table (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exist Value in a table [message #677873]

Fri, 18 October 2019 07:18

deepakdot
Messages: 93
Registered: July 2015

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

Report message to a moderator

Re: Exist Value in a table [message #677875 is a reply to message #677873]

Fri, 18 October 2019 08:11

Bill B
Messages: 1971
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.

Report message to a moderator

Re: Exist Value in a table [message #677892 is a reply to message #677875]

Mon, 21 October 2019 02:45

deepakdot
Messages: 93
Registered: July 2015

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.

Report message to a moderator

Re: Exist Value in a table [message #677894 is a reply to message #677892]

Mon, 21 October 2019 04:12

cookiemonster
Messages: 13975
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.

Report message to a moderator

Re: Exist Value in a table [message #677897 is a reply to message #677894]

Mon, 21 October 2019 04:27

deepakdot
Messages: 93
Registered: July 2015

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.

Report message to a moderator

Re: Exist Value in a table [message #677899 is a reply to message #677897]

Mon, 21 October 2019 08:57

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

select COUNT(*) from TABLE1 where COL1 = 'TEST' and rownum = 1

And index on COL1.

SY.

Report message to a moderator

Re: Exist Value in a table [message #677900 is a reply to message #677899]

Mon, 21 October 2019 09:20

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Ah, missed you want database independent SQL.

SY.

Report message to a moderator

Re: Exist Value in a table [message #677902 is a reply to message #677897]

Mon, 21 October 2019 11:03

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Exist Value in a table [message #677920 is a reply to message #677902]

Wed, 23 October 2019 02:39

deepakdot
Messages: 93
Registered: July 2015

Member

I agree that the COUNT is a bad solution. But for now we need to leave with this bad performance .
Thank you all.

Report message to a moderator

Previous Topic:	Help with questions i'm tying to validate from interview
Next Topic:	Two digit decimal

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 01 18:12:43 CDT 2026