没有键/索引的MYSQL连接表 [英] MYSQL Join Tables without key / index

问题描述

就像标题中所说的，我想加入2个表而没有要比较的键.

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

没有键的mysql联接表

此示例描述了初始情况，但他正在寻找笛卡尔积. 我想要这样的东西

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

结果应如下所示:

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

像这样仅通过sql就能实现吗?

Is something like this possible just with sql?

推荐答案

由于MySQL没有ROW_ID()函数，因此您必须使用递增的用户变量来伪造它:

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)

演示

请注意，这假设表1中的形容词总是比表2中的名词多.如果需要允许任何一个表都较小，则需要使用full outer join.正如Andriy M所提到的，MySQL没有对此的内置支持，但是如果您搜索SO或google，则应该找到编码的方法.

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.

这篇关于没有键/索引的MYSQL连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！