Is a FULL OUTER JOIN neccessary for this situation?

I'm trying to find an alternative to using a FULL OUTER JOIN for a stored procedure I am writing. Here's the scenario.

Two tables of mostly unrelated data representing events that need to occur. The events need to be processed in chronological order and both tables have a datetime column. So I need to get a single table that acts as a list of ALL these events ordered by datetime (those two datetime columns need to be mixed together).

A small catch: sometimes events from the two tables will be related, and in this case, event from Table A needs to go first regardless of the datetime. So if A has 3 events, and B has 3 events, but there is a single related pair between A and B, I want to return 5 rows. In cases where there is unrelated data, there will be NULL data (and that's ok, I use NULL data checks to determine what to do next, i.e. process event A or B, etc.)

C开发者_如何学编程urrently what I have looks something like this:

    SELECT
    CASE
        WHEN A.EventDateTime IS NULL THEN B.EventDateTime
        ELSE A.EventDateTime 
    END AS SortDateTime,
    A.EventId,
    B.EventId,
    FROM A FULL OUTER JOIN B
    ON A.RelatedData=B.RelatedData
    ORDER BY SortDateTime

That should be fine

also instead of using CASE

 CASE
        WHEN A.EventDateTime IS NULL THEN B.EventDateTime
        ELSE A.EventDateTime 
    END AS SortDateTime,

you can do this

COALESCE(A.EventDateTime,B.EventDateTime) AS SortDateTime,

Which is a little shorter

If you really want to avoid the OUTER JOIN you might try

SELECT A.EventDateTime, A.EventID
FROM A
UNION ALL
SELECT B.EventDateTime, B.EventID
FROM B WHERE B.RelatedData NOT IN (SELECT RelatedData FROM A)

The above NOT IN in can be written as NOT EXISTS SELECT 1 FROM A WHERE A.RelatedData = B.RelatedData or as LEFT JOIN ... IS NULL if your RDBMS has preferences (or if correlated sub-query is better for your dataset).

If there is no exact relation between the two, I can't think of any other way you could perform this.