Mysql Documentation阅读笔记:JOIN

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.

LEFTJOINと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では同じ列がすべて一致します.