MySQL如果子查询的结果为NULL，则整个查询将失败 - MySQL the whole query fails if result of sub query is NULL

MySQL the whole query fails if result of sub query is NULL

我有一种情况，需要用同一表的有效数据中的随机数据生成行

我已经用$min = 1和$max = [SELECT MAX(ID) FROM patient] - 1 通过php rand($min, $max)函数生成了ID

Select
tblFirstName.FirstName,
tblLastName.LastName,
tblBirthDate.BirthDate,
tblLocation.Location
From
(SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName,
(SELECT LastName FROM patient WHERE ID > 74964 AND LastName != '' LIMIT 1) AS tblLastName,
(SELECT BirthDate FROM patient WHERE ID > 26360 LIMIT 1) AS tblBirthDate,
(SELECT Location FROM patient WHERE ID > 68356 AND Location != '' LIMIT 1) AS tblLocation

现在，来自上述查询26360的ID > 26360是随机数，并且使用>来避免如果26360被删除的可能性

问题：

如果任何子查询未返回任何结果，则完整查询将失败，并且不返回任何结果。

试试这个：

SELECT (SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName,
         (SELECT LastName FROM patient WHERE ID > 74964 AND LastName != '' LIMIT 1) AS tblLastName,
         (SELECT BirthDate FROM patient WHERE ID > 26360 LIMIT 1) AS tblBirthDate,
         (SELECT Location FROM patient WHERE ID > 68356 AND Location != '' LIMIT 1) AS tblLocation

将所有查询更改为：

SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' UNION SELECT NULL FROM DUAL LIMIT 1

即添加UNION SELECT NULL FROM DUAL（在所有子查询中）

您需要使用LEFT OUTER JOIN:

Select
tblFirstName.FirstName,
tblLastName.LastName,
tblBirthDate.BirthDate,
tblLocation.Location
From
(SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName
LEFT JOIN
(
(SELECT LastName FROM patient WHERE ID > 74964 AND LastName != '' LIMIT 1) AS tblLastName, (SELECT BirthDate FROM patient WHERE ID > 26360 LIMIT 1) AS tblBirthDate,
(SELECT Location FROM patient WHERE ID > 68356 AND Location != '' LIMIT 1) AS tblLocation
)