MYSQL: Two Table SELECT without common values

I开发者_如何学C need a query to make a Select over table1 and table2. I want them to appear as a common result, but without any connection there is no value from table 1 that corresponds with any value from table 2. So if a result was from table1 it should contain the values table1.a , table1.b, table1.c if it comes from table2 it should have table2.d, table2.e, table2.f with the a, b, c-values empty.

SELECT * FROM table1, table2 

...doesn't help me because it always makes connections, connects result of table1 and table2 in one result. I need to have them separate, each result its own row.

Is it simple, difficult or impossible?

You need to use the same alias for the equivalent columns from both tables, like in :

( select a as FIRST_COL
  from table 1 
) 
union 
( select d as FIRST_COL
  from table2 
)

As long as the column types are the same, you can use a union. For example:

select a, b, c
from table1
union
select d, e, f
from table2

If they are not the same, you can still do it by creating 'dummy' columns. As long as the type and number of columns is the same, the results will appear as one set of rows.

If I understand your wish select a,b,c,'','','' from table1 union '','','',e,f,g from table2

replace '' with your favorite place holder for empty column a,b,c - column names.

I was trying to do something similar and was not really satisfied with any answers I found so I came up with the following implemntation.

/* Create Sample Data
*/
declare @a table(a varchar(max))
declare @b table(b varchar(max))
insert @a (a)
values('a1'),('a2'),('a3'),('a4');

insert @b (b)
values('b1'),('b2'),('b3');

/* Show Cartesian Intersection of both tables
*/
select * from @a, @b

/* Create matching table variables with ID column
*/
declare @aIdx table(a varchar(max), Id int identity(1,1))
declare @bIdx table(b varchar(max), Id int identity(1,1))

insert into @aIdx (a)
select a
from @a

insert into @bIdx (b)
select b
from @b

/* Join Record by Record
*/
select
    l.Id,
    l.a,
    r.b 
from @aIdx l full outer join @bIdx r on l.Id = r.Id

Cartesian of the two tables is:

a1  b1
a2  b1
a3  b1
a4  b1
a1  b2
a2  b2
a3  b2
a4  b2
a1  b3
a2  b3
a3  b3
a4  b3

Join Record by Record:

1   a1  b1
2   a2  b2
3   a3  b3
4   a4  NULL

Try this:

SELECT * FROM table1 UNION SELECT * FROM table2