Click here to Skip to main content
15,066,311 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How Can i join two table without any common field?
Posted
Updated 13-Dec-17 22:16pm
Comments
NeverJustHere 28-Apr-14 12:50pm
   
Yes. You can do a cross join. It will join all rows of table A with all rows of table B.

This is really unorthodox and hopefully it's not anything in production. But, there a couple of ways to do this - one mentioned by NeverJustHere - most accepted way of achieving your scenario.

Here are some ways I know (there are possibly more) would be:
1. Cartesian Product - basically matching every row in the first table with every in the second table. I don't know if this is what you need, since if you have 100 rows in the first table and 100 rows in the second table, it will give you 10,000 rows! To achieve this:
SQL
SELECT * FROM table1, table2

2. A union of the two tables.
SQL
SELECT column1, column2, etc FROM table1
UNION
SELECT column1, column2, etc FROM table2

You'll need to ensure that the column datatypes match up here.
3. Cross Join (as already mentioned)
SQL
SELECT table1.Column1, table2.Column1 FROM table1 
CROSS JOIN table2 WHERE table.Column1 = 'Some value'

4. I would even say you could use an inner join as well with a condition that's true.
SQL
SELECT table1.Column1, table2.Column2 FROM table1 
INNER JOIN table2 ON 1 = 1 


Best bet would be to go with a cross join as already mentioned by another user.

Hope that helps!
SS
   
Comments
Aman singh Parihar 17-May-19 12:46pm
   
This worked for me, as I have no relation between the two of the tables, number of columns and the type are not even same and I want the data in one result set.

SELECT table1.Column1, table2.Column2 FROM table1
INNER JOIN table2 ON 1 = 1
;WITH T1 AS (select EmpName,Salary,ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS ID FROM table1),
     T2 AS (select Address1,StreetName,ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS ID FROM table1)

SELECT T1.EMPNAME,
       T2.StreetName,
       T2.Address1 
 FROM T1 FULL JOIN T2 ON(T1.ID=T2.ID)
   
v2
Comments
CHill60 14-Dec-17 11:11am
   
I'm guessing you answered this because of Solution 2 bringing this back into the active posts list after 3 years, but you're actually selecting stuff from a single table via two CTEs (not two tables) so not really on the mark anyway
Santosh kumar Pithani 15-Dec-17 1:48am
   
Two table returning from CTE(derived tables also possible) and joining data using row_num(id) as relation to avoid M*N rows(duplicate rows) ,i trust this query make sense
CHill60 15-Dec-17 5:10am
   
Point - missed. You are querying on the same table so your ORDER BY works. If the tables are unrelated then ORDER BY will not necessarily work, you can get rubbish. You are not answering the question that was asked, and it was fully answered 3 years ago!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900