有两个表格,table1和table2,两个表中有相同的字段name,
现想从table1中查出所有name字段的值不在tables2中name值的纪录
---------------------------------------------------------------
select * from table1 left join table2 on table1.name <= >table2.name
---------------------------------------------------------------
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.name = Table2.name WHERE Table2.name IS NULL;
> > > >
在MySQL中下列语句还不能工作:
查询:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
可以被写成:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
查询:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
可以被写成:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
现想从table1中查出所有name字段的值不在tables2中name值的纪录
---------------------------------------------------------------
select * from table1 left join table2 on table1.name <= >table2.name
---------------------------------------------------------------
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.name = Table2.name WHERE Table2.name IS NULL;
> > > >
在MySQL中下列语句还不能工作:
查询:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
可以被写成:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
查询:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
可以被写成:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
解决方案 »
- select 与 update 如何连用
- 我的my.cnf, 都调到最低了,还是占用了300多M虚拟内存
- MYSQL 多表查询
- 数据库怎么构建才合理,并且可扩展?
- 论坛显示置顶贴的SQL
- 请教向另一台机器的MySQL数据库插入记录
- 急,熟悉linux命令的高手请帮忙!!在linux的客户机上怎么用命令导mssql数据库(表结构跟数据)到一个文件中去!!
- 入门问题:请问mysql跟mysql-max有什么区别?
- 错误:Duplicate entry '0' for key 1!?!?!?!
- 既有逗号关联,又有INNER JOIN关联;逗号关联的两个表调换一下顺序,执行结果就不一样了?
- MYSQL Beta有没有时间限制!
- 送给你一本好书《MySQL核心源代码分析》
查询:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
可以被写成:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
但是我是
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 where condition);
那该怎么改呢?
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id = 'condition';
SELECT table1.* FROM table1,table2 WHERE (table1.id=table2.id) AND (table2.id = 'condition');