0

Mysql doesn't have FULL OUTER joins, and I know there are ways to emulate them using right and left joins. However, all of the suggested methods I have encountered require referencing the tables you are joining twice. If I am using a large subquery, there is no way to reference that query twice because mysql does not support CTE. Is there any other way I can structure this query that doesn't involve copying and pasting my large subquery?

Community
  • 1
  • 1
Jeremy Salwen
  • 8,061
  • 5
  • 50
  • 73
  • opposed to creating a temp table, load the subquery to that table, index if necessary, then right/left join? – Twelfth May 14 '15 at 23:00
  • my first reflex was "use a common table expression", but MySql [does not support them](http://stackoverflow.com/questions/324935/mysql-with-clause) neither. So probably you should switch to a temporary table. – davidhigh May 14 '15 at 23:24
  • @Twelfth If you are suggesting true `TEMPORARY` tables, MySQL has limitations on their use that almost always end up necessitating creating two copies for purposes such as this. However, "fake" temporary tables (i.e. tables you DROP when you're done) work rather well, and can give the advantageous option of being able to add appropriate indexes to the intermediate data before use. – Uueerdo May 14 '15 at 23:44
  • 1
    your question about *doing a join with only a single access to both tables* is a serious theoretical problem imo. I've been scratching my head for an hour now and found nothing which doesn't rely heavily on analytic functions used together with a cartesian product. That is, something which MySql supports even less. – davidhigh May 15 '15 at 00:21
  • I should note that in my particular case, only one of the tables is a subquery. I was trying to cook something up where I do two successive joins with the other table, but I couldn't get it to work out. – Jeremy Salwen May 15 '15 at 00:25

1 Answers1

0

So suppose I want to emulate

SELECT * FROM A OUTER JOIN B USING(x)

With the tables

A                      B
x     y                x     z
---------              --------
1     2                1     Q
3     3                2     P
6     11               3     R

So to start off, we do a LEFT JOIN Which creates the table

A LEFT JOIN B
x   y   z
-------------
1   2   Q
3   3   R
6   11  NULL

Now we append a null entry to the table like so:

(A LEFT JOIN B) UNION (SELECT NULL,NULL,NULL)
x    y    z
-------------
1    2    Q
3    3    R
6    11   NULL
NULL NULL NULL

We then append a null column to B using the same technique

B UNION (SELECT NULL, NULL)
x     z
---------------------------
1     Q
2     P
3     R
NULL  NULL

if we RIGHT JOIN these two tables together, we will get the following

((A LEFT JOIN B USING(x)) UNION (SELECT NULL,NULL,NULL)) RIGHT JOIN (B UNION SELECT NULL,NULL) USING (x)
x    y    z1    z2
-------------------
1    2    Q     Q
NULL NULL NULL  P
3    3    R     R
6    11   NULL  NULL
NULL NULL NULL  NULL

Which if we ignore the column z1 and the extra "NULL" row, is the same as the OUTER JOIN of A and B. To remove the extra row, you need an extra WHERE statement. Also to note is that this is based on NULL not showing up in the columns you are merging on.

I'm still working out the detail of the SQL syntax, because mysql complains about naming subqueries, but the sequence of operations is theoretically correct, and only accesses table A once.

Jeremy Salwen
  • 8,061
  • 5
  • 50
  • 73