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 的时候不能省略

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 < 100) as 100_rows right join lzh_overdue_list ol on ol.`order_id` = 100_rows.id;
# 过程：将子句中的执行结果 100_rows 与 lzh_overdue_list连接

• 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 clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.
  • ON条件里可以是任何条件，同时它也可以被用在WHERE子句中(我理解的大概是这个意思)。
  • 通常ON用于指定如何连接表，而WHERE在连接的结果里面选择保留那些行。

# 第一条关于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 ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

这个是OUTER JOIN 里面比较重要的一个特性：在LEFT JOIN 里面如果右表没有对应左表某行的匹配项，则在该连接行中将右表字段置为NULL，可以利用这个NULL特性找出一个表在另一个表中无对应项的行。

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来判断吗？若读者有更好的解释，请指教。

• The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

using里面的列必须是参与连接的表里面共有的(否则会报错)，此时会根据这个共有的列去比较，输出连接结果。

table_a join table_b using (column1)
# 下面两条语句是等价的
select * from orders right join cellphone using (cellphone_id, price);
select * from orders right join cellphone on orders.cell_phone = cellphone.cellphone_id and order.price = cellphone.price;

• RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

LEFT JOIN 和 RIGHT JOIN 可以互相转换，但是为了垮库，推荐使用LEFT JOIN（像SQLite就不支持RIGHT JOIN）。

• The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

NATURAL JOIN , NATURAL LEFT/RIGHT JOIN 和USING的效果是一样的，都会去根据连接的表中相同的列去匹配比较。只不过USING会指定去匹配哪些列，但是NATURAL会匹配所有相同的列。