Joining 2 Queries without Common Column Fields in SQL?

My issue is in reference to a similar post: Joining tables without a common column in sql server

I have 2 queries, each query references different tables, hence the queries do not have common column fields.

I want to join the 2 queries together. Let’s take for example the 2 queries has the following data:

Query1

Test1           Test2
-----------     -------------
A               1
B               2
C               3

Query2

Test3           Test4
-----------     -------------
D               4
E               5
F               6

Expected Query3:

Test1           Test2             Test3            Test4
-----------     -------------     -------------    -------------
A               1                                     
B               2                                     
C               3                  
                                  D                4
                                  E                5
                                  F                6

The provided answer to the similar post is referenced here: Using Full Outer Join

I’ve tried the recommended solution like so:

SELECT Query1.Test1, Query1.Test2, Query2.[Test3], Query2.[Test4]
FROM Query1
FULL OUTER JOIN Query2 ON 0 = 1;

However, I get an error in Microsoft Access stating:

Syntax error in FROM clause.

Can someone give me some guidance on what I did wrong and what the solution would be?

Answer

You are trying Full Outer Join but it does not exist on MySQL. We can emulate it by doing a UNION of a left join and a right join

Try:

create table query1(
  test1 varchar(5), 
  test2 int(3)  );

insert into query1 values ( 'A', 1),( 'B', 2),( 'C', 3);

create table query2(
  test3 varchar(5), 
  test4 int(3)  );

insert into query2 values ( 'D', 4),( 'E', 5),( 'F', 6);



SELECT *
FROM `query1` as `t1`
LEFT OUTER JOIN `query2` as `t2` ON `t1`.`test1` = `t2`.`test3`   
UNION  
SELECT *
FROM `query1` as `t1`
RIGHT OUTER JOIN `query2` as `t2` ON `t1`.`test1` = `t2`.`test3` ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/69