时间戳

首页 > 解决方案 > 如果外键列具有 NULL 值，MySQL 不选择行

mysql - 如果外键列具有 NULL 值，MySQL 不选择行

问题描述

以下 SQL 有效，但如果一行具有transfers.driveras NULL，则不会选择该行。一旦我为该行添加了一个值，driver就会被选中。即使driver是，我如何选择行NULL？

SELECT 
    transfers.id, 
    transfers.name AS name, 
    transfers.pickup_time AS pickup_time, 
    transfers.voucher AS voucher, 
    transfer_types.name AS type, 
    drivers.name AS driver 
FROM
    transfers 
INNER JOIN transfer_types ON transfer_types.id = transfers.type 
INNER JOIN drivers ON drivers.id = transfers.driver

这是表的结构：

CREATE TABLE `transfers` (
  `id` int(10) UNSIGNED NOT NULL,
  `voucher` varchar(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` int(10) UNSIGNED DEFAULT NULL,
  `pickup_time` time DEFAULT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `driver` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这是外键：

ALTER TABLE `transfers`
  ADD CONSTRAINT `transfers_driver_foreign` 
      FOREIGN KEY (`driver`) REFERENCES `drivers` (`id`),
  ADD CONSTRAINT `transfers_type_foreign` 
      FOREIGN KEY (`type`) REFERENCES `transfer_types` (`id`);

标签： mysql

解决方案

使用left join而不是inner join：

SELECT 
    transfers.id, 
    transfers.name AS name, 
    transfers.pickup_time AS pickup_time, 
    transfers.voucher AS voucher, 
    transfer_types.name AS type, 
    drivers.name AS driver 
FROM
    transfers 
LEFT JOIN transfer_types ON transfer_types.id = transfers.type 
LEFT JOIN drivers ON drivers.id = transfers.driver

推荐阅读