SQL Left Join Losing Rows After Filtering

SQL Left Join losing rows after filtering

You are filtering on the second table in the where. The values could be NULL and NULL fails the comparisons.

Move the where condition to the on clause:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
     Table2 2
     ON 1.fk = 1.pk AND
        2.Date BETWEEN 1/1/2010 AND 12/31/2010    
GROUP BY 1.Label

Note:

The date formats retain the dates from the question. However, I don't advocate using BETWEEN for dates and the conditions should use standard date formats:

SELECT 1.Label, COUNT(2.values)
FROM Table1 1 LEFT JOIN
     Table2 2
     ON 1.fk = 1.pk AND
        2.Date >= '2010-01-01' AND
        2.Date < '2011-01-01'
GROUP BY 1.Label;

Some databases support the SQL Standard keyword DATE to identify date constants.

Rows are eliminated from LEFT JOIN when using OR in WHERE clause

in this query:

SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef
WHERE lt.RunID = rt.RunID OR rt.runid IS NULL

this part

SELECT *
FROM #LeftTable lt
LEFT OUTER JOIN #RightTable rt ON lt.PolicyRef = rt.PolicyRef

will give you 3 result:

100,'pol1','hi',80,'pol1','celec'

100,'pol2','hi2',90,'pol2','colorado'

100,'pol2','hi2',100,'pol2','colorado'

but the where statement want that set of result have the same id, so this is the only possible result:

100,'pol2','hi2',100,'pol2','colorado'

Explanation needed for missing rows with left join and count()

This is because NULL is not greater than or equal to anything; If you change your WHERE clause to where o.order_timestamp is null or o.order_timestamp >= '2011-01-05' then you will get the same behavior as your join clause limit.

Note though - I would recommend the join clause approach, as it matches more closely what you are trying to do. Also the change to the WHERE clause I mentioned above will only work if the order_timestamp column is not nullable -- if it is then you should use a different column for the null check (eg, where o.primarykey is null or o.order_timestamp >= '2011-01-05').

LEFT JOIN drops rows without filtering original table

Move all related invetory_to_pos clauses into LEFT JOIN, i.e.:

SELECT
    products.ID,
    products. NAME,
    products.VOLUME,
    productcombinations.PRODUCTID,
    productcombinations.PART,
    inventory_to_pos.FULLCOUNT
FROM
    products
LEFT JOIN productcombinations ON products.ID = productcombinations.PARTOF
LEFT JOIN inventory_to_pos ON products.ID = inventory_to_pos.PRODUCT AND (
    inventory_to_pos.POSID IS NULL
    OR inventory_to_pos.POSID = ?
)
WHERE
    products.INVENTORY = 1
AND products.AVAILABLE = 1
AND products.ID > 0

In LEFT JOIN, why did AND is not filtering out row in LEFT TABLE

Use where instead of condition in left join

select test1.*, test2.*
from test1
left join test2
on test1.id = test2.id
WHERE test1.ex_group = 'control'

SQL Server : left join results in fewer rows than in left table

Try this:

select * from TableA as a
left join (SELECT * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b
on a.id = b.id 

Or this:

select * from TableA as a
left join TableB as b on (a.id = b.id AND RealDate = '4/20/2013 12:00:00 AM')

Why would LEFT JOIN on a field to then later filter it out in WHERE clause?

The query you have in the question is basically equivalent to the following query:

SELECT ID, Name, Phone 
FROM Table1 
WHERE NOT EXISTS
(
    SELECT 1
    FROM  Table2 
    WHERE Table1.ID = Table2.ID
)

Meaning it selects all the records in Table1 that does not have a correlated record in Table2.

The execution plan for both queries will most likely be the same (Personally, I've never seen a case when they produce a different execution plan, but I don't rule that out), so both queries should be equally efficient, and it's up to you to decide whether the left join or the exists syntax is more readable to you.

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

---

Related Topics