Combine two tables that have no common fields

Question

I want to learn how to combine two db tables which have no fields in common. I've checked UNION but MSDN says :

The following are basic rules for combining the result sets of two queries by using UNION:

1. The number and the order of the columns must be the same in all queries.
2. The data types must be compatible.

But I have no fields in common at all. All I want is to combine them in one table like a view.

So what should I do ?

Thanks in advance.

Sincerely.

Answer 1

If the tables have no common fields then there is no way to combine the data in any meaningful view. You would more likely end up with a view that contains duplicated data from both tables.

Answer 2

SELECT *
FROM table1, table2

This will join every row in table1 with table2 (the Cartesian product) returning all columns.

Answer 3

To get a meaningful/useful view of the two tables, you normally need to determine an identifying field from each table that can then be used in the ON clause in a JOIN.

THen in your view:

SELECT T1.*, T2.* FROM T1 JOIN T2 ON T1.IDFIELD1 = T2.IDFIELD2

You mention no fields are "common", but although the identifying fields may not have the same name or even be the same data type, you could use the convert / cast functions to join them in some way.

Answer 4

This is a very strange request, and almost certainly something you'd never want to do in a real-world application, but from a purely academic standpoint it's an interesting challenge. With SQL Server 2005 you could use common table expressions and the row_number() functions and join on that:

with OrderedFoos as (
    select row_number() over (order by FooName) RowNum, *
    from Foos (nolock)
),
OrderedBars as (
    select row_number() over (order by BarName) RowNum, *
    from Bars (nolock)
)
select * 
from OrderedFoos f
    full outer join OrderedBars u on u.RowNum = f.RowNum

This works, but it's supremely silly and I offer it only as a "community wiki" answer because I really wouldn't recommend it.

Answer 5

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, '' as cid, '' as name from parts
  union
  select '' as pid, '' 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.