select * from table_a where col_a_a=123 and col_a_b in (select col_b_a from table_b where col_b_b=456 and col_x in(2, 3)) order by col_a_c如果col_x 属于 table_a,则你的: select col_b_a from table_b where col_b_b=456 and col_x in(2, 3) 这句不可能对,会报找不到列col_x,所以不是结果正确不正确,而是你的语句就不正确,无法运行.
按理说会报错 你desc table_a; desc table_b;贴出来看看
特意搭了环境-- phpMyAdmin SQL Dump -- version 3.2.4 -- http://www.phpmyadmin.net -- -- 主机: localhost -- 生成日期: 2011 年 09 月 30 日 14:42 -- 服务器版本: 5.1.41 -- PHP 版本: 5.3.1SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";-- -- 数据库: `test` -- CREATE DATABASE `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `test`;-- ---------------------------------------------------------- -- 表的结构 `table_a` --CREATE TABLE IF NOT EXISTS `table_a` ( `col_a_a` int(11) NOT NULL, `col_a_b` int(11) NOT NULL, `col_a_c` int(11) NOT NULL, `col_x` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;-- -- 转存表中的数据 `table_a` --INSERT INTO `table_a` (`col_a_a`, `col_a_b`, `col_a_c`, `col_x`) VALUES (123, 456, 20, 2), (123, 789, 10, 3), (123, 789, 0, 1), (456, 456, 30, 2);-- ---------------------------------------------------------- -- 表的结构 `table_b` --CREATE TABLE IF NOT EXISTS `table_b` ( `col_b_a` int(11) NOT NULL, `col_b_b` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;-- -- 转存表中的数据 `table_b` --INSERT INTO `table_b` (`col_b_a`, `col_b_b`) VALUES (123, 123), (456, 456), (789, 456); 这是执行过程mysql> use test Database changed mysql> select * from table_a where col_a_a=123 and col_a_b in (select col_b_a fr om table_b where col_b_b=456 and col_x in(2, 3)) order by col_a_c; +---------+---------+---------+-------+ | col_a_a | col_a_b | col_a_c | col_x | +---------+---------+---------+-------+ | 123 | 789 | 10 | 3 | | 123 | 456 | 20 | 2 | +---------+---------+---------+-------+ 2 rows in set (0.00 sec)mysql> select * from table_a where col_a_a=123 and col_a_b in (select col_b_a fr om table_b where col_b_b=456) and col_x in(2, 3) order by col_a_c ; +---------+---------+---------+-------+ | col_a_a | col_a_b | col_a_c | col_x | +---------+---------+---------+-------+ | 123 | 789 | 10 | 3 | | 123 | 456 | 20 | 2 | +---------+---------+---------+-------+ 2 rows in set (0.00 sec)mysql>
帮助里面说了,子查询里面总会包含对子查询外面的表的引用。13.2.8.7. 关联子查询 相关联的子查询是一个包含对表的引用的子查询。该表也显示在外部查询中。例如: SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2); 注意,即使子查询的FROM子句不提及表t1,该子查询也会包含一个对t1中一列的引用。所以,MySQL看上去位于子查询的外部,并在外部查询中查找t1。
col_a_a=123 and col_a_b in
(select col_b_a from table_b where col_b_b=456 and col_x in(2, 3))
order by col_a_c如果col_x 属于 table_a,则你的:
select col_b_a from table_b where col_b_b=456 and col_x in(2, 3)
这句不可能对,会报找不到列col_x,所以不是结果正确不正确,而是你的语句就不正确,无法运行.
你desc table_a;
desc table_b;贴出来看看
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2011 年 09 月 30 日 14:42
-- 服务器版本: 5.1.41
-- PHP 版本: 5.3.1SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `test`
--
CREATE DATABASE `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `test`;-- ----------------------------------------------------------
-- 表的结构 `table_a`
--CREATE TABLE IF NOT EXISTS `table_a` (
`col_a_a` int(11) NOT NULL,
`col_a_b` int(11) NOT NULL,
`col_a_c` int(11) NOT NULL,
`col_x` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;--
-- 转存表中的数据 `table_a`
--INSERT INTO `table_a` (`col_a_a`, `col_a_b`, `col_a_c`, `col_x`) VALUES
(123, 456, 20, 2),
(123, 789, 10, 3),
(123, 789, 0, 1),
(456, 456, 30, 2);-- ----------------------------------------------------------
-- 表的结构 `table_b`
--CREATE TABLE IF NOT EXISTS `table_b` (
`col_b_a` int(11) NOT NULL,
`col_b_b` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;--
-- 转存表中的数据 `table_b`
--INSERT INTO `table_b` (`col_b_a`, `col_b_b`) VALUES
(123, 123),
(456, 456),
(789, 456);
这是执行过程mysql> use test
Database changed
mysql> select * from table_a where col_a_a=123 and col_a_b in (select col_b_a fr
om table_b where col_b_b=456 and col_x in(2, 3)) order by col_a_c;
+---------+---------+---------+-------+
| col_a_a | col_a_b | col_a_c | col_x |
+---------+---------+---------+-------+
| 123 | 789 | 10 | 3 |
| 123 | 456 | 20 | 2 |
+---------+---------+---------+-------+
2 rows in set (0.00 sec)mysql> select * from table_a where col_a_a=123 and col_a_b in (select col_b_a fr
om table_b where col_b_b=456) and col_x in(2, 3) order by col_a_c ;
+---------+---------+---------+-------+
| col_a_a | col_a_b | col_a_c | col_x |
+---------+---------+---------+-------+
| 123 | 789 | 10 | 3 |
| 123 | 456 | 20 | 2 |
+---------+---------+---------+-------+
2 rows in set (0.00 sec)mysql>
相关联的子查询是一个包含对表的引用的子查询。该表也显示在外部查询中。例如:
SELECT * FROM t1 WHERE column1 = ANY
(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
注意,即使子查询的FROM子句不提及表t1,该子查询也会包含一个对t1中一列的引用。所以,MySQL看上去位于子查询的外部,并在外部查询中查找t1。