Using OUTER JOIN with a WHERE condition [duplicate]

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
– Isaiah3015, Commented Aug 16, 2017 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
– xQbert, Commented Aug 16, 2017 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
– Zohar Peled, Commented Aug 16, 2017 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
– Joe C, Commented Aug 16, 2017 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
– Keith, Commented Aug 16, 2017 at 14:24

Jens · Accepted Answer · 2017-08-16 14:12:40Z

0

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

answered Aug 16, 2017 at 14:12

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-08-16 14:13:10Z

0

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

answered Aug 16, 2017 at 14:13

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

Element Zero · Accepted Answer · 2017-08-16 14:14:22Z

0

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

answered Aug 16, 2017 at 14:14

Element Zero

1,7613 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Using OUTER JOIN with a WHERE condition [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related