How to join two different tables(without common column) side by side without getting cross join in t-CodePudding

I want to join two different tables side by side without loosing any row from the tables and the result should not be cross join.

Create table query for t1:

CREATE TABLE IF NOT EXISTS t1 (
    `i` INT,
    `foo` INT);
INSERT INTO t1 VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

Output for t1 table is:

Create table query for dates table:

CREATE TABLE IF NOT EXISTS dates (
    `User` VARCHAR(2) CHARACTER SET utf8,
    `start_date` date,
    `end_date` date
);
INSERT INTO dates VALUES
    ('U1','2020-01-01','2020-01-31'),
    ('U2','2020-01-16','2020-01-26'),
    ('U3','2020-01-28','2020-02-06');

Output for dates table is:

User  start_date    end_date
U1    2020-01-01    2020-01-31
U2    2020-01-16    2020-01-26
U3    2020-01-28    2020-02-06

BUT I WANT THE OUTPUT LIKE: t1 and dates table side by side

i  foo  User   start_date   end_date
1   1    U1    2020-01-01   2020-01-31
2   2    U2    2020-01-16   2020-01-26
3   3    U3    2020-01-28   2020-02-06
4   4    null   null        null

CodePudding user response：

Relational algebra doesn't work this way, so normal joins and unions won't produce the result you want.

You can, however, manufacture a fake key on each data set to join with, using ROW_NUMBER(). For example:

with a as (
  select *, row_number() over() as rn from t1
),
b as (
  select *, row_number() over() as rn from dates
)
select a.i, a.foo, b.User, b.start_date, b.end_date
from a left join b on a.rn = b.rn

Note: The solution above assumes a has more rows than b. If this is not a given the query will need a full join, that unfortunately MySQL does not implement. It can be simulated with a left join and an anti-join, however.