I was struggling with a custom SQL query I had to write. The "first pass" at this functionality, just using my Rails models and predefined associations came at a cost of M x N queries. I figured I could get that down to 2 queries, which is better, right? Of course, it took me the better part of two days (many interruptions, not conducive to concentration) to get it right.

After a lot of trial and error, I knew that one part of my query involved a full outer join on two of the three tables. This was only required for 2 subsets of data, so it wasn't as huge a performance issue as you might think. The problem was that the two subsets of data I had to join had no common columns.

Sadly, it appears that no one out there who has solved this problem has blogged or written an article about it. Or, if they have, it's hidden in sites like "Experts Exchange" where you have to sign up and/or pay to see the results. So, here is my answer, to this part of the problem that I solved.

Assuming you have table (or subquery) w and another table (or subquery) s, you can do something like this: with w1 as ( select w.*, 1 as match from w ), s1 as ( select s.*, 1 as match from s ) select .... from w1 full outer join s1 on ( w1.match = s1.match )

Note that the ... part is where you have to select all the columns by name, because w1.*, s1.* would result in an ambiguous (and unnecessary) column "match".

I was using Oracle. I know that SQLServer also supports the "with" clause. If you know how to restructure this to work in MySQL, or any other common database, please do reply in the comments for the edification of all. And, of course, if there is some other magical way to accomplish this type of full outer join without my hack, please comment as well.