Home How to join two different tables(without common column) side by side without getting cross join in the output in Mysql?

Questions

How to join two different tables(without common column) side by side without getting cross join in the output in Mysql?

byMR

November 10, 2021

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

>Solution :

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 t1
)
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.