I need to compare the records from two tables: X and Y. Each record has two ids: ID1 and ID2. Either ID1 or ID2 can be null in either table, but both can’t be null at once. I need to produce a view with all the information from both tables:

* Rows where X.ID1 = Y.ID1 and X.ID2 = Y.ID2
* Rows where X.ID1 = Y.ID1 but X.ID2 <> Y.ID2
* Rows where X.ID1 <> Y.ID1 but X.ID2 = Y.ID2
* Rows where X.ID1 and Y.ID1 don’t have any matches at all
* Rows where X.ID2 and Y.ID2 don’t have any matches at all

Example:

```
X:                   Y:
|---------------|    |---------------|
|  ID1  |  ID2  |    |  ID1  |  ID2  |
|---------------|    |---------------|
|   1   |   A   |    |   1   |   A   |
|   2   |   B   |    |   2   |   C   |
|   3   | NULL  |    | NULL  |   B   |
| NULL  |   D   |    |   5   | NULL  |
|---------------|    |---------------|

Output:
|---------------------------------------|
|  XID1 |  YID1 |  XID2 |  YID2 |  SRC  |
|---------------------------------------|
|   1   |   1   |   A   |   A   |  X+Y  |
|   2   |   2   |   B   |   C   |  X+Y  |
|   3   | NULL  | NULL  | NULL  |   X   |
| NULL  |   5   | NULL  | NULL  |   Y   |
|   2   | NULL  |   B   |   B   |  X+Y  |
| NULL  |   2   | NULL  |   C   |   Y   |
| NULL  | NULL  |   D   | NULL  |   X   |
|---------------------------------------|

```

My first obvious solution was to do a `FULL OUTER JOIN`:

```sql
SELECT … FROM X FULL OUTER JOIN Y ON X.ID1 = Y.ID1 OR X.ID2 = Y.ID2

```

This works, but a conditional within a join has terrible performance, and this view would take up to a minute to run. Removing the conditional takes the execution time down to less than a second, but then I lose matching by one of the IDs.

How can I elegantly achieve the above without using a conditional join? I’ve tried:

* Joining by concatenation of the two IDs, but this only matches when both IDs match
* Doing a `CROSS JOIN` and filtering by `X.ID1=Y.ID1 OR X.ID2=Y.ID2`, but this loses the cases without any matches. This is the most promising approach.
* Doing a `UNION ALL` of X and Y and then grouping by ID1 and ID2, but this once again only matches when both IDs match