Using OUTER JOIN with a WHERE condition

Question

I have two related tables like this:

CREATE TABLE Vehicle (
  Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(255) NOT NULL
);
INSERT INTO Vehicle (Id, Name) VALUES (1, 'Car');
INSERT INTO Vehicle (Id, Name) VALUES (2, 'Van');
INSERT INTO Vehicle (Id, Name) VALUES (3, 'Motorcycle');

CREATE TABLE Signal (
  Id INT NOT NULL PRIMARY KEY,
  VehicleId INT NOT NULL,
  Reading INT,
  StartTime DATE
);
INSERT INTO Signal (Id, VehicleId, Reading, StartTime) VALUES (1, 1, 10, '2016-12-10');
INSERT INTO Signal (Id, VehicleId, Reading, StartTime) VALUES (2, 1, 15, '2017-01-10');
INSERT INTO Signal (Id, VehicleId, Reading, StartTime) VALUES (3, 1, 4, '2017-02-10');
INSERT INTO Signal (Id, VehicleId, Reading, StartTime) VALUES (4, 2, 4, '2017-03-10');

When I perform an OUTER JOIN like this, it behaves as expected:

SELECT v.Name, SUM(s.Reading)
FROM Signal s
RIGHT OUTER JOIN Vehicle v ON v.Id = s.VehicleId
GROUP BY v.Name

(actual and expected result):
Car | 29
Motorcycle | NULL
Van | 4

But when I add a WHERE condition like this, it no longer does what I expect:

SELECT v.Name, SUM(s.Reading)
FROM Signal s
RIGHT OUTER JOIN Vehicle v ON v.Id = s.VehicleId
WHERE StartTime > '2017-01-01'
GROUP BY v.Name

(actual result):
Car | 19
Van | 4

(expected result):
Car | 19
Motorcycle | NULL
Van | 4

Why do I not get the expected result when I add the WHERE condition, and how do I fix it?

What are you expecting? By adding WHERE StartTime > '2017-01-01' you are only getting 3/4 of your Signal data — Isaiah3015, Aug 16 '17 at 14:12
Change `where` to `and` and it will work. The where clause is eliminating the nulls generated by the outer join, Basically your where clause is making your outer join behave like an inner join. — xQbert, Aug 16 '17 at 14:12
It's a duplicate of a duplicate. I would expect someone with over 3000 reputation points will know how to search stackoverflow. — Zohar Peled, Aug 16 '17 at 14:16
The join happens first. Because the result of the join has null for start time for motorcycle that record is filtered out. You can do as other have suggested and move to the on clause or you can also select records with null in start time. SELECT v.Name, SUM(s.Reading) FROM Signal s RIGHT OUTER JOIN Vehicle v ON v.Id = s.VehicleId WHERE StartTime > '2017-01-01' Or StartTime Is Null GROUP BY v.Name Name (No column name) Car 19 Motorcycle NULL Van 4 — Joe C, Aug 16 '17 at 14:18
Just to add an alternative. You can also use COALESCE or ISNULL, such as: SELECT v.Name, SUM(s.Reading) FROM Signal AS s RIGHT OUTER JOIN Vehicle AS v ON v.Id = s.VehicleId WHERE COALESCE(StartTime,'4000-01-01') > '2017-01-01' GROUP BY v.Name — Keith, Aug 16 '17 at 14:24

score 0 · Answer 1 · answered Aug 16 '17 at 14:12

because WHERE StartTime > '2017-01-01' makey your outer join to an join and removes all entries wich are not in table Signal.

Move the where condition to the on clause:

SELECT v.Name, SUM(s.Reading)
FROM Signal s
RIGHT OUTER JOIN Vehicle v ON v.Id = s.VehicleId AND StartTime > '2017-01-01'
GROUP BY v.Name

BTW: The column name StartTime is a little bit confusing, because it looks like you have date and time part. Better name ist StartDate

score 0 · Answer 2 · answered Aug 16 '17 at 14:13

Move the WHERE condition to the ON clause:

SELECT v.Name, SUM(s.Reading)
FROM Signal s
RIGHT OUTER JOIN Vehicle v
    ON v.Id = s.VehicleId AND
       StartTime > '2017-01-01'
GROUP BY v.Name

But normally LEFT JOIN seems to be more prevalent, so you could rewrite your query as this:

SELECT
    v.Name,
    SUM(s.Reading) AS reading_sum
FROM Vehicle v
LEFT JOIN Signal s
    ON v.Id = s.VehicleId AND
       s.StartTime > '2017-01-01'
GROUP BY v.Name

score 0 · Answer 3 · answered Aug 16 '17 at 14:14

You are using a right outer join so it's taking the Vehicle table and then joining to any matching records in the Signal table. The where clause is filtering the Signal table, therefore if there is no matching record in the Signal table for the Vehicle table to join on, it will be a NULL, then you use the where and are removing that result.

Perhaps what you want is to put the "StartTime > '2017-01-01'" in the ON cluse instead? Or perhaps you were looking to do a left outer join instead of a right. Hard to say without knowing your intentions

Using OUTER JOIN with a WHERE condition

3 Answers3