CODEWITHSUNDEEP
mysqlsqljoin
alabama
alabama

Reputation: 422

MYSQL Join Tables without key / index

like the title says, i want to join 2 Tables without a key to compare.

mysql join tables without keys

This example descripes the initial situation, but he is looking for the cartesian product. I want something like this

Table 1: t1
red
blue
big
small


Table 2: t2
cat
dog
person

The result should look like this:

red cat
blue dog
big person
small NULL <--- can be empty (not shown)

Is something like this possible just with sql?

Upvotes: 1

Views: 2321

Answers (1)

Barmar
Barmar

Reputation: 780851

Since MySQL doesn't have a ROW_ID() function, you have to fake it with a user-variable that you increment:

select adjective, noun
from (select @counter1 := @counter1+1 as counter, adjective
      from table1, (select @counter1 := 0) init) t1
left join (select @counter2 := @counter2+1 as counter, noun
           from table2, (select @counter2 := 0) init) t2
using (counter)

DEMO

Note that this assumes there are always more adjectives in table1 than nouns in table2. If you need to allow either table to be smaller, you need to use a full outer join. As Andriy M mentioned, MySQL doesn't have built-in support for this, but if you search SO or google you should find ways to code it.

Upvotes: 4

Related Questions