我有两个表:
表1id code
1 a
2 b
3
表2 id code
4 a
5 b
6
表3
id id1
1 4
2 5
我基本上想插入表3与表1和表2的id,其中基于table1.name = table2.name
。
INSERT INTO table3(id,id1)
SELECT t1.id,t2.id FROM table1 t1, table2 t2 where t1.name=t2.name;
然而,表1和表2的第3行有空代码,我将如何比较null = null
不工作。
只需添加一个(t1.name IS NULL AND t2.name IS NULL)
测试:
INSERT INTO table3 (id, id1)
SELECT t1.id, t2.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.name = t2.name OR (t1.name IS NULL AND t2.name IS NULL);
正如我几天前了解到的,MySQL支持语法增强<=>
来比较null为相等:
INSERT INTO table3(id,id1)
SELECT t1.id,t2.id
FROM table1 t1 JOIN table2 t2
ON t1.name <=> t2.name;
编辑:MySQL参考在oracle中,使用nvl()将空值转换为较少使用的字符。
INSERT INTO table3(id,id1)
SELECT t1.id,t2.id
FROM table1 t1, table2 t2
WHERE nvl(t1.code,'$$')=nvl(t2.code,'$$');
use inner join
INSERT INTO table3(id,id1) SELECT t1.id, t2.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.name = t2.name OR (t1.name IS NULL AND t2.name IS NULL);