左外联接等效 [英] LEFT OUTER JOIN EQUIVALENT

问题描述

我有一个表包含空值.在ORDER表中，在PART_ID部分中有2个null，在CUSTOMER_ID中有2个null值.

I have a tables contains null values. In ORDER table i have 2 null in PART_ID section and 2 null values in CUSTOMER_ID.

我有这种查询:

SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       C.CUST_NAME, C.CUST_CODE, P.PART_NAME, P.PART_CODE
FROM [ORDER] O
     LEFT OUTER JOIN PART P ON P.PART_ID = O.PART_ID
     LEFT OUTER JOIN CUSTOMER C ON C.CUST_ID = O.CUST_ID 

这是我的问题.不使用外部联接怎么办? 我尝试了很多事情，包括不存在的地方或这个地方；

So here is my question. How can i do it without using outer join ? I tried too many things including where not exists or this ;

SELECT *
FROM [ORDER] O ,CUSTOMER C, PART P
WHERE C.CUST_ID = (
  SELECT CUST_ID FROM CUSTOMER C WHERE O.CUST_ID = C.CUST_ID
) AND P.PART_ID = (SELECT PART_ID FROM PART P WHERE O.PART_ID = P.PART_ID) 

但是我找不到解决方案.如果有解决方案，它将如何?

but i couldn't find solution. If there is a solution how it will be ?

(注意:这是家庭作业.)

(Note: this is homework.)

我有这种桌子:

和左外连接给出:

硬件说不使用外部联接就可以做到，并得到与左外部联接相同的表.但是就像我所说的那样.我也在使用MSSQL.

the hw said do it without using outer join and get same table as left outer join gives. But like a said i coulnd't. I'm also using MSSQL.

推荐答案

外部联接在内部联接上产生超集.实际上，来自维基百科:左外部联接返回所有内部联接的值以及左表中与右表不匹配的所有值.

Outer join produces super-set over inner join. Indeed, from Wikipedia: A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

因此，要使用内部联接对左外部联接建模，可以在具有相同联接条件的相同表与另一个 SELECT SELECT 的 UNION >从第一个表返回的所有行都与右表不匹配(我将您的情况减少为单个左联接):

So to model left outer join using inner join one could use UNION of inner join SELECT between same tables with same join condition and another SELECT from 1st table that returns all rows without a match from the right table (I reduced your case to a single left join):

SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       P.PART_NAME, P.PART_CODE
FROM [ORDER] O JOIN PART P ON P.PART_ID = O.PART_ID
UNION
SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       NULL, NULL 
FROM [ORDER] O
WHERE NOT EXISTS (SELECT 'found' FROM PART P WHERE P.PART_ID = O.PART_ID)

这篇关于左外联接等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！