Search code examples
sqloraclejoinoracle-sqldeveloper

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

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

enter image description here

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'
)
Solution

  • 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.