[sql-server] Combine two tables that have no common fields

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?

Joining Non-Related Tables

Demo SQL Script

IF OBJECT_ID('Tempdb..#T1') IS NOT NULL  DROP TABLE #T1;

CREATE TABLE #T1 (T1_Name VARCHAR(75));

INSERT INTO #T1 (T1_Name) VALUES ('Animal'),('Bat'),('Cat'),('Duet');

SELECT * FROM #T1;

IF OBJECT_ID('Tempdb..#T2') IS NOT NULL  DROP TABLE #T2;

CREATE TABLE #T2 (T2_Class VARCHAR(10));

INSERT INTO #T2 (T2_Class) VALUES ('Z'),('T'),('H'); 

SELECT * FROM #T2;

To Join Non-Related Tables , we are going to introduce one common joining column of Serial Numbers like below.

SQL Script

SELECT T1.T1_Name,ISNULL(T2.T2_Class,'') AS T2_Class FROM
( SELECT T1_Name,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T1) T1
LEFT JOIN
( SELECT T2_Class,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T2) T2
ON t1.S_NO=T2.S_NO;

SELECT *
FROM table1, table2

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

Select 
DISTINCT  t1.col,t2col
From table1 t1, table2 t2

OR

Select 
DISTINCT  t1.col,t2col
From table1 t1 
cross JOIN  table2 t2

if its hug data , its take long time ..

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.

select 
    status_id, 
    status, 
    null as path, 
    null as Description 
from 
    zmw_t_status

union
select 
    null, 
    null, 
    path as cid, 
    Description from zmw_t_path;

why don't you use simple approach

    SELECT distinct *
    FROM 
    SUPPLIER full join 
    CUSTOMER on (
        CUSTOMER.OID = SUPPLIER.OID
    )

It gives you all columns from both tables and returns all records from customer and supplier if Customer has 3 records and supplier has 2 then supplier'll show NULL in all columns

try:

select * from table 1 left join table2 as t on 1 = 1;

This will bring all the columns from both the table.

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.

SELECT t1.col table1col, t2.col table2col
FROM table1 t1 
JOIN table2 t2 on t1.table1Id = x and t2.table2Id = y

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.

Very hard when you have to do this with three select statments

I tried all proposed techniques up there but it's in-vain

Please see below script. please advice if you have alternative solution

   select distinct x.best_Achiver_ever,y.Today_best_Achiver ,z.Most_Violator from 

    (SELECT  Top(4) ROW_NUMBER() over (order by tl.username)  AS conj, tl. 
     [username] + '-->' + str(count(*)) as best_Achiver_ever 
    FROM[TiketFollowup].[dbo].N_FCR_Tikect_Log_Archive tl
     group by tl.username
     order by count(*) desc) x
      left outer join 

(SELECT 
Top(4) ROW_NUMBER() over (order by tl.username)  as conj, tl.[username] + '-->' + str(count(*)) as Today_best_Achiver
FROM[TiketFollowup].[dbo].[N_FCR_Tikect_Log] tl
where convert(date, tl.stamp, 121) = convert(date,GETDATE(),121)
group by tl.username
order by count(*) desc) y 
on x.conj=y.conj

 left outer join 

(
select  ROW_NUMBER() over (order by count(*)) as conj,username+ '--> ' + str( count(dbo.IsViolated(stamp))) as Most_Violator from N_FCR_Ticket
where dbo.IsViolated(stamp) = 'violated' and  convert(date,stamp, 121) < convert(date,GETDATE(),121)
group by username
order by count(*) desc) z
on x.conj = z.conj

Please try this query:

Combine two tables that have no common columns:

SELECT * 
FROM table1 

UNION 

SELECT * 
FROM table2  
ORDER BY orderby ASC

select * from this_table;

select distinct person from this_table

union select address as location from that_table 

drop wrong_table from this_database;