Sql Server Left Join and Where Clause

SQL Server LEFT JOIN and WHERE clause

SELECT ID, Name, Phone 
FROM Table1 
LEFT JOIN Table2 
ON Table1.ID = Table2.ID AND Table2.IsDefault = 1
WHERE Table1.ID = 12

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'  

How to replace a condition in left join through a where clause?

Your real tables probably look like this (primary keys bold):

• item (item_id, name, created_by_user_id)
• itemuserupdate (item_id, updated_by_user_id, name)
• users (user_id, name)

What you can do is get all user/item combinations first and then outer join the existing entries:

create myview as
select i.item_id, i.name, u.user_id, iu.name as name_by_user
  i.item_id,
  u.user_id,
from users u
cross join item i
left outer join itemuserupdate iu on iu.itemid = i.itemid
                                 and iu.updated_by_user_id = u.user_id;

You then then use this view with

select item_id, name, name_by_user from myview where user_id = 123;

SQL left join with filter in JOIN condition vs filter in WHERE clause

The big difference with the Where condition b.status is null or b.status in (10, 100)
is when b.status is say 1 as well as b.id=a.id

In the first query you will still get the row from table A with corresponding B part as NULL as On condition is not fully satisfied.
In the second query you will get the row in the JOIN for both a and b tables which will be lost in the where clause.

SQL LEFT JOIN with where clause from another column

Move AND Condition in WHERE clause to table joining condition (ON), When you use LEFT JOINED table in WHERE clause then its start behaving like INNER JOIN and only give result when there is exact match found.

SELECT `s`.`slider_id` as `id`, `s`.`slider_type` as `type`, `s`.`slider_name` as `name`, `sd`.`slider_data_type` AS `sd_type`, `sd`.`slider_data_value` AS `sd_value`, `sd`.`slider_data_group` AS `sd_group`, `sd`.`slider_data_id` AS `sd_id` 
FROM `sliders` AS `s` 
LEFT OUTER JOIN `slider_data` AS `sd` ON `s`.`slider_id` = `sd`.`slider_data_s_id` AND ( ( `sd`.`slider_data_active` = 1 AND `sd`.`slider_data_delete` =0 ) OR ( `sd`.`slider_data_active` IS NULL AND `sd`.`slider_data_delete` IS NULL ) )
WHERE `s`.`slider_id` = '11' 

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345.

The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

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.

Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?

The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done.

An example with Disney toons voting for president:

declare @candidates table (name varchar(50));
insert @candidates values 
    ('Obama'), 
    ('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values 
    ('Mickey Mouse', 'Romney'),
    ('Donald Duck', 'Obama');

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
        and v.voter = 'Donald Duck'

This still returns Romney even though Donald didn't vote for him. If you move the condition from the on to the where clause:

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
where   v.voter = 'Donald Duck'

Romney will no longer be in the result set.

Why did my WHERE clause affect my LEFT JOIN?

I was taught that, during the SQL's query processing, the JOIN clause would run before the WHERE clause, ensuring that the latter would look through the joined result.

That's the correct description of the SQL semantics, so what you're seeing is most likely a bug.

The actual implementation of an RDBMS is more complex. At a high level, the SQL query is parsed into a logical query plan, which is a tree that closely follows the structure of the input SQL. The optimizer is then responsible for converting the logical plan to the actual steps (physical operators) that will run to produce the result.

The logical plan of your query will be something like:

read MAIN_TABLE        read PRODUCTS
       \                  /
      join them on MAIN_TABLE.code = PRODUCTS.code
              |
       apply filter MAIN_TABLE.code LIKE '%ABC%'

The optimizer's job is to figure out the efficient way to execute this. It can do transformations like predicate pushdown, where the filter (MAIN_TABLE.code LIKE '%ABC%') is pushed to the "read" stage, so that only relevant rows are read. Then the optimizer can decide on the physical operation it will use to read the input table (e.g. full-scan vs index-based reads).

(This is speculation on my part.) The optimizer could also notice that since you're joining on code, only the PRODUCTS that satisfy PRODUCTS.code LIKE '%ABC%' can be matched, so it could push down the predicate to the PRODUCTS scan operator as well. Depending on the collation on the input tables, if the optimizer is not very careful, the semantics of the LIKE '%ABC%' predicate could change, resulting in the behavior you're seeing.

---

Related Topics