Help me out with this MySql full outer join (or union)

Question

This is coming from converting MSSQL to MySql. The following is code I'm trying to get to work:

CREATE TEMPORARY TABLE PageIndex (
  IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY,
  ItemId VARCHAR(64)
);

INSERT INTO PageIndex (ItemId)
SELECT Paths.PathId
  FROM Paths,
       ((SELECT Paths.PathId
           FROM AllUsers, Paths
          WHERE Paths.ApplicationId = @ApplicationId
            AND AllUsers.PathId = Paths.PathId
            AND (@Path IS NULL
                OR Paths.LoweredPath LIKE LOWER(@Path))) AS SharedDataPerPath
          UNION -- This used to be a FULL OUTER JOIN but MySQL doesnt support that.
        (SELECT DISTINCT Paths.PathId
           FROM PerUser, Paths
          WHERE Paths.ApplicationId = @ApplicationId
            AND PerUser.PathId = Paths.PathId
            AND (@Path IS NULL
                OR Paths.LoweredPath LIKE LOWER(@Path))) AS UserDataPerPath
             ON SharedDataPerPath.PathId = UserDataPerPath.PathId)
          WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
          ORDER BY Paths.Path ASC;

Assume any variables exist already. Where this is breaking is on the 'As SharedDataPerPath' part, so I'm guessing that I aliasing a select statement so that you can access it like a table isn't supported in MySQL? If table schema would help, reply with a comment and I will add that to the question.

Thanks in advance!

Answer 1

A FULL OUTER JOIN can often be simulated with the UNION of both LEFT JOIN and RIGHT JOIN. i.e. it is all on the left and those on the right, matching where possible on the join criteria. It is usually extremely rarely used, in my experience. I have a large system where it is only used once.

What you seem to be wanting to do here because FULL OUTER JOIN is not available is to UNION two sets and set some JOIN criteria between the two subsets, which really isn't possible. The two sets that are UNIONed in your example cannot have aliases, nor can they have a WHERE clause which attempts to link them.