MySQL JOIN 和 LEFT JOIN 之间的主要区别是什么？

了解 MySQL JOIN 与 LEFT JOIN 之间的区别

MySQL JOIN 是一种基本操作，它基于共同专栏。默认情况下，MySQL 执行 INNER JOIN，它仅返回所有连接表中匹配的行。

但是，当用户认为缺少显式连接类型意味着 LEFT JOIN 时，就会出现常见的误解。事实并非如此；默认连接类型仍为 INNER JOIN。

LEFT JOIN 与 INNER JOIN 的示例

考虑以下查询：

SELECT
    `DM_Server`.`Jobs`.*,
    `DM_Server`.servers.Description AS server,
    digital_inventory.params,
    products.products_id,
    products.products_pdfupload,
    customers.customers_firstname,
    customers.customers_lastname
FROM `DM_Server`.`Jobs`
    INNER JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
    JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
    JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, ".pdf")
    JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

此查询执行所有表上的 INNER JOIN，仅返回在所有连接中具有匹配值的行

现在，让我们修改查询以使用 LEFT JOIN：

SELECT
    `DM_Server`.`Jobs`.*,
    `DM_Server`.servers.Description AS server,
    digital_inventory.params,
    products.products_id,
    products.products_pdfupload,
    customers.customers_firstname,
    customers.customers_lastname
FROM `DM_Server`.`Jobs`
    LEFT JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
    LEFT JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
    LEFT JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, ".pdf")
    LEFT JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

此查询中的 LEFT JOIN 返回左表（“作业”）中的所有行，并且仅返回匹配右表中的行。这意味着即使右表中没有匹配的行，“Jobs”中的行仍将包含在结果中。

联接的视觉表示

以下是 INNER JOIN 和 LEFT JOIN 的视觉表示：

INNER连接：

              TABLE 1               TABLE 2
+-----------+------+  +-----------+------+
| Column 1  | KEY  |  | Column 3  | KEY  |
+-----------+------+  +-----------+------+
| ROW 1.1   | 1    |  | ROW 2.1   | 1    |
| ROW 1.2   | 2    |  |           |      |
|           |      |  | ROW 2.2   | 2    |
+-----------+------+  +-----------+------+

Result:
| Column 1 | Column 3 |
+-----------+-----------+
| ROW 1.1   | ROW 2.1   |
| ROW 1.2   | ROW 2.2   |

左连接：

              TABLE 1               TABLE 2
+-----------+------+  +-----------+------+
| Column 1  | KEY  |  | Column 3  | KEY  |
+-----------+------+  +-----------+------+
| ROW 1.1   | 1    |  | ROW 2.1   | 1    |
| ROW 1.2   | 2    |  |           |      |
| ROW 1.3   | 3    |  |           |      |
+-----------+------+  +-----------+------+

Result:
| Column 1 | Column 3 |
+-----------+-----------+
| ROW 1.1   | ROW 2.1   |
| ROW 1.2   | NULL      |
| ROW 1.3   | NULL      |

以上是MySQL JOIN 和 LEFT JOIN 之间的主要区别是什么？的详细内容。更多信息请关注PHP中文网其他相关文章！