何时使用内部联接，何时使用左联接? [英] When to use inner join and when to left join?

问题描述

专家，

在我的 Nridubai.com 网站中，

我正在根据事件搜索查询.查询与1个或多个事件相关.

有两个表 inquiry(inquiry_id，comapnyname，category，..)和 inquiry_event(id，inquiry_id，event_id).

我将所有查询信息存储在查询表中.
并且每个与查询相关联的事件(可能是一个或多个)都存储在次要表 inquiry_event 表中.

现在，当我要基于加入(有用的/内部加入的或左加入的)事件进行搜索查询时?

目前，我正在使用内部联接正常工作.

例如.

Hi Expert,

In My Nridubai.com website,

I am searching inquiries based on event. inquiry is associated with 1 or more events.

There are 2 tables inquiry(inquiry_id,comapnyname,category,..) and inquiry_event(id,inquiry_id,event_id).

I store all inquiry information in inquiry table.
and each inquiry associated event(may be one or more) storing in secondory table inquiry_event table.

now when i want to search inquiry based on event which join in useful / Inner Join or left join ?

Currently i am using inner join working fine .

eg.

select * from inquiry inq INNER JOIN inquiry_event inqevent ON inq.inquiry_id=inqevent .inquiry_id 
WHERE inqevent.event_id=10

现在我可以在这里使用左联接吗?

请帮帮我.

Now here can i use left join ?.

Please Help me.

thanks in advance.

推荐答案

LEFT OUTER JOIN包括结果左侧表中的所有行，无论右侧的连接列是否匹配桌子.
鉴于
此内联接被称为等联接.它返回两个表中的所有列，并且仅返回联接列中具有相等值的行.

您应该阅读有关JOINS的信息.从这里开始: MSDN:加入基础知识 [ ^ ]

移至:
MSDN:使用外部联接 [ MSDN:使用内部联接 [

The LEFT OUTER JOIN includes all rows in the left table in the results, whether or not there is a match on the join column in the right table.
WHEREAS
This INNER JOIN is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.

You should read about JOINS. Start from here: MSDN: JOIN fundamentals[^]

Move on to:
MSDN: Using Outer Joins[^]
MSDN: Using Inner Joins[^]

您应该使用左连接，其中右侧的表向左侧的表添加了额外的可选信息.使用左联接时，如果右表中不存在联接列条目，则右表中的所有列均在结果的该行中填充为空.

内部联接将完全消除那些行.当您需要两个表都包含该项目的数据时，应该使用它，例如，如果您正在查找已分派的订单，并且要从订单和交货"表中进行联接，则内部联接可能很有意义，这样就可以处理未交货的订单.不在结果中.

对于您而言，答案归结为您是否要在查询尚未关联事件时返回结果.我会说您可能是这样做的，在这种情况下，我会使用左联接.

You should use a left join where the table on the right is adding extra, optional information to the table on the left. When you use a left join, if the join-column entry doesn''t exist in the right table, all the columns from the right table are filled out with nulls in that row of the result.

An inner join will eliminate those rows entirely. You should use that when you require that both tables have data for the item, for example if you''re looking up dispatched orders and you are joining from an orders and deliveries table, it might make sense to inner join so that undelivered orders are not in the result.

In your case, the answer comes down to whether you want to have a result returned when an inquiry doesn''t have an event associated with it yet. I''d say you probably do so I''d use a left join in this situation.

我认为联接最好从视觉上理解. 检查这些链接.

1. CP [编码恐怖 [

I think joins are best understood visually.
Check these links.

1. CP[^]
2. Coding Horror[^]

这篇关于何时使用内部联接，何时使用左联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！