CODEWITHSUNDEEP
sqldatabaseimpala
Andrews D
Andrews D

Reputation: 21

Left join returns fewer rows than select * on the left table?

I'm trying to do a left join with the right table. But on the left outer join, the results shows fewer rows on the left join than the select statement on the LHS table - why?

[hadoop-29:21000] > SELECT 
                  >   COUNT (*) 
                  > FROM 
                  >   state_vectors_data4 
                  >   left OUTER JOIN position_data4 ON (state_vectors_data4.lastcontact = position_data4.maxtime 
                  >   AND state_vectors_data4.icao24 = position_data4.icao24)
                  > WHERE 
                  >   state_vectors_data4.time >= 1677628800 
                  >   AND state_vectors_data4.time < 1677628810
                  >   AND state_vectors_data4.hour >= 1677628800 
                  >   AND state_vectors_data4.hour <= 1677632400 
                  >   AND state_vectors_data4.lat > 22.707434526056968 
                  >   AND state_vectors_data4.lat < 71.83985097021925 
                  >   AND state_vectors_data4.lon < -49.97631795370285 
                  >   AND state_vectors_data4.lon > -168.4528775341493 
                  >   AND position_data4.hour >= 1677628800 
                  >   AND position_data4.hour <= 1677632400;
+----------+
| count(*) |
+----------+
| 13260    |
+----------+

How is it possible that an inner join returns fewer rows than the select statement on the LHS table? What am I missing here?

[hadoop-29:21000] > SELECT 
                  >    COUNT(*) 
                  > FROM 
                  >   state_vectors_data4
                  > 
                  > WHERE
                  >   state_vectors_data4.time >= 1677628800 
                  >   AND state_vectors_data4.time < 1677628810
                  >   AND state_vectors_data4.hour >= 1677628800 
                  >   AND state_vectors_data4.hour <= 1677632400 
                  >   AND state_vectors_data4.lat > 22.707434526056968 
                  >   AND state_vectors_data4.lat < 71.83985097021925 
                  >   AND state_vectors_data4.lon < -49.97631795370285 
                  >   AND state_vectors_data4.lon > -168.4528775341493; 
+----------+
| count(*) |
+----------+
| 34210    |
+----------+

If I do a left join, won't the number of rows in the left table be maintained, if no match a null would be added from the right table?

Upvotes: 1

Views: 359

Answers (1)

M_S
M_S

Reputation: 3753

You have extra condition on right table

              >   AND position_data4.hour >= 1677628800 
              >   AND position_data4.hour <= 1677632400;

So probably here you are loosing some records

Upvotes: 0

Related Questions