mysql - SQL问个基础例子,书上的,我怎么看都看不懂..谁帮我解释一下第2个为什么和第1个一样?

Question

SELECT
    id,
    name,
    cid
from
    shbooks
where
    cid=(
        SELECT
            cid 
        FROM
            shbooks
        where
            name= '光荣与梦想'
        );

上面这个我完全看得懂,返回<光荣与梦想>的cid,然后用这个值作为条件,匹配整个表的cid;

---

SELECT
    b1.id,
    b1.name,
    b1.cid
from
    shbooks b1,
    shbooks b2 
where
    b1.cid=b2.cid
and
    b2.name='光荣与梦想';

但是这个,条件是 b1.cid=b2.cid 的话,会返回整个表,为什么加上个 b2.name='光荣与梦想' 就能返回 和 光荣与梦想 相同cid 的别的书???

---

答案不在长,也不在语言是不是准确,只要让我能明白怎么个逻辑就行了..谢谢了!

Answer 1

Original

b1.cid b1.name
1     光荣与梦想
2     另一本书
1     梦想与光荣

Condition b1.cid = b2.cid (5 results are obtained by filtering out of 9 results) As long as you understand why there are 9 results, there should be no doubt about the whole problem.

b1.cid b1.name b2.cid b2.name
1     光荣与梦想 1    光荣与梦想
1     光荣与梦想 1    梦想与光荣
2     另一本书   2    另一本书
1     梦想与光荣 1    梦想与光荣
1     梦想与光荣 1    光荣与梦想

Condition b2.name='Glory and Dream'
then you will get

b1.cid b1.name b2.cid b2.name
1     光荣与梦想 1    光荣与梦想
1     梦想与光荣 1    光荣与梦想

---

我就是提问的的人,这是我补充的!
我给大家解释解释到底是怎么回事,
其实逻辑就是自己把自己当俩表;
然后算自己的的笛卡尔集,
然后再用第二个条件筛选!

Answer 2

If your first way of writing contains multiple cids, would it be wrong?
You can refer to the following writing method:

/*子查询*/
select id,name,cid 
  from shbooks 
where cid in (select cid from shbooks where name='');

--或者这种写法
select id,name,cid
  from shbooks outerquery
where cid exists  
  (select 1 from shbooks innerquery where outerquery.cid = innerquery.cid and innerquery.name='');
  

As for your second way of writing, do you understand inner join? Do you think the following writing method makes it easier to understand?

select id,name,cid
  from shbooks main
  inner join (select cid from shbooks where name = '') child
  on main.cid = child.cid; 

select id,name,cid
  from shbooks main
  inner join shbooks child on main.cid = child.cid
  where child.name = ''; 

Finally, do you follow the examples in the book? If so, I suggest you don’t read that book!

Answer 3

The conditions are two, big brother, b2.name='Glory and Dream' and b1.cid = b2.cid

Answer 4

Just think of it as two different tables, this is a self-join

Answer 5

@broken mirror His is the correct solution, first Cartesian set, and then filter. This is the implementation principle. However, generally this kind of statement SQL will be automatically optimized. It doesn't really do a Cartesian product first and then filter.