mysql - 如何在MySQL中进行完全外部联接

我已经搜索过并且已经花了很多时间进行此查询...
我想要的是我需要在某一列为空时输出特定原料的原料名称。我已经尝试了完全外部联接，但是我不知道为什么phpmyadmin不接受我的查询命令，我尝试了几乎所有可以的方法。

表格：

Purchaserequest_tbl

列：purchaserequestID，orderID，rawmaterialID，rawmaterialquantity

rawmaterial_tbl：

列：ID，名称，类别，单位

这是查询

SELECT `purchaserequest_tbl`.`orderID` AS 'Order No',
       `rawmaterial_tbl`.`rawMaterialName` AS 'Raw Material Name',
       `purchaserequest_tbl`.`rawMaterialQty` AS 'Length (inches)'
FROM `purchaserequest_tbl`
LEFT OUTER JOIN `rawmaterial_tbl`
 ON `purchaserequest_tbl`.`rawMaterialID` = `rawmaterial_tbl`.`rawMaterialName`
WHERE `purchaserequest_tbl`.`purchaseRequestStatus` = 'Requested'

我希望有人能够帮助我..我真的很难在这里

最佳答案

如果我理解您，则您发布的查询对您有效，但未给出预期的结果。完全外部联接对您不起作用，因为MYSQL不支持完全外部联接，因此您必须进行2次左外部联接才能获得此结果，请尝试以下操作：

SELECT `purchaserequest_tbl`.`orderID` AS 'Order No',
       `rawmaterial_tbl`.`rawMaterialName` AS 'Raw Material Name',
       `purchaserequest_tbl`.`rawMaterialQty` AS 'Length (inches)'
FROM `purchaserequest_tbl`
LEFT OUTER JOIN `rawmaterial_tbl`
ON `purchaserequest_tbl`.`rawMaterialID` = `rawmaterial_tbl`.`rawMaterialName`
WHERE `purchaserequest_tbl`.`purchaseRequestStatus` = 'Requested'
UNION
SELECT `purchaserequest_tbl`.`orderID` AS 'Order No',
       `rawmaterial_tbl`.`rawMaterialName` AS 'Raw Material Name',
       `purchaserequest_tbl`.`rawMaterialQty` AS 'Length (inches)'
FROM `rawmaterial_tbl`
LEFT OUTER JOIN `purchaserequest_tbl`
ON `purchaserequest_tbl`.`rawMaterialID` = `rawmaterial_tbl`.`rawMaterialName`
WHERE `purchaserequest_tbl`.`purchaseRequestStatus` = 'Requested'

如果只希望这些为空，则在两个where子句中添加WHERE rightTable.Column为null