mysql - SQL 这个 left jion 和 left outer jion 怎么结果是一样的?

Question

SELECT b.id,b.cid,b.name,t.type FROM shbooks b LEFT JOIN shtype t ON t.id = b.cid;

SELECT b.id,b.cid,b.name,t.type FROM shbooks b LEFT OUTER JOIN shtype t ON t.id = b.cid;

不加OUTER的时候,我完全能理解,把表1在表2对应的类名显示出来,

但是加上 OUTER后,我看到结果还是一模一样,用什么例子可以理解加和不加的区别呢?

Answer 1

LEFT JOIN and LEFT OUTER JOIN are the same, except that usually when we write SQL statements, we omit OUTER. This can be understood like inner joins. When we write inner joins, we usually omit INNER and write JOIN directly

Multiple table links are available

• Inner join (JOIN or INNER JOIN)

  SELECT * FROM a [INNER] JOIN b ON a.id=b.id

• Outer join

  • Left JOIN or LEFT OUTER JOIN

    SELECT * FROM a LEFT [OUTER] JOIN b ON a.id=b.id

  • Right JOIN or RIGHT OUTER JOIN

    SELECT * FROM a RIGHT [OUTER] JOIN b ON a.id=b.id 

  • A complete outer join is to connect the left join and right join statements together through the key UNION

    SELECT * FROM a LEFT [OUTER] JOIN b ON a.id=b.id
    UNION
    SELECT * FROM a RIGHT [OUTER] JOIN b ON a.id=b.id

• Cross join (CROSS JOIN), this will involve the Cartesian product. My personal understanding of the Cartesian product is the cross combination of two tables. So the set result obtained is the queried records that meet the conditions in table A * the records that meet the conditions in table B.

There is a small pitfall in this, that is, you cannot add ON to the statement like inner connection and outer connection. If you add it, the query result will be the same as inner connection

SELECT * FROM a CROSS JOIN b where a.id=1

Answer 2

These two should be the same.
Left join is the abbreviation of left outer join.
You can use explain extended and show warnings to see the statements after database optimization and rewriting. The two SQLs are the same.