Left Outer Join Doesn't Return All Rows from My Left Table

Left Outer Join doesn't return all rows from my left table?

Nanne's answer given explains why you don't get the desired result (your WHERE clause removes rows), but not how to fix it.

The solution is to change WHERE to AND so that the condition is part of the join condition, not a filter applied after the join:

SELECT day.days, COUNT(*) as opens 
FROM day 
LEFT OUTER JOIN tracking
ON day.days = DAY(FROM_UNIXTIME(open_date)) 
AND tracking.open_id = 10 
GROUP BY day.days

Now all rows in the left table will be present in the result.

LEFT JOIN query not returning all rows in first table

Move the b condition from WHERE to ON to get a real LEFT JOIN. (With the b condition in the WHERE clause, it executes as a regular inner join...)

select a.id, a.name, b.store, b.stock
from products a left join stock b
  on a.id = b.id and b.store = '001'
order by a.id

Left outer join not fetching all records of left table

The between condition does not allow nulls add left join is transformed to inner. Add OR b.emp_id is NULL (join key) this will allow not joined records, no need to add the same conditions for all columns used in the between.

    select *
    from A 
    LEFT JOIN B ON a.emp_id=b.emp_id
    LEFT JOIN C on a.emp_id=c.emp_id 
    where ((A.date between B.appl_d and B.expr_d) OR b.emp_id is NULL)
          and
          ((a.date between c.del_d and c.fin_d) OR c.emp_id is NULL)

And this is a test:

with 
A as
(    
     select stack(3,100,'2019-01-13',
                    200,'2019-01-13',
                    300,'2019-01-13'
                 ) as (emp_id, date)
 ),

 B as (
 select stack(1,100,'2019-12-30','3000-01-01') as (emp_id, appl_d, expr_d)
 ),

 C as
 (
 select stack(1,100,'2015-06-07', '9999-12-31') as (emp_id, del_d, fin_d)
 )

 select A.*
   from A 
        LEFT JOIN B ON a.emp_id=b.emp_id
        LEFT JOIN C on a.emp_id=c.emp_id 
   where ((A.date between B.appl_d and B.expr_d) OR b.appl_d is NULL) 
          and
         ((a.date between c.del_d and c.fin_d) OR c.emp_id is NULL)   

Result:

OK
200     2019-01-13
300     2019-01-13
Time taken: 84.475 seconds, Fetched: 2 row(s)

Obviously this approach does not work. emp_id=100 should be in the dataset returned.

And the question is interesting, I will continue investigating a bit later. You guys can use my test to find the working solution.

LEFT join not returning all rows from LEFT table

This is now solved. The second SQL statement in my question was in fact correct but because it was taking null data from the left table into my concatenated string it wasn't bringing any data back because of an "invalid use of null" error. I had thought the concatenation had been working ok but this was only on the statements that excluded the Null data through the incorrect use of WHERE.

Here is my final code.

SELECT DAYS.Day, IIF(DATA.Hours is null, DATA.Hours, (LEFT(DATA.Adj_Type,1) + ' ' + Cstr(DATA.Hours) + ' Hrs')) 
FROM DAYS LEFT OUTER JOIN DATA ON (DAYS.Day = DAY(DATA.Date) AND (MONTH(DATA.Date)=4) AND (DATA.Deleted Is Null) AND (DATA.Name='Joe Bloggs')) 
ORDER BY DAYS.Day 

Hope this may help someone else in the future.

SQL Left Outer Join not returning all rows from left table (no where clause filter)

Your outer join works fine.

You probably mean partitioned outer join.

See the additional query_partition_clause in the join

PARTITION BY (sender) only this join will fill the gaps in sender as you expects.

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  left outer join t2
  PARTITION BY  (sender)
    on t1.product = t2.product   
 order by 2, 1


OV_PRODUCT         SE      ITEMS
------------------ -- ----------
Product 1          AT        100
Product 2          AT         25
Product 3          AT           
Product 4          AT         57
Product 1          GR         45
Product 2          GR         22
Product 3          GR          5
Product 4          GR          4

Left join doesn't return all results from first table

Your WHERE clause is making this into an INNER JOIN. Put that in the ON clause instead:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
AND un.User_Id = 400;

Oracle, LEFT OUTER JOIN not returning all rows from left table, instead behaving like INNER JOIN

If ORG_NO is within the RF_MERCHANT_ORG table (using aliases consistently would help there) then acting like an inner join would then would be the correct result for the SQL being used.

The join should be this to make it act like a proper left join:

LEFT OUTER JOIN RF_MERCHANT_ORG MO ON ROW_SEG.DEPT_NO = MO.DEPT_NO AND MO.ORG_NO = 100

LEFT JOIN to take all rows from left table irrespective of join condition

Move your WHERE to your ON (join predicate):

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 LEFT JOIN Table2
ON Table1.Aid = Table2.Bid
   AND Table2.IssueId IN ('a','b');

A WHERE that filters on the right side table essentially makes your LEFT JOIN into an INNER JOIN. It filters out the NULL rows.

Keep in mind, this will now return NULL for the Table2.Bid = 3 row, since it doesn't meet the Table2.IssueId IN ('a','b') condition.

How can a LEFT OUTER JOIN return more records than exist in the left table?

The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.

If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN.

EDIT:
In response to your edit, I've just had a further look at your query and it looks like you are only returning data from the LEFT table. Therefore, if you only want data from the LEFT table, and you only want one row returned for each row in the LEFT table, then you have no need to perform a JOIN at all and can just do a SELECT directly from the LEFT table.

---

Related Topics