Difference between on .. and and on .. where in SQL Left Join?
create table A(id int);
create table B(id int);
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);
INSERT INTO B VALUES(1);
INSERT INTO B VALUES(2);
INSERT INTO B VALUES(3);
SELECT * FROM A;
SELECT * FROM B;
id
-----------
1
2
3
id
-----------
1
2
3
Filter on the JOIN to prevent rows from being added during the JOIN process.
select a.*,b.*
from A a left join B b
on a.id =b.id and a.id=2;
id id
----------- -----------
1 NULL
2 2
3 NULL
WHERE will filter after the JOIN has occurred.
select a.*,b.*
from A a left join B b
on a.id =b.id
where a.id=2;
id id
----------- -----------
2 2
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.
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'
Where vs AND in LEFT JOIN
This version filters on a.id
:
select *
from #a a left join
#b b
on a.id = b.id
where a.id = 3
This version does not filter on a.id
:
select *
from #a a left join
#b b
on a.id = b.id and a.id = 3;
Why not? Go to the definition of the left join
. It takes all rows from the first table, regardless of whether the on
clause evaluates to true, false, or NULL
. So, filters on the first table have no impact in a left join
.
Filters on the first table should be in the where
clause. Filters on the second table should be in the on
clause.
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.
sql left join criteria in join vs where clause
If they can have multiple alerts, theoretically. However since you are excluding payments with alerts, this should not be a problem. If you were including them it could be. If this was a problem, you should use a "not in" subquery instead of left outer join since that can cause duplicate records if it's not 1:1.
Having criteria in the where clause excludes the entire row if it doesn't match the criteria. Having it in the join clause means the joined record is not shown but the "parent" is.
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.
Difference between Where and Join on Id
This is very good practice -- in fact, you cannot (easily) get this logic in a WHERE
clause.
A LEFT JOIN
returns all rows in the first table -- even when there are no matches in the second.
So, this returns all rows in the preceding tables -- and any rows from Client
where ClientId = 1
. If there is no match on that ClientId
, then the columns will be NULL
, but the rows are not filtered.
SQL INNER JOIN vs LEFT JOIN with a WHERE
Yes, they will return the same result. The left join without the where clause would read as show me all the records from the header table and the related items from the details table or null for the details where there are no matches.
Adding a where clause relating the ids effectively transforms the left join to an inner join by eliminating the non-matching rows that would have shown up as having null for the detail part.
In some databases, like MS SQL Server, the left join would show up as an inner join in the query execution plan.
Although you stated that you don't want Venn diagrams I can't help referring you to this question and its answers even though they are filled with (in my opinion very helpful) Venn diagrams.
Related Topics
How to Grant the Database Owner (Dbo) the External Access Assembly Permission
Sql-Only Find Time and Not Date in Access Date/Time Field
Retrieve the Maximum Length of a Varchar Column in SQL Server
How to Make Create or Replace View Work in SQL Server
What Is the Optimal Way to Compare Dates in Microsoft SQL Server
Sqlite Create Pre-Populated Fts Table
Dynamic SQL Column Value Duplicate and Difference Detection Merge Query
How to Take Last Four Characters from a Varchar
Concatenate a Selected Column in a Single Query
Why Would Year Fail with a Conversion Error from a Date
T-SQL Query:Getting Child Nodes of a Parent
Delete Statement in SQL Is Very Slow
How to Check Type of Value in Postgres
Rails: How to Find_By a Field Containing a Certain String
SQL Server:Get All String Occurences (Tags) from Nvarchar(Max) Variable Containing a JSON String
How to Create a One-Time-Use Function in a Script or Stored Procedure