LEFT JOIN to take all rows from left table irrespective of join condition

Question

I am trying to do a left join such that I get all rows from left table even when the join condition is not true for those rows.

Example: Below Aid = Bid but Table B has only 3 ids

The Query that I am using gives only rows where the join condition is true. Is there a way to get all rows from left table even when the Join condition is not true?

My Query:

SELECT Table1.Aid,
       Table2.Bid,
       Table2.Issueid
  FROM Table1
       LEFT JOIN Table2 ON Table1.Aid = Table2.Bid;

 WHERE Table2.Issueid IN (
    'a',
    'b'
)

Is that `where` clause part of the same query? There is a semicolon before it. If by the 'left' table you mean Table2 (on the right in your screenshot), your `where` clause specifies that you only want rows where Table2.Issueid is 'a' or 'b', so the rows where Table2.Issueid is null will be excluded. — William Robertson, Aug 08 '18 at 23:08
Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. PS That is what you are getting & you are removing rows after via the where. If you don't want that, explain exactly what rows you do want. Please read & act on [mcve]. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. — philipxy, Aug 09 '18 at 08:51
Possible duplicate of [Left Outer join and an additional where clause](https://stackoverflow.com/questions/3677246/left-outer-join-and-an-additional-where-clause) — philipxy, Aug 09 '18 at 09:05

Aaron Dietz · Accepted Answer · 2018-08-08T21:16:29.370

10

Move your WHERE to your ON (join predicate):

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 LEFT JOIN Table2
ON Table1.Aid = Table2.Bid
   AND Table2.IssueId IN ('a','b');

A WHERE that filters on the right side table essentially makes your LEFT JOIN into an INNER JOIN. It filters out the NULL rows.

Keep in mind, this will now return NULL for the Table2.Bid = 3 row, since it doesn't meet the Table2.IssueId IN ('a','b') condition.

edited Aug 08 '18 at 21:16

answered Aug 08 '18 at 21:13

Aaron Dietz

10,137
1
14
26

Yes you are right. I added a Where clause, I think my where clause is causing this issue as its turning my JOin into a inner join. Is there a work arround this? – Programmermid Aug 08 '18 at 21:15
@Programmermid Almost always the solution is to move the `WHERE` to the join predicate with `AND`. In your case you would get the expected results by leaving that part out all together, but I suspect in your real query you will want to move the `WHERE` to the `ON`. – Aaron Dietz Aug 08 '18 at 21:20
You don't know what the query should be, eg that the where condition should be anded into the join on, so it is wrong to say that @Programmermid should do that--because they do not clearly say what they want--only that they want all left table rows & their query is wrong. – philipxy Aug 09 '18 at 09:16
@philipxy OP expected their query to retain the all the left table rows because they are using `LEFT JOIN`, and asked why it isn't. The answer is 100% because of the `WHERE` clause filter on the right table. Moving the `WHERE` condition to `ON` is the correct (albeit general) solution to give until they say otherwise. – Aaron Dietz Aug 09 '18 at 14:05

Gordon Linoff · Answer 2 · 2018-08-08T21:23:34.380

0

I'm surprised your query runs. You should have an ON clause:

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 LEFT JOIN
     Table2
     ON Table1.Aid = Table2.Bid;

For the revised query, you still need to move the condition into the ON clause:

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 LEFT JOIN
     Table2
     ON Table1.Aid = Table2.Bid AND Table2.IssueId IN ('a','b');

edited Aug 08 '18 at 21:23

answered Aug 08 '18 at 21:00

Gordon Linoff

1,242,037
58
646
786

This query will only give three rows instead of expected five. – Programmermid Aug 08 '18 at 21:06
@Gordon Linoff - You left out the predicate on the join – Dan Aug 08 '18 at 21:23
@Dan . . . I don't understand your comment. – Gordon Linoff Aug 08 '18 at 21:25
1

@GordonLinoff - In your original answer, you left off the `AND Table2.IssueId IN ('a','b')`, which you've now corrected. – Dan Aug 08 '18 at 21:27
@Dan . . . I would call that a filtering condition, not a predicate on the `join`. In any case, that wasn't actually in the question when I answered it (although I've updated the answer). – Gordon Linoff Aug 08 '18 at 23:47
https://stackoverflow.com/questions/3677246/left-outer-join-and-an-additional-where-clause – Dan Aug 09 '18 at 02:57
https://stackoverflow.com/questions/34454867/difference-between-filtering-queries-in-join-and-where – Dan Aug 09 '18 at 03:17

Dan · Answer 3 · 2018-08-08T22:38:08.340

0

I think you need a left outer join in this situation without the WHERE clause.

For example:

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 
LEFT OUTER JOIN Table2 ON Table1.Aid = Table2.Bid 
                       AND Table2.IssueId IN ('a','b')

edited Aug 08 '18 at 22:38

answered Aug 08 '18 at 21:14

Dan

979
1
8
29

score 0 · Answer 4 · answered Aug 08 '18 at 23:21

Based on your desired result the common answer of

  ON Table1.Aid = Table2.Bid 
  AND Table2.IssueId IN ('a','b')

won't give you the results you want. Specifically row 3 will be

3, null, null

not

3, 3, null

If that is what you actually want you'd get that simply by doing the left join without any where clause at all.

LEFT JOIN to take all rows from left table irrespective of join condition

4 Answers4