Joining 2 Queries without Common Column Fields in SQL?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

If you want that exact final column structure, you could use union but first you should edit your query adding 2 empty values for 'Test3' and 'Test4' in the first query and 'Test1' and 'Test2' in the second.

First query

SELECT Test1, Test2, '' as Test3, '' as Test4 FROM someTable

Second query

SELECT '' as Test1, '' as Test2, Test3, Test4 FROM someTable

Then you can do the UNION

Query1 

UNION

Query2