MySQL: How can I merge two tables without any common fields?

Other Questions MySQL
1

I have two distinct tables in my database that I need to combine, but they do not share any common columns.

The first table is named table_1:

bmw
benz
honda

The second table is called table_2:

blue
red
brown

I’m looking to create a new table, which I will refer to as table_3, that looks like this:

cars   colour
----   -----
bmw     blue
benz    red
honda   brown

Is there a method to perform this kind of join in MySQL even when there are no common fields between the two tables? I’m fairly new to SQL, so I could use some guidance.

2

SQL works but gets messy with hundreds or thousands of rows. Plus you’re manually running queries every time data changes.

I hit this exact problem last year merging product data from two systems. Instead of complex queries, I built automation in Latenode that handles the merge.

It connects to both MySQL tables, pulls data, matches rows by position (like ROW_NUMBER), and creates merged output. Runs automatically when new data arrives.

Set it up once and you’re done. No more writing SQL for every merge. Latenode handles the row counting and matching.

It validates both tables have matching row counts before merging, so you won’t get incomplete results. Much cleaner than doing it manually.

3

You can achieve this using ROW_NUMBER() to create artificial keys.

SELECT t1.cars, t2.colour
FROM (
    SELECT cars, ROW_NUMBER() OVER() as rn FROM table_1
) t1
JOIN (
    SELECT colour, ROW_NUMBER() OVER() as rn FROM table_2  
) t2 ON t1.rn = t2.rn;

This assigns row numbers to each table and joins on those numbers. So, the first row in table_1 matches the first row in table_2, and so on.

However, manual data handling can get tedious. I use Latenode for automating database operations, like merging tables and transforming data effortlessly.

Latenode connects directly to MySQL and lets you run queries on schedules. It’s way simpler than running SQL manually all the time.

4

just use a cross join with LIMIT if both tables have the same row count. Something like SELECT * FROM (SELECT cars, @rownum1:=@rownum1+1 as rn FROM table_1 CROSS JOIN (SELECT @rownum1:=0) r) a JOIN (SELECT colour, @rownum2:=@rownum2+1 as rn FROM table_2 CROSS JOIN (SELECT @rownum2:=0) s) b USING(rn). works fine on MySQL 5.7 and older.

5

You can also use variables to create sequential counters for each table. This works better than ROW_NUMBER() on older MySQL versions:

SELECT t1.cars, t2.colour
FROM (
    SELECT @row_number1:=@row_number1+1 AS row_num, cars
    FROM table_1, (SELECT @row_number1:=0) AS temp1
) t1
JOIN (
    SELECT @row_number2:=@row_number2+1 AS row_num, colour
    FROM table_2, (SELECT @row_number2:=0) AS temp2
) t2 ON t1.row_num = t2.row_num;

This join depends completely on the physical row order in your tables. Add ORDER BY clauses if you need consistent ordering. Both tables need the same row count or you’ll get incomplete results.

6

You can also use a simple CROSS JOIN with LIMIT clauses. It’s straightforward and doesn’t need window functions or variables:

SELECT 
    (SELECT cars FROM table_1 LIMIT 1 OFFSET 0) as cars,
    (SELECT colour FROM table_2 LIMIT 1 OFFSET 0) as colour
UNION ALL
SELECT 
    (SELECT cars FROM table_1 LIMIT 1 OFFSET 1) as cars,
    (SELECT colour FROM table_2 LIMIT 1 OFFSET 1) as colour
UNION ALL
SELECT 
    (SELECT cars FROM table_1 LIMIT 1 OFFSET 2) as cars,
    (SELECT colour FROM table_2 LIMIT 1 OFFSET 2) as colour;

This works great when you know exactly how many rows you’re dealing with. Big advantage is it runs on any MySQL version without worrying about variable syntax changes. Just remember both tables need the same row count and you’re relying on your data’s natural order.