Left Outer Join and an Additional Where Clause

Left Outer join and an additional where clause

Yes, put the condition (called a predicate) in the join conditions

   Select [stuff]
   From TableA a
       Left Join TableB b
           On b.Pk = a.Pk
               -- [Put your condition here, like this]
               And b.Column = somevalue

The reason this works is because the query processor applies conditions in a where clause after all joins are completed, and the final result set has been constructed. So, at that point, a column from the a table on the outer side of a join that has null in a a column you have established a predicate on will be excluded.

Predicates in a join clause are applied before the two result sets are "joined". At this point all the rows on both sides of the join are still there, so the predicate is effective.

LEFT OUTER JOIN with WHERE clause on 2nd table, not effecting 1st table

use product_details.detail_type = 'ALTERNATIVE NAMES' condition in ON Clause instead of where Clause

SELECT products.code, products.name, product_details.product_code, product_details.detail_type, product_details.description
FROM products 
LEFT OUTER JOIN product_details 
ON products.code = product_details.product_code 
and product_details.detail_type = 'ALTERNATIVE NAMES'

Left outer join with Where Clause

The problem us the WHERE conditions are executed after the join is made, so soh.CalYearWeek=1512 will only be true for successful joins - missed joins have all nulls, and the where clause filters them out.

The solution is simple: Move the condition into the join:

SELECT [Product_Code], [Product_Desc], r.store, soh.SOH
FROM [Product Range] as r
LEFT JOIN [dbo].SOH as soh on r.[Product_Code] = soh.PRODUCT_Code 
   AND r.store = soh.store
   AND soh.CalYearWeek=1512

Conditions on the join are executed as the join is being made, so you'll still get your left join, but only to rows in the right table that have that special condition.

Putting non-null conditions on the right table in the WHERE clause effectively turns a LEFT join into an INNER join, since the right table can only have a non-null value if the join was successful.

Sql LEFT OUTER JOIN with WHERE clause

Move the constraint to your on clause.

select *
from request r
left join requestStatus rs
on r.requestID = rs.requestID
--and status_id = 1
and status_id <> 2

What's happening to you is that the outer join is performed first. Any rows coming from the outer join that don't have matches will have nulls in all the columns. Then your where clause is applied, but since 1 <> null, it's not going to work like you want it to.

EDIT: Changed on clause based on Piyush's comment.

Left Join With Where Clause

The where clause is filtering away rows where the left join doesn't succeed. Move it to the join:

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

LEFT OUTER JOIN with a WHERE clause

Try:

SELECT WO_BreakerRail.ID, indRailType.RailType, WO_BreakerRail.CreatedPieces, 
    WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged,
    WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop
FROM indRailType
LEFT OUTER JOIN WO_BreakerRail ON indRailType.RailCode = WO_BreakerRail.RailCode 
            AND WO_BreakerRail.Date = @Date

Thus adding AND WO_BreakerRail.Date = @Date onto the join

WHERE clause ignoring rows in LEFT OUTER JOIN

Remove the condition from where clause and add it to the join:

SELECT
      apc.id AS clinic,
      apc.region,
      apc.country,
      apc.continent,
      apc.area,
      vrm.id AS ym,

    SUM(CASE test_value_range_id WHEN '1124_1' THEN number_of_values ELSE 0 END) AS avf

FROM result_month vrm
    LEFT JOIN  test_value_in_range_count vt on  vrm.id = vt.result_month_id and (vt.test_value_range_id IN ('1124_1', '1124_2', '1124_3', '1124_4', '1124_5')) AND (vt.patient_sub_set_id = 'ALL')
    LEFT OUTER JOIN clinic apc on vt.clinic_id = apc.id

GROUP BY apc.id, 
    apc.region, 
    apc.country, 
    apc.continent,
    apc.area, 
    vrm.id   

    ; 

Else the where clause makes a join from your left join

MySQL Left outer join with where clause

On the query without the account number in the join, the join does match row in the last_24_toplines table, but it does not match the account number in the where clause, so it is filtered out and not seen as a row from top_lines without a matching row from last_24_topline.

So, for example this row from the top_line table

Will match this row from the last_24_toplines

but both will then be filtered out, because the account_no didn't match what was in the where clause: WHERE l.account_no = 32049 OR l.account_no IS NULL

The query with the check for account_no within the join will still match the row from top_line, but will not have a matching row from last_24_topline, so you will get a row with top_lines data with null last_24_topline.

LEFT-JOIN instead of oracle (+) with where clause

You want to INNER JOIN to b and then LEFT OUTER JOIN to c:

FROM  a
      INNER JOIN b
      ON (b.c = a.c)
      LEFT OUTER JOIN c
      ON (a.d = c.d)
WHERE b.a  = '101'
AND   a.a    = '202'
AND   ROWNUM = 1;

---

Your query:

FROM a,b,c
WHERE b.a  = '101'
AND a.a    = '202'
AND b.c    = a.c
AND a.d    = c.d(+)
AND ROWNUM = 1;

A comma in the FROM clause signifies a CROSS JOIN.

However, in the WHERE clause you have:

• b.c = a.c which changes the join condition between tables a and b from a CROSS JOIN to an INNER JOIN; and
• a.d = c.d(+) which changes the join condition between tables a and c from a CROSS JOIN to a LEFT OUTER JOIN.

---

Related Topics