SQL & PL/SQL

You do understand that ROWNUM is a pseudo-column created by Oracle that just assigns a number to a row retrieved by the query and Oracle makes NO guarantees that the same rownum will be assigned to the same record every time you execute the query?

Also what you're really asking is the SET equivalent of INTERSECT.

Table A has records (1, 2, 3).

Table B has records (3, 4, 5).

Table A INTERSECT Table B = records (3).

Is this what you're asking for?

Please as always, define your actual table definition (column with data types) and any insert statements you may have, and also format your code using the "Advanced Editor" option and Use Syntax Highlighting -> SQL.

How about

select count(*) into cnt

from (select 1 from t1 union all select 1 from t2);

Hi,

Yes this is one way to check if a table is empty. Depending on your logic, you can even use NULL instead of COUNT(*) and catch the NO_DATA_FOUND exception.

Hi,

944524 wrote:

Hi,

I am having two tables below where i need to check if any record present in either of two tables proceed something like this

select count(*) into cnt from T1 where rownum=1
select count(*) into cnt_1 from T2 where rownum=1


if cnt or cnt_1 > 0 then 
proceed 

OR expects a complete condition before the keyword "OR", as well as after it.

Correct syntax for 2 comparisons is like this:

IF  cnt > 0  OR cnt_1 > 0  THEN

To make it a little more efficient, you might want to eliminate the second query if the first one gets results, e.g.:

SELECT  COUNT (*)
INTO    cnt
FROM    t1
WHERE   ROWNUM = 1;

IF  cnt = 0  THEN
    SELECT  COUNT (*)
    INTO    cnt
    FROM    t2
    WHERE   ROWNUM = 1;
END IF;

IF  cnt > 0  THEN 
...

You mean to say use like below

select count(*) into cnt

from (select 1 from t1 where rownum = 1 union all select 1 from t2 where rownum = 1);

944524 wrote:

You mean to say use like below
select count(*) into cnt
from (select 1 from t1 where rownum = 1 union all select 1 from t2 where rownum = 1);

SELECT COUNT(T1.*)+COUNT(T2.*) CNT FROM T1, T2;

IF CNT > 0 THEN

This will impact the performance since both tables have huge data

Hi,

944524 wrote:

This will impact the performance since both tables have huge data

Really?  Isn't the whole point of this question that you don't know if either table has any data?

944524 wrote:

You mean to say use like below
select count(*) into cnt
from (select 1 from t1 where rownum = 1 union all select 1 from t2 where rownum = 1);

post EXPLAIN PLAN for SQL above.

Do you realize & understand that SELECT COUNT(*) only ever returns one row; but the COUNT() is still forced to spin thru table (or PK index).

I am having two tables below where i need to check if any record present in either of two tables proceed something like this

select count(*) into cnt from T1 where rownum=1
select count(*) into cnt_1 from T2 where rownum=1

if cnt or cnt_1 > 0 then 
proceed 

Ok - what is your question or issue?

What is it you need to 'proceed' with?

You need to actually ASK A QUESTION and give us ALL of the requirements and context.