What is a join

The query, instructs the server to use the foreign_key as the transportation between two or more tables, thereby allowing columns from both tables to be included in the query's result set.

• cross join: rarely be used, 简单的将两张表"乘"起来

    SELECT c.first_name, c.last_name, a.address
    FROM customer c JOIN address a;
  

• Inner Joins

  • Describe how two tables how to related.

  • 查询过程(以customer和address为例)

    1. 首先，在customer的表内查找到address_id的值;
       1. 然后根据address_id，在address表内对地点名称进行查询，拼接到表后。

    code

    SELECT c.first_name, c.last_name, a.address
    FROM customer c INNER JOIN address a
      ON c.address_id = a.address_id;
    

  • Inner Join 和 Outer Join
    • 当某个表内的foreign_key在另一个表内查找不到时，会在查询结果中删除此行。如custmor.address_id=999,但在address表内无999这个address_id时，查询结果不会显示该行。

• 当不指定Join类型时，默认使用INNER JOIN
• 当join的两个表的field名称相同，可使用USING()