SQL Merge Tables Side-By-Side with Nothing in Common

sql merge tables side-by-side with nothing in common

You can do this by creating a key, which is the row number, and joining on it.

Most dialects of SQL support the row_number() function. Here is an approach using it:

select gu.id, gu.name, gi.id, gi.name
from (select g.*, row_number() over (order by id) as seqnum
      from guys g
     ) gu full outer join
     (select g.*, row_number() over (order by id) as seqnum
      from girls g
     ) gi
     on gu.seqnum = gi.seqnum;

Join two tables side by side

Use a CTE where you rank all the rows of both tables by id and order of the dates and then aggregate:

WITH cte AS (
  SELECT id, date dateA, null dateB, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) rn 
  FROM TableA 
  UNION ALL
  SELECT id, null, date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) rn 
  FROM TableB
)
SELECT id, MAX(dateA) dateA, MAX(dateB) dateB
FROM cte 
GROUP BY id, rn
ORDER BY id, rn;

See the demo.

Note that your dates as they are in the format dd/mm/yyyy, they are not comparable.

You should change them to yyyy-mm-dd for the code to work properly.

Combine two tables that have no common fields

There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.

Let's say you have the two tables:

parts:              custs:
+----+----------+   +-----+------+
| id | desc     |   |  id | name |
+----+----------+   +-----+------+
|  1 | Sprocket |   | 100 | Bob  |
|  2 | Flange   |   | 101 | Paul |
+----+----------+   +-----+------+

Forget the actual columns since you'd most likely have a customer/order/part relationship in this case; I've just used those columns to illustrate the ways to do it.

A cartesian product will match every row in the first table with every row in the second:

> select * from parts, custs;
      id desc     id  name
      -- ----     --- ----
      1  Sprocket 101 Bob
      1  Sprocket 102 Paul
      2  Flange   101 Bob
      2  Flange   102 Paul

That's probably not what you want since 1000 parts and 100 customers would result in 100,000 rows with lots of duplicated information.

Alternatively, you can use a union to just output the data, though not side-by-side (you'll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select):

> select id as pid, desc, null as cid, null as name from parts
  union
  select null as pid, null as desc, id as cid, name from custs;
    pid desc     cid name
    --- ----     --- ----
                 101 Bob 
                 102 Paul
    1   Sprocket
    2   Flange

In some databases, you can use a rowid/rownum column or pseudo-column to match records side-by-side, such as:

id desc     id  name
-- ----     --- ----
1  Sprocket 101 Bob
2  Flange   101 Bob

The code would be something like:

select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;

It's still like a cartesian product but the where clause limits how the rows are combined to form the results (so not a cartesian product at all, really).

I haven't tested that SQL for this since it's one of the limitations of my DBMS of choice, and rightly so, I don't believe it's ever needed in a properly thought-out schema. Since SQL doesn't guarantee the order in which it produces data, the matching can change every time you do the query unless you have a specific relationship or order by clause.

I think the ideal thing to do would be to add a column to both tables specifying what the relationship is. If there's no real relationship, then you probably have no business in trying to put them side-by-side with SQL.

If you just want them displayed side-by-side in a report or on a web page (two examples), the right tool to do that is whatever generates your report or web page, coupled with two independent SQL queries to get the two unrelated tables. For example, a two-column grid in BIRT (or Crystal or Jasper) each with a separate data table, or a HTML two column table (or CSS) each with a separate data table.

sql to combine two unrelated tables into one

Get a row number for each row in each table, then do a full join using those row numbers:

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.

How join two tables using SQL without a common column

You can use ROW_NUMBER window function to create a calculated field that can be used to join the two tables together:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM (
   SELECT Column1, Column2,
          ROW_NUMBER() OVER (ORDER BY Column1) AS rn
   FROM Table1) AS t1
FULL OUTER JOIN  (
   SELECT Column3, Column4,
          ROW_NUMBER() OVER (ORDER BY Column3) AS rn
   FROM Table2) AS t2
ON t1.rn = t2.rn

A FULL OUTER JOIN is necessary in case either Table1 or Table2 has more rows.

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

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.

Oracle SQL merge tables side by side

Perhaps you're after something like the following?:

with t1 as (select 'e1' name, 1 columna from dual),
     t2 as (select 'h1' name, 2 columnb, 2.5 columnc from dual),  
     t3 as (select 't1' name, 3 columnd from dual union all
            select 'h1' name, 4 columnd from dual)
select coalesce(t1.name, t2.name, t3.name) name,
       t1.columna,
       t2.columnb,
       t2.columnc,
       t3.columnd
from   t1
       full outer join t2 on t1.name = t2.name
       full outer join t3 on t1.name = t3.name or t2.name = t3.name
order by name;

NAME    COLUMNA    COLUMNB    COLUMNC    COLUMND
---- ---------- ---------- ---------- ----------
e1            1                       
h1                       2        2.5          4
t1                                             3

This uses full outer joins to join the tables together (note the use of the "OR" when joining the third table), and assumes the name column is unique in each table.

Join two tables with common column names but no related data

I think what you are looking for is UNION query like below

select userid, username, incomeid, incomeamount, null as ExpenseID, null as expenseAmount
from table1
union
select userid, username, null as incomeid, null as incomeamount, null as ExpenseID, null as expenseAmount
from table2

---

Related Topics