select table1.*
from table1 left join table2
on table1.id = table2.id
where table2.id =1;
from table1 left join table2
on table1.id = table2.id
where table2.id =1;
解决方案 »
- java程序如何连接使用PL/Proxy方式集群的数据库
- mY sql服务器版本的问题
- mysql连接不上了
- sql语句中单引号带来的差异,为什么?
- 今日下载,昨日下载,本周下载怎么实现
- MYsql的存储过程中怎么循环取变量值?急救!!!!大侠们!
- 这个错误怎么解决:Query execution was interrupted
- SELECT u.*,i.* 这是什么意思
- MYSQL在Linux下启动的问题!
- mysql中的enum和set类型
- 在局域网内的mysql server,我明明已经给root用户(其他用户也一样)增加了密码了的,
- Table 'mysql.proc' doesn't exist 这是什么错?
from table1 left join table2
on table1.id = table2.id
where table2.id is null
to xayzmb(行者):
实现IN (SELECT table1ID FROM table2 WHERE ID=1)才是这样,但现实
Not IN (SELECT table1ID FROM table2 WHERE ID=1)应该是错误吧!
to tomboy0(小波波):
当table2.id对应table1.id的关系是一对一时朋友的语句是正确的,但当
当table2.id不是主键或唯一索引,对应table1.id的关系是一对多时,就不行了。
即table2.id为1时,对应table1ID为1,2,3,4,
table1表记录为:
id
1
2
3
4
即table2列记录为:
id,table1ID
1,1
1,2
1,3
2,1
2,2
2,3
2,4
执行:
SELECT table1.* FROM table1 WHERE table1.ID NOT IN (SELECT table1ID FROM table2 WHERE ID=1)
应该得
id,
4
如果执行tomboy0(小波波)的SQL语句,应该得:
id
(空)
from table1 left join table2
on table1.id = table2.table1ID and table2.id=1
where table2.id is null
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)mysql> select * from table2;
+----+----------+
| id | table1ID |
+----+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
+----+----------+
7 rows in set (0.00 sec)mysql> select table1.*
-> from table1 left join table2
-> on table1.id = table2.table1ID and table2.id=1
-> where table2.id is null;
+----+
| id |
+----+
| 4 |
+----+
1 row in set (0.00 sec)
对了,朋友!感谢你!我没有去细想这个问题。感谢了!:)