Zthat I want to outer join from left to right: The first table,
A, contains the (primary keys) 1 through 7. I want my select to return each of these, that's why I use an outer join.
iis outer joined to
A.i = A2Z.ia (+).
(+)symbol indicates that a record should be returned even if there is no matching record. Note, the
(+)is on the side of the
=where "missing" records are expected.
Now, the records in
A2Z should be joined to
flg is equal to
y (colored green in the graphic above). For example, for the
A, I expected the query to return the
Z, for the
A I expect no matching record in
Z since the corresponding
flg is either
null or not equal to
This requirement can be implemented with a
A2Z.flg (+) = 'y'
(+) is on the side where missing records (or null values) are expected. Since
y is neither missing nor null, it goes to the other side.
A2Z needs to be joined to
A2Z.iz = Z.i (+)
Complete SQL Script
When run, the select returns the following records:
A_I Z ---------- - 1 a 2 3 4 5 6 7