难以理解联接的逻辑 [英] Difficulty Understanding Logic of Joines

问题描述

我将使用以下查询来说明我的问题:

I'll use the following query to illustrate my question:

select a.shipperid,
       b.orderid,
       b.custid
from shippers a inner join orders b

on a.shipperid = b.shipperid

Shipperid是托运人表中的主键，也是订单表中的外键.
只有三个发运ID，每个ID与订单表中的许多不同订单相关联.当托运人ID匹配时，联接将匹配表.

Shipperid is the primary key in the shippers table, and it is also the foreign key in the orders table.
There are only three shipping IDs, and each of them is associated with many different orders in the order table. The join will match the tables when the shipper IDs match.

但是，托运人表中的每个托运人ID与订单表中的许多不同行相关联.

However, each of the shipper IDs in the shippers table is associated with many, many different rows in the orders table.

那么，我怎么知道托运人表中的行与订单表中的行不是以任意方式匹配的?

So, how do I know that the rows in the shippers table aren't being match with the rows in the orders table in an arbitrary manner?

推荐答案

在您当前的查询中，您正在执行内部联接，这将使您仅输出匹配的行::

In your current query you are doing inner join which will give you output of only matching lines::

请参阅下图中的内容，以使您更好地理解.

See in the image below for your better understanding.

因此，要回答您的问题，您需要在以下情况下进行左联接:

So to answer your question, you need to do Left join in your case like::

select a.shipperid,
       b.orderid,
       b.custid
from shippers a 
left join orders b

on a.shipperid = b.shipperid

如果该运送中没有任何订单，则此结果将为Orders的空值.

the result of this will be with null value of Orders if there is not any order on that shipping.

这篇关于难以理解联接的逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！