Joins
-
Hi, I hope someone can help me understand this problem. two tables, A and B. Both tables have two columns; 1 and 2. Table 2 has lets say 12 rows while table 1 has 25. why do I get different results when I run the following;
select count(a.1)
from a
inner join b on (a.1 = b.1)
where a.1='some value'
vs.
select count(a.1)
from a
inner join b on (a.1 = b.1)
where b.1='some value'
-
If possible can you post some of the data and structure
Suggestion: replace the word WHERE with AND, and check if it makes any difference.
Which SQL(7,2000)
-
Better yet, check the Query plan.
I guess when you do a count(*) both results are the same?
-
The problem is you're using INNER JOINS. INNER JOINS play havoc whenever the data you're joining on is duplicated in both tables. It creates more records than you started out with. You should you left joins or right joins. For instance.
Select count(a.1) FROM a left join b on a.1 = b.1 WHERE a.1 = 'some value'
and
Select count(a.1) FROM b left join a on b.1 = a.1 WHERE a.1 = 'some value'
It depends on what the main table is. I always have a habit of laying my tables out from left to right relationship wise. Then I can easily figure out how to join the table with left or right joins depending on the result I wish to get. You run into this problem a lot when righting queries for reports. You can right the same query but mess up the join and get two different results. My reccomendation don't use inner join.
-
The problem is, when you use an inner join and then specify where t1.a='somevalue' you will only get back the values from both tables where t1.a shows up in the selected row.
For example:
Table One
A B C
------- ------- -------
1 red puppy
1 blue dog
3 green rabbit
Table Two
A B C
------- ------- -------
1 green dragon
1 blue dog
3 green rabbit
4 red puppy
This query:
Select Count(One.b)
from One
inner join Two on One.a=Two.a
where One.b='green'
this would return one row where if you were to use Two.b='green' you would return 2 rows.
I hope this clears it up a bit.
-
Thank you for responding. I've changed the "where" clause to an "and" and the result are still different running SQL 7. Table "A" with 12 rows is a lookup table with int data type. Table "B" also has int as data type.
Count(*) produces results same as my origional queries as well.
using left or right joins do not have any effect either.
I've also checked the query plan. The difference between the two queries is-- a bookmark lookup is used for the first query while a table scan is used for the second.
May be I'm making it more comlicated than it is, if so I apologize.
Thanks again.
-
Just did a test, and in my case, both queries return the same result.
Can you post DDL and data?
-
Thank you cliffb. The little light in my head is getting brighter!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply