How to Combine Two Tables Without a Common Column

While writing queries in SQL during the learning phase, we mostly join the table in which there are common columns or some kind of relationship between them. But, real-life scenarios are more complex and there are times when we are required to join the tables without any common column between them. In this tutorial, we will learn multiple ways to solve this problem.

Exploring the Data

We are building an e-commerce-based startup for marketing and for that we have two tables named Youtuber and Influencers.

The Youtuber table contains ID, name, subscribers and money.

The Influencers table contains ID, name, followers and collaboration money.

Now, let’s combine these tables.

Method 1: Using CROSS JOIN

Cross Join is used to form the cartesian product of the tables which may or may not have any common column between them. This join cannot be claimed as good as other joins because it does not check for any column, it just does the cross-product of the two specified tables which may result in repetitions of data values. But as we want to know the methods, let’s discuss this.

Syntax

SELECT column01, colum02
FROM table01
CROSS JOIN table02;Code language: SQL (Structured Query Language) (sql)

Example

Let’s say we want to fetch the record of all the possible combinations of people from the table Youtuber and Influencers.

SELECT y.name AS youtuber_name, i.name AS influencer_name
FROM Youtuber y
CROSS JOIN Influencers i;Code language: SQL (Structured Query Language) (sql)

Also Read: Types of JOINs in MySQL

Method 2: Using UNION

In UNION, the main goal is to combine the table using a row-by-row approach. It combines the number of unique rows from both tables and labels the table according to the first table specified in the method.

Syntax

SELECT * 
FROM table01
UNION
SELECT *
FROM table02;Code language: SQL (Structured Query Language) (sql)

Example

Let’s say we want to fetch the record of the total amount of money both the Youtuber and Influencers earn.

SELECT y.money
FROM Youtuber y
UNION
SELECT i.collaboration_money
FROM Influencers i
ORDER BY money;Code language: SQL (Structured Query Language) (sql)

Method 3: UNION ALL

The difference between the previous method and this one is that, UNION ALL allows duplicate values but UNION doesn’t.

Syntax

SELECT * 
FROM table01
UNION ALL
SELECT *
FROM table02;Code language: SQL (Structured Query Language) (sql)

Example

In the previous example, you must have noticed that the result set skipped “600”, which was written twice. Now let’s see what will happen while using UNION ALL.

SELECT y.money
FROM Youtuber y
UNION ALL
SELECT i.collaboration_money
FROM Influencers i
ORDER BY money;Code language: SQL (Structured Query Language) (sql)

As we can see above, we have got the data value “600” twice.

Method 4: Using FROM

In this method, we simply put the list of tables that we want to combine in the FROM clause and then add a WHERE clause to add conditions of joining.

Syntax

SELECT *
FROM table01 t1, table02 t2Code language: SQL (Structured Query Language) (sql)

Example

Let’s say we want to fetch the record of all the possible combinations of people from the table Youtuber and Influencers.

SELECT y.name AS name, i.name AS pair_with
FROM Youtuber y, Influencers i;Code language: SQL (Structured Query Language) (sql)

Conclusion

Joining tables without a common column can turn out as a challenging task and it also demands creative approaches such as the above methods we have discussed in this tutorial: CROSS JOIN, UNION, UNION ALL and FROM. These methods help developers to integrate and manipulate the database accordingly.

Reference

https://stackoverflow.com/questions/75894565/join-two-tables-without-common-column