Mysql Documentation 学习笔记： JOIN

大家好，今天本人给大家带来文章《Mysql Documentation 学习笔记： JOIN》，文中内容主要涉及到MySQL，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

JOIN

Basic

• In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standardSQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN isused otherwise.

• In general,parentheses can be ignored in join expressions containing only inner join operations.

inner join 的时候可以省略关键字 inner；
-- outer join 的时候不能省略

Hint

The following list describes general factors to take into account when writing joins.

• A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

# 给表定义别名的时候  AS 是可选的，下面两条sql是等价的：
select od.id , ol.id from lzh_order as od left join lzh_overdue_list as ol on ol.`order_id` = od.id;
select od.id , ol.id from lzh_order od left join lzh_overdue_list ol on ol.`order_id` = od.id;

• A table_subquery is also known as a subquery in the FROM clause. Such subqueries must include an alias to give the subquery result a table name. A trivial example follows;

# 子句查询可以作为JOIN对象与其他表连接：
SELECT * FROM (SELECT 1, 2, 3) AS t1;
# 但是，必须要给子句一个别名（AS仍然是可选的）：
select * from (select * from lzh_order where id 

• INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

# 如果一个没有条件的JOIN 和 逗号连接的两张表的查询是等价的，此时查询结果是两张表的笛卡尔积
SELECT * FROM cellphone , company;
SELECT * FROM cellphone JOIN company;
# However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. 逗号的优先级要比JOIN等其他运算符的优先级低。

• The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause.Generally, you should use the 

  # 第一条关于ON和WHERE的理解就是这样的：将ON里面的条件拿到WHERE里面是同样适用的。
  SELECT * FROM cellphone JOIN company ON cellphone.`manufacture` = company.id;
  SELECT * FROM cellphone JOIN company WHERE cellphone.`manufacture` = company.id;
  # 第二条也不难理解：拿上面的语句来说ON告诉MYSQL以这个条件去连接表，WHERE的执行过程就稍微不一样，前面提到过如果JOIN不指定ON条件的话，结果是两个表的笛卡尔积，然后再去笛卡尔积中匹配WHERE成立的条件。

  reference: mysql documentation : join syntax

  • If there is no matching row for the right table in the 

    SELECT left_tbl.*
        FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id }
        WHERE right_tbl.id IS NULL;
        
    # 实现就是这样的：
    SELECT * FROM cellphone RIGHT JOIN company ON cellphone.`manufacture` = company.id WHERE cellphone.`series` IS NULL;
    
    # 但是有这样一个问题，同样的语句,把 IS 换成 = 就会有不一样的结果：
    
    SELECT * FROM cellphone RIGHT JOIN company ON cellphone.`manufacture` = company.id WHERE cellphone.`series` = NULL;
    
    # IS：Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN. 
    # = ：Equal
    # TODO 不是很好理解，二者的定义上也不太容易区分来解释这种情况，是说FALSE 、 NULL 、TRUE、UNKNOWN等就必须用IS来判断吗？若读者有更好的解释，请指教。

    TODO...

    以上就是《Mysql Documentation 学习笔记： JOIN》的详细内容，更多关于mysql的资料请关注golang学习网公众号！