Menu
the result of sql 1st:
select p1.t1, p2.t2, p2.t3 from
(select 'A' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
is
A NULL NULL
the result of sql 2nd:
select p1.t1, p2.t2, p2.t3 from
(select 'B' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
is
B NULL NULL
if I union all these two part of sql, my expected sql are:
A NULL NULL
B NULL NULL
but I get no result .
the sql is like below:
select p1.t1, p2.t2, p2.t3 from
(select 'A' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
union all
select p1.t1, p2.t2, p2.t3 from
(select 'B' as t1,'' as t2,'' as t3 from dual ) p1 left join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
I tested this sql on oracle. I want to know why the result is not like my expected. by the way, if use union instead of union all, the result is just like expected.
257
The SQL UNION operator treats all NULL values as a single NULL value when evaluating duplicate. When combining with UNION, the ALL or DISTINCT operator is optional. The ALL operator allows duplicate rows to appear in the combined result set.
UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records. UNION first performs a sorting operation and eliminates of the records that are duplicated across all columns before finally returning the combined data set.
SQL Union All Operator Overview The SQL Union All operator combines the result of two or more Select statement similar to a SQL Union operator with a difference. The only difference is that it does not remove any duplicate rows from the output of the Select statement.
When a UNION is required to put together data from multiple queries, you might be able to use a CASE statement instead. This is very useful, particularly when the data for each of the queries in the UNION come from the same table.
You haven't said which version you're encountering this on, but Gordon Linoff reproduced on SQL Fiddle which is running 11.2.0.2, and as others have said it's not seen in 10g and 11.2.0.3, so it seems reasonable to think you might be on 11.2.0.2 as well.
In which case, this looks identical to bug 12336962. If you have access to Oracle Support you (or your DBA) can look that up, but I can't reproduce what it says here, even though it's a published bug. Running your query and the example from the bug report produce the same results, and in both cases changing from union all to union produces correct results. You might want to raise a service request to get that confirmed though.
That bug is fixed in the 11.2.0.3 patch set - I'm not sure I'm really supposed to even share that, but it's already published here - so patching up might be your best bet, and Oracle might suggest that if you do raise an SR. If in doubt, ask Oracle directly.
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
