Conditions in Left Join (Outer Join) VS Inner Join

What is the difference between INNER JOIN and OUTER JOIN?

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

SQL LEFT JOIN: difference between WHERE and condition inside AND

with a left join there is a difference

with condition on left join rows with column > 10 will be there filled with nulls

with where condition rows will be filtered out

with a inner join there is no difference

example:

declare @t table (id int, dummy varchar(20))
declare @a table (id int, age int, col int)

insert into @t
select * from (
values 
    (1, 'pippo'    ),
    (2, 'pluto'    ),
    (3, 'paperino' ),
    (4, 'ciccio'   ),
    (5, 'caio'     ),
    (5, 'sempronio')
) x (c1,c2)

insert into @a
select * from (
values 
    (1, 38, 2 ),
    (2, 26, 5 ),
    (3, 41, 12),
    (4, 15, 11),
    (5, 39, 7 )
) x (c1,c2,c3)

select t.*, a.age
from @t t
left join @a a on t.ID = a.ID and a.col > 10

Outputs:

id  dummy       age
1   pippo       NULL
2   pluto       NULL
3   paperino    41
4   ciccio      15
5   caio        NULL
5   sempronio   NULL

While

select t.*, a.age
from @t t
left join @a a on t.ID = a.ID
where a.col > 10

Outputs:

id  dummy       age
3   paperino    41
4   ciccio      15

So with LEFT JOIN you will get ALWAYS all the rows from 1st table

If the join condition is true, you will get columns from joined table filled with their values, if the condition is false their columns will be NULL

With WHERE condition you will get only the rows that match the condition.

Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

Consider the following example. We have two tables, DEPARTMENTS and EMPLOYEES.

Some departments do not yet have any employees.

This query uses an inner join that finds the department employee 999 works at, if any, otherwise it shows nothing (not even the employee or his or her name):

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  join employees b
    on a.department_id = b.department_id
 where b.employee_id = '999'

This next query uses an outer join (left between departments and employees) and finds the department that employee 999 works for. However it too will not show the employee's ID or his or her name, if they do not work at any departments. That is because of the outer joined table being used in the WHERE clause. If there is no matching department, it will be null (not 999, even though 999 exists in employees).

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  left join employees b
    on a.department_id = b.department_id
 where b.employee_id = '999'

But consider this query:

select a.department_id, a.department_desc, b.employee_id, b.employee_name
  from departments a
  left join employees b
    on a.department_id = b.department_id
   and b.employee_id= '999'

Now the criteria is in the on clause. So even if this employee works at no departments, he will still be returned (his ID and name). The department columns will be null, but we get a result (the employee side).

You might think you would never want to use the outer joined table in the WHERE clause, but that is not necessarily the case. Normally it is, for the reason described above, though.

Suppose you want all departments with no employees. Then you could run the following, which does use an outer join, and the outer joined table is used in the where clause:

select a.department_id, a.department_desc, b.employee_id
  from departments a
  left join employees b
    on a.department_id = b.department_id
 where b.employee_id is null

^^ Shows departments with no employees.

The above is likely the only legitimate reason you would want to use an outer joined table in the WHERE clause rather than the ON clause (which I think is what your question is; the difference between inner and outer joins is an entirely different topic).

A good way to look at is this: You use outer joins to allow nulls. Why would you then use an outer join and say that a field should not be null and should be equal to 'XYZ'? If a value has to be 'XYZ' (not null), then why instruct the database to allow nulls to come back? It's like saying one thing and then overriding it later.

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

Inner join or Left outer join based on condition

Always do the LEFT OUTER JOIN and add the WHERE condition to simulate INNER JOIN functionality for isMigrated = 0.

SELECT TOP 1000 *
FROM [tisonline].[dbo].[Jobs] AS j
LEFT OUTER JOIN [tisonline].dbo.JobQueries AS jq 
ON j.JobID = jq.JobID 
LEFT OUTER JOIN [tisonline]. dbo.Agents AS agt ON agt.AgentID = jq.AgentID
WHERE j.isMigrated = 1 
    OR agt.AgentID IS NOT NULL

left join and where condition in joining condition

You should not use column related to left table in where condition (this work as a INNER JOIN) move the condition for left join in the related ON clause

 select *  
 FROM   table1 t1
 left join table2 t2
          ON t1.id = t2.fk_id AND t2.id_number = 12174
 WHERE  t1.code = 'CODE1' ;

The where condition is the equivalent part of the INNER JOIN clause this is the reason that you have this behavior..

adding the condition to the on clause mean that also the added condition work as an outer join ..