How to combine two MySQL tables with single columns into one result set

Other Questions MySQL
1

I need help with a MySQL query that combines data from two separate tables. I have two tables that each contain just one column.

The first table (TableA) has a column called name with these values:

name
dog
cat
bird

The second table (TableB) also has a column called name with these values:

name
red
blue
green

What I want to achieve is a result that pairs up the rows from both tables like this:

name1----name2
dog------red
cat------blue
bird-----green

Basically I want to match the first row from TableA with the first row from TableB, second with second, and so on. How can I write a MySQL query to get this kind of output?

2

if both tables have the same row count, the easiest way is using LIMIT with OFFSET in a union approach. try SELECT (SELECT name FROM TableA LIMIT 1 OFFSET 0) as name1, (SELECT name FROM TableB LIMIT 1 OFFSET 0) as name2. you’ll have to repeat this for each row, which gets annoying quickly. but it works fine for a few rows when you don’t want anything complicated.

3

Been doing this in production for years. ROW_NUMBER works great, but here’s another trick with MySQL’s AUTO_INCREMENT if you’re cool with temp tables. Just create temp tables with auto-incrementing IDs, dump your data in, then join on those IDs. Like CREATE TEMPORARY TABLE temp_a (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)) and same for temp_b. INSERT your source data into each, then SELECT temp_a.name, temp_b.name FROM temp_a JOIN temp_b ON temp_a.id = temp_b.id. You get better control over ordering and it’s rock solid across MySQL versions. Don’t forget to drop your temp tables unless your session dies automatically.

4

You’ll need row numbers for both tables since MySQL doesn’t guarantee order without ORDER BY. Use ROW_NUMBER() window functions:

SELECT a.name AS name1, b.name AS name2
FROM (
    SELECT name, ROW_NUMBER() OVER () AS rn FROM TableA
) a
JOIN (
    SELECT name, ROW_NUMBER() OVER () AS rn FROM TableB
) b ON a.rn = b.rn;

This numbers each row sequentially, then joins on those numbers. Warning: without ORDER BY in the subqueries, row pairing might change between runs depending on how MySQL fetches data. If your tables have a natural ordering column, throw that into ORDER BY within the ROW_NUMBER() functions.

5

Here’s another approach that gets overlooked: using SET @rownum := 0 with ORDER BY. The main difference from the variable method is you get proper control over resetting the counter:

SELECT 
    (@rownum := @rownum + 1) as position,
    a.name as name1,
    b.name as name2
FROM 
    (SELECT name FROM TableA ORDER BY name) a,
    (SELECT name FROM TableB ORDER BY name) b,
    (SELECT @rownum := 0) r
WHERE 
    @rownum <= (SELECT COUNT(*) FROM TableA)
    AND @rownum <= (SELECT COUNT(*) FROM TableB)

This lets you control exactly which rows get paired based on your sorting. I’ve used this tons when migrating legacy data where row insertion order was unreliable. The ORDER BY clauses guarantee consistent pairing every run, instead of relying on natural table order that shifts after maintenance.

6

if ur using an older MySQL version and don’t have row_number(), u can use variables. here’s what works for me: SELECT (@row1:=@row1+1) as row_num, a.name, b.name FROM TableA a, TableB b, (SELECT @row1:=0) r WHERE (@row2:=@row2+1)=@row1. kinda messy but it gets it done on legacy systems.

7

Manual SQL joins work but you’ll hit walls fast with complex data or regular syncing needs.

I’ve been there - multiple single column tables needing constant pairing. ROW_NUMBER works but becomes a nightmare with frequent operations or growing tables.

Automation changed everything for me. Instead of writing complex SQL each time, I built workflows that handle table combining automatically. Schedule it when new data arrives, add validation, handle unequal row counts, push results anywhere you want.

Write the logic once and forget about it. No more identical ROW_NUMBER queries or manual temp table management. You get proper error handling and logging too.

For your case, build a workflow that reads both MySQL tables, pairs rows by position, writes the result where you need it. 10 minutes to setup, then it just runs.