need SQL help (The Output does not contain correct number of rows)

Analytics
Enthusiast

need SQL help (The Output does not contain correct number of rows)

Hi,

I need your help on this issue.

I have a query between two tables A & B.

I get >100 rows in the result set when I write a query as follows:-

SELECT
A.id, B.id
FROM
table_a A,
table_b B
where
A.id=B.id;

But, no rows are returned when I write a query as follows:-

SELECT
A.id,B.id, A.F_KEY
FROM
table_a A,
table_b B
where
A.id=B.id;

Is there some thing I am missing when I write this query?

Please, help.

Thanks,
Sree
4 REPLIES
Enthusiast

Re: need SQL help (The Output does not contain correct number of rows)

what do the explain plans show between the two queries?
Enthusiast

Re: need SQL help (The Output does not contain correct number of rows)

The explain in the second case does the join whereas in the first case, there is no join and the output is a straight SELECT from table b.

The id field in table A and the id field in table B is same and it is "CUSTOMER_ID".

From tweaking more, I am into a feeling that the optimizer is getting into a thinking that the optimizer is not doing the join when (A.id, B.id) is the result set. But, when I use (A.id, B.id, A.), the join takes place and the result set is 0.

My new question is:- Why is the optimizer not doing the join operation?

Please share your thoughts.

Thanks,
Sree
Junior Contributor

Re: need SQL help (The Output does not contain correct number of rows)

Hi Sree,
check the table definition if there's a FOREIGN KEY WITH NO CHECK OPTION REFERENCES tableA(id)

The optimizer might do a join elimination if tableA.id is defined as NOT NULL (i.e. always the same as tableB.id), but when you select another column from tableA it has to do the join. TableA seems to be empty.

When you use soft/dummy RI you must check FKs manually within your load steps to avoiud such wrong answers.

Dieter
Enthusiast

Re: need SQL help (The Output does not contain correct number of rows)

Great answer Sir. that is exactly what happened. We have excellent practices here. Our ETL is getting better day-by-day.

Another item gets into the checklist now.

Sree