Oracle SQL Full Outer Joins

Question

My tables are as follows:

SELECT * FROM r_a;

CLASS   SECTION
-----   -------
1       A      
1       B      
2       A      
2       B      


SELECT * FROM r_b;

CLASS   SECTION
-----   -------
1       B      
1       C      
2       B      
2       C

I want to perform a full outer join on these tables over the column SECTION where CLASS=1 in both tables. My desired output is :

SECTION SECTION_1
------- ---------
B       B        
A       (Null)   
(Null)  C

However, the following query yields only the matching row, similar to result of an inner join.

**QUERY 1**

SELECT a.section, b.section 
FROM 
r_a a 
FULL OUTER JOIN 
r_b b 
ON a.section=b.section 
WHERE A.class=1 AND B.class=1

SECTION SECTION_1
------- ---------
B       B

I am able to achieve desired result by taking the where conditions inside a nested query:

**QUERY 2**

SELECT a.section, b.section  
FROM
(SELECT SECTION FROM r_a WHERE class=1) a 
FULL OUTER JOIN 
(SELECT SECTION FROM r_b WHERE class=1) b
ON a.section=b.section 

SECTION SECTION_1
------- ---------
B       B        
(Null)  C        
A       (Null)

The result is more surprising when the where conditions in **Query 1** is moved to the on clause:

** Query 3**

SELECT a.section, b.section 
FROM 
r_a a 
FULL OUTER JOIN 
r_b b 
ON a.section=b.section AND  A.class=1 AND B.class=1

SECTION SECTION_1
------- ---------
B       B        
(Null)  C        
(Null)  B        
(Null)  C        
B       (Null)   
A       (Null)   
A       (Null)   


MORE CLARITY:
*************

SELECT *
FROM 
r_a a 
FULL OUTER JOIN 
r_b b 
ON a.section=b.section AND  A.class=1 AND B.class=1


CLASS   SECTION CLASS_1 SECTION_1
------  ------- ------- ---------
1       B       1       B        
(Null)  (Null)  1       C        
(Null)  (Null)  2       B        
(Null)  (Null)  2       C        
2       B       (Null)  (Null)   
2       A       (Null)  (Null)   
1       A       (Null)  (Null)

Explanation on why Query 1 doesn't produce the desired result and why Query 3 joins on class=2 even when the on clause says AND A.class=1 AND B.class=1 is greatly appreciated.

That is not the output from those queries because the column names don't agree with the code. Please in code questions give a [mcve]--cut & paste & runnable code & desired output & clear specification & explanation. You seem to want to have a different name in r_b or to rename section to section_1 in a subquery before the join. — philipxy, Apr 28 '19 at 09:59
Learn what FULL JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left/both table column to be not NULL after an OUTER JOIN ON removes any rows from the table(s) extended by NULLs, ie leaves only LEFT/RIGHT/INNER [sic] JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Apr 28 '19 at 09:59

score 1 · Answer 1 · answered Nov 17 '17 at 20:47

You first query is 100% perfect, just change your WHERE statement to OR instead of AND.

SELECT a.section, b.section 
FROM 
r_a a 
FULL OUTER JOIN 
r_b b 
ON a.section=b.section 
WHERE A.class=1 OR B.class=1

That is essentially what you are doing when you do your second attempt with the subqueries.

Keeping in mind that your WHERE clause is executed AFTER the FROM clause. When you use AND here you are saying "Only take results AFTER the join where both conditions are true." Obviously this dumps results when one table has a condition = 1 and the other does not. With the "OR" you are saying "Take results AFTER the join where either condition is true".

doing as you say returns additional rows of A.class=1, B.class=2 for section=B and A.class=2,B.class=1 for section=B. Please simulate and see for yourself. — Raghu, Nov 17 '17 at 21:52
The output columns in the question don't agree with its code or your code. — philipxy, Apr 28 '19 at 10:01

score 1 · Answer 2 · answered Nov 18 '17 at 05:46

Be careful, condition where vs condition on is difference:

where is a filter which is applied after rows are selected using the join. It is not always the case that a join ... on condition is sematically equivalent to a where condition. You can see more detail here.

Your Query 1 <=>

select asection, bsection from(
  SELECT a.section asection, b.section bsection, a.CLASS a, b.CLASS b
  FROM r_a a 
  FULL OUTER JOIN r_b b 
  ON a.section=b.section 
)
WHERE a=1 AND  b=1

Oracle SQL Full Outer Joins

2 Answers2