SQL join result errors

Question

I'm trying to run this join and I'm not receiving the correct values.

My first query return like 25,000 record

SELECT count(*) from table1 as DSO,
table2 as EAR
WHERE
(UCASE(TRIM(EAR.value)) = UCASE(TRIM(DSO.value))
 AND 
UCASE(TRIM(EAR.value1) = UCASE(TRIM(DSO.value1))

my second Query return like 3,000,000

SELECT count(*) from table1 as DSO
left join table2 as EAR,
ON
(UCASE(TRIM(EAR.value)) = UCASE(TRIM(DSO.value))
 AND 
UCASE(TRIM(EAR.value1) = UCASE(TRIM(DSO.value1))

The total of records of the table 1 are like 45,000, thats what I Should recieve.

Answer 1

First query is an INNER JOIN and second one is a LEFT JOIN. You should expect quite different results. Also, look at the way db2400 treats NULLs with the UCASE and TRIM functions. My guess is that your left join is making some matches that you don't want.

The INNER JOIN in the first query is going to exclude any records from table1 that don't have a match in table2. That pretty quickly explains the lower count.

Either join will happily create more than one row for each record in table1 if it finds multiple matches in table2. The difference is that the LEFT JOIN will ALSO create one row for each record in table1 that doesn't have a match in table2. It sounds like you expect there to be a 1:1 match between the two tables, but that is not what you are getting.