mysql cross join的用法_MySQL中的各种JOIN(CROSS JOIN, INNER JOIN, LEFT [OUTER] JOIN) | 学步园...

1.

In MySQL,CROSS JOINis a syntactic equivalent toINNER JOIN(they can replace each other). In standard SQL, they are not equivalent.INNER JOINis used with anONclause,CROSS JOINis used otherwise.手册中提到标准SQL中CROSS JOIN交叉连接(笛卡尔积)和内连接INNER JOIN不同，但是MySQL中两者是相同的，即有[CROSS | INNER] JOIN，两者可以互相替代，而且可以只使用JOIN

2. A table reference can be aliased usingtbl_name AS alias_nameortbl_name alias_name:SELECT t1.name, t2.salary

FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;可以对数据表使用别名3. ,运算符例如SELECT * FROM table1,table2由于在MySQL中INNER JOIN与CROSS JOIN相同，INNER JOIN和,在MySQL也相同，都是产生两个表的笛卡尔积Cartesian Product

(等于两个表格的行数乘积)但是,号的优先级要低于INNER JOIN, CROSS JOIN, LEFT JOIN因此If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

4.什么时候使用ON，什么时候使用WHERE

ON应该用户数据表连接的时候指定连接条件；WHERE用于用户限制所选取的列例如ON a.column=b.column

WHERE a.column='hello'

5.可以使用LEFT JOIN查看，两个连接的表中，不符合连接条件的部分，因为不符合条件的部分LEFT JOIN之后会显示为NULL

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:

SELECT left_tbl.*

FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id

WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.

6.当别连接的表指定连接条件的列举有相同的名称的时候，不需要ON a.column=b.column不同的时候才使用ON a.column_a=b.column_b可以使用USING (column)当然也可以使用多个USING (c1,c2,c3)

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:

a LEFT JOIN b USING (c1,c2,c3)

7.其他的：#

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.

#

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.

#

The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

#

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.