Left join in SQL Developer yields incomplete master/left table

Question

Usually, when performing a left join on two tables, all rows of the left table (A) are kept, and the right table (B) is only joined for matching join conditions, e.g. A.ID = B.ID. This works fine with small test data sets.

However, when I try to join set A with a considerably larger set B (i.e., the number of unique IDs in B is about 100 times the number of unique IDs in A) the result dataset includes only those rows of A that have matching IDs with B, which -- in my understanding -- is a normal (inner) join.

I get the desired result table by left joining set A with only those rows of set B that have matching IDs with set A, but I do not understand why the simple left join does not yield the same result.

Unfortunately, I cannot replicate the result with test data.

In general, are there possible reasons for a truncated left table after a left join?

EDIT:

set A:

ID  name
X1  AB
X2  XY
X3  VT
X4  ZY
X5  YZ
X6  KJ
X7  HA
X8  BK
X9  LM

set B:

ID  Var1
X1  blue
X11 red
X3  yellow
X4  blue
X12 yellow
X6  red
X7  orange
X7  blue
X8  green
X9  green
X10 blue

This gives a truncated set A:

select A.*, B.Var1 from
setA A 
left join setB B
on A.ID = B.ID
where B.Var1 = 'blue';

This gives what I want:

select A.*, B.Var1 from
setA A 
left join (select * from setB where Var1 = 'blue') B
on A.ID = B.ID;

I now understand that where placed after the join filters the join result, and that I need to see join and where as two separate tasks (correct?). However, it does not seem that natural to me (as a non-expert) that where B.Var1 = 'blue' filters the join result although it says B.Var1 and not only Var1, which I could understand more easily to refer to the join result. The B. suggests (to me) somehow to affect the left table used in the join.

Answer 1

TL/DR Use setA A left join setB B on A.ID = B.ID and B.Var1 = 'blue'.

all rows of the left table (A) are kept, and the right table (B) is only joined for matching join conditions, e.g. A.ID = B.ID"

It's not clear what you are trying to say by that.

left join on is defined to return inner join on rows plus unmatched left table rows extended by nulls. If not all rows of A are in the result then you added a where (or an inner join on or right join on) after the left join on. Whenever you left join on, have clear in your mind what associated inner join on you want; that's what determines the on.

Does this mean that a where clause is not allowed in [outer] joins?

That (comment) is also strange because where is never "in" a (outer or inner) join, it is always after any joins. (You can think of inner join as being like cross join and on as being like where but with higher precedence.)

This gives what I want:

select A.*, B.Var1 from
setA A 
left join (select * from setB where Var1 = 'blue') B
on A.ID = B.ID;

So does this, the "natural" phrasing:

select A.*, B.Var1 from
setA A
left join setB B
on A.ID = B.ID
and B.Var1 = 'blue';

However, it does not seem that natural to me (as a non-expert) that

inner join on is defined to return cross join rows that meet the (entire) on condition. Another way to describe this is that for every possible pair of a row from each input a combined row is made but only if it satisfies the (entire) on condition is it output. Another way to describe this is that for every possible pair of a row from each input only if they satisfy the (entire) on condition are they combined into an output row.

Some people seem to use one of the latter two interpretations but have misconceptions. They think that inner join on outputs something different than a cross join followed by filtering by the on. Or they think that a join can only be on a condition involving (whatever they mean by that) columns from both inputs--as if each must be mentioned in order to be available to be combined and possibly output. But no--the on condition can be anything and is just evaluated for each possible input row pairing.

CROSS JOIN vs INNER JOIN: CROSS JOIN = (INNER) JOIN = comma (",")
What is the difference between “INNER JOIN” and “OUTER JOIN”?
(Beware of the nonsense and poor writing in so many other answers there.)

where B.Var1 = 'blue' filters the join result although it says B.Var1 and not only Var1, which I could understand more easily to refer to the join result. The B. suggests (to me) somehow to affect the left table used in the join.

This is more unclear & strange phrasing. It seems like you have misconceptions that you need to let go of. It would probably help if you tried to force yourself to clearly express the quoted statements and also how you have expected joins with on to work. (A necessary condition for becoming/being an expert is to not tolerate sloppiness in reading, thinking or writing.)

Answer 2

Your query is technically fine conceptually. You simply forgot the (+) operator. It should be

select A.*, B.Var1 from
setA A 
left join setB B
on A.ID = B.ID
where B.Var1(+) = 'blue';  -- Notice the magic (+) symbol

The (+) symbol comes from the old style Oracle join syntax and might be pronounced as "Where B.Var1, if it exists, is equal to blue." Otherwise it'll only allow rows where the variable has a value that matches, which mean that it has to exist.

The more modern way to write it would be:

select    A.*, 
          B.Var1 
from      setA A 
left join setB B  on  A.ID   = B.ID
                  AND B.Var1 = 'blue';  

In other words, take it out of the where and keep it as part of the on condition by continuing it with and.