Last week I was surprised to find out that sybase 12 doesn't support full outer joins. But it occurred to me that a full outer join should be the same as a left outer join unioned with a right outer join of the same sql. Can anybody think of a reason this would not hold true?
A:
UNION-ing two OUTER JOIN statements should result in duplicate rows representing the data you'd get from an INNER JOIN. You'd have to probably do a SELECT DISTINCT on the data set produced by the UNION. Generally if you have to use a SELECT DISTINCT that means it's not a well-designed query (or so I've heard).
Josh
2010-01-18 22:54:23
+1
A:
If you union them with UNION ALL, you'll get duplicates. If you just use UNION without the ALL, it will filter duplicates and therefore be equivalent to a full join, but the query will also be a lot more expensive because it has to perform a distinct sort.
Aaronaught
2010-01-18 23:13:20
A lot more expensive than union all or a lot more expensive than a native full outer join?
stu
2010-01-20 16:17:42
@stu: Both. Even though a native full join is technically the same thing as a `UNION`, database engines that support full joins will have a way to optimize it. Having said that - full joins are still pretty slow and worth avoiding if you can. I don't think I ever use them.
Aaronaught
2010-01-20 16:29:46