Here is a weird one.

Suppose that I have 2 tables, table1 and table2. table1 has 2 fields of interest, call them id and field1. table2 has 2 fields of interest, call them id and field2. Assume that id is a unique identifier in tables 1 and 2.

We want a query whose output is id, field1, field2 with all records in either table showing up, populated with data from either or both tables depending on what is available.

Here is some untested SQL showing the trick:

\nSELECT id, MAX(field1) as field1, MAX(field2) as field2\nFROM\n  (\n    SELECT id, field1, NULL as field2\n    FROM table1\n  UNION\n    SELECT id, NULL as field1, field2\n    FROM table2\n  )\nGROUP BY id\n

It looks ugly, but in fact the performance should not be too dissimilar to what a real join would be. If you wanted, you could even implement inner, left and right joins through an extension of this trick. (Though that would definitely be slower than the native equivalent.)

Cheers,
Ben