SQL LEFT JOIN: difference between WHERE and condition inside AND

Question

What's the difference between

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

and

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

? Specifically, what's the difference when I put the condition on the table I am joining to the main table inside AND versus inside WHERE condition?

Good question. Create the tables, add some sample data and run the queries! — jarlh, Dec 12 '19 at 13:30
The important distinction is that with the `LEFT JOIN` condition, you filter out non-matching rows before joining the two tables together, thus only joining to matching rows — Martin, Dec 12 '19 at 13:37
Does this answer your question? [SQL join: where clause vs. on clause](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) — gbeaven, Dec 12 '19 at 14:55
This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Dec 13 '19 at 06:52

MtwStark · Accepted Answer · 2019-12-12T14:50:37.673

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.

What do you mean by "with condition on left join rows with column > 10 will be there filled with nulls"? I still cannot imagine it. — doremi, Dec 12 '19 at 13:56

score 0 · Answer 2 · answered Dec 12 '19 at 16:08

So what's the difference between them?

An explanation through examples:

CREATE TABLE Students
(
  StudentId INT PRIMARY KEY,
  Name VARCHAR(100)
);

✓

CREATE TABLE Scores
(
  ScoreId INT PRIMARY KEY,
  ExamId INT NOT NULL,
  StudentId INT NOT NULL,
  Score DECIMAL(4,1) NOT NULL DEFAULT 0,
  FOREIGN KEY (StudentId) 
    REFERENCES Students(StudentId)
);

✓

INSERT INTO Students
(StudentId, Name) VALUES
(11,'Joe Shmoe'),
(12,'Jane Doe'),
(47,'Norma Nelson');

✓

INSERT INTO Scores
(ScoreId, ExamId, StudentId, Score) VALUES
(1, 101, 11, 65.2),
(2, 101, 12, 72.6),
(3, 102, 11, 69.6);

✓

--
-- Using an INNER JOIN
--
-- Only Students that have scores
-- So only when there's a match between the 2 tables
--
SELECT stu.Name, sco.Score
FROM Students AS stu
INNER JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
ORDER BY stu.Name

Name      | Score
:-------- | :----
Jane Doe  | 72.6 
Joe Shmoe | 65.2 
Joe Shmoe | 69.6

--
-- Using an LEFT JOIN
--
-- All Students, even those without scores
-- Those that couldn't be matched will show NULL's
-- for the fields from the joined table
--
SELECT stu.Name, sco.Score, sco.ScoreId
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
ORDER BY stu.Name

Name         | Score | ScoreId
:----------- | :---- | :------
Jane Doe     | 72.6  | 2      
Joe Shmoe    | 65.2  | 1      
Joe Shmoe    | 69.6  | 3      
Norma Nelson | null  | null

--
-- Using an LEFT JOIN
-- But with an extra criteria in the ON clause
--
-- All Students again.
-- That have scores >= 66
-- But also the unmatched without scores
-- 
SELECT stu.Name, sco.Score, sco.ScoreId
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId

 AND sco.Score >= 66.0

ORDER BY stu.Name

Name         | Score | ScoreId
:----------- | :---- | :------
Jane Doe     | 72.6  | 2      
Joe Shmoe    | 69.6  | 3      
Norma Nelson | null  | null

--
-- Using an LEFT JOIN
-- But with an extra criteria in the WHERE clause
--
-- Only students with scores >= 66
-- The WHERE filters out the unmatched.
-- 
SELECT stu.Name, sco.Score
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId

WHERE sco.Score >= 66.0

ORDER BY stu.Name

Name      | Score
:-------- | :----
Jane Doe  | 72.6 
Joe Shmoe | 69.6

--
-- Using an INNER JOIN
-- And with an extra criteria in the WHERE clause
--
-- Only Students that have scores >= 66
--
SELECT stu.Name, sco.Score
FROM Students AS stu
INNER JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
WHERE sco.Score >= 66
ORDER BY stu.Name

Name      | Score
:-------- | :----
Jane Doe  | 72.6 
Joe Shmoe | 69.6

db<>fiddle here

Did you notice how the criteria in the WHERE clause can make a LEFT JOIN behave like an INNER JOIN?

SQL LEFT JOIN: difference between WHERE and condition inside AND

2 Answers2