Can you join two table without any common column?
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 17 )
Using Cross Join:
scala> df1.show
+—+—-+
| A| B|
+—+—-+
| 1|puja|
| 1| kop|
| 2| lop|
| 3| jop|
+—+—-+
scala> df2.show
+—+—+
| C| D|
+—+—+
| 1| lo|
| 2| ko|
| 3| no|
| 4| lo|
+—+—+
JOIN:
scala> val res4=df1.crossJoin(df2).show
+—+—-+—+—+
| A| B| C| D|
+—+—-+—+—+
| 1|puja| 1| lo|
| 1|puja| 2| ko|
| 1|puja| 3| no|
| 1|puja| 4| lo|
| 1| kop| 1| lo|
| 1| kop| 2| ko|
| 1| kop| 3| no|
| 1| kop| 4| lo|
| 2| lop| 1| lo|
| 2| lop| 2| ko|
| 2| lop| 3| no|
| 2| lop| 4| lo|
| 3| jop| 1| lo|
| 3| jop| 2| ko|
| 3| jop| 3| no|
| 3| jop| 4| lo|
+—+—-+—+—+
Sorry it a private answer.
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
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
Will it work in case the number of rows differ in both the tables?
Using cross join
select
from
Yes we can use cross join to join two table without any common column
CROSS JOIN works without any common column between tables. It is a Cartesian Product of two tables.
Yes, we can use a cross join to join two tables that don’t have any common column. CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.
The syntax of the CROSS JOIN in SQL :
SELECT *
FROM table1
CROSS JOIN table2;
The resultset row count will equal to multiplication of tables row counts that will be joined.
NOTE: The Cartesian Product is a multiplication operation in the set theory that generates all ordered pairs of the given sets. Suppose that, A is a set and elements are {a,b} and B is a set and elements are {1,2,3}. The Cartesian Product of these two A and B is denoted AxB and the result will be like the following.
AxB ={(a,1), (a,2), (a,3), (b,1), (b,2), (b,3)}
Two tables can be combined without any common column using Cross Join.
The CROSS JOIN is used to combine each row of the first table with each row of the second table. It is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.
In general, if the first table has n rows and the second table has m rows, the cross join will result in n x m rows.
Yes we can join two table without common column name
CROSS JOIN we use for doing the same
SELECT *
FROM TABLE_1
CROSS JOIN TABLE_2;
Yes we can join two table without common column name
CROSS JOIN we use for doing the same
SELECT *
FROM TABLE_1
CROSS JOIN TABLE_2;
We can use cartesian join or cross join in order to join two tables with no common columns.
SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]
OR
SELECT * FROM [ TABLE1] , [TABLE2]
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:
SELECT * FROM table1, table2
We can use the Cartesian product, union, and cross-product to join two tables without a common column.
SELECT * FROM tableA, tableB;
SELECT column_list FROM tableA
UNION
SELECT column_list FROM tableB
SELECT tableA.Column1, tableB.Column1
FROM tableA
CROSS JOIN tableB
WHERE tableB.Column1 = ‘value’;
We can join two tables without a common column name using Cartesian product or Cross join
1. Cartesian Product
SELECT * FROM Table1, Table2
2. Cross Join
SELECT *
FROM Table1
CROSS JOIN Table2
Yes, CROSS JOIN.
CROSS JOIN is used to combine each row of a table_1 with another table. It is also known as cartesian join since it returns the cartesian product of the two tables.
Syntax:
SELECT col_1, col_2, col_3…..
FROM table_1
CROSS JOIN table_2;