INNER和LEFT OUTER加入帮助

Say I have 3 tables. TableA, TableB, TableC

说我有3张桌子。表A,表B,表C.

I need to operate the recordset that is available after a INNER JOIN.

我需要操作INNER JOIN后可用的记录集。

Set 1 -> TableA INNER JOIN TableB
Set 2 -> TableC INNER JOIN TableB

I need the Set 1 irrespective of if Set 2 is empty or not (LEFT OUTER JOIN) comes to mind.

不管Set 2是否为空(LEFT OUTER JOIN),我都需要Set 1。

So essentially, I am trying to write a query and have come this far

基本上,我正在尝试编写一个查询并且已经走到了这一步

SELECT *
  FROM TableA 
       INNER JOIN TableB ON ...
       LEFT OUTER JOIN (TableC INNER JOIN TableB)

How would I write in SQL Server?

我将如何在SQL Server中编写?

EDIT: In reality, what I am trying to do is to join multiple tables. How would your response change if I need to join multiple tables ex: OUTER JOIN OF (INNER JOIN of TableA and TableB) and (INNER JOIN OF TableC and TableD) NOTE: There is a new TableD in the equation

编辑:实际上,我想要做的是加入多个表。如果我需要连接多个表,你的响应会如何变化ex:OUTER JOIN OF(TableA和TableB的内部联接)和(TableC和TableD的内部联接)注意:等式中有一个新的TableD

9 个解决方案

#1

 SELECT * FROM TableA 
       INNER JOIN TableB ON TableB.id = TableA.id
       LEFT JOIN TABLEC ON TABLEC.id = TABLEB.id

I Don't know what columns you are trying to use but it is just that easy

我不知道你试图使用哪些列,但它就是这么简单

Edit: Looking at your edit it seems that you are confused about what Joins actually do. In the example I have written above you will recieve the following results.

编辑:看看你的编辑,似乎你对Joins实际做的事感到困惑。在上面写的示例中,您将收到以下结果。

Columns -> You will get all of the columns for TableA,TableB and TableC

列 - >您将获得TableA,TableB和TableC的所有列

Rows-> You will start off with all of the rows from tableA. Next you will remove all rows from TableA that do not have a matching "id" in Table B.(You will have duplicates if it is not a 1:1 relationship between TableA and TableB).

行 - >您将从tableA的所有行开始。接下来,您将从TableA中删除表B中没有匹配“id”的所有行。(如果TableA和TableB之间不是1:1的关系,则会有重复项)。

Now if you take the results from above you will match any records from TableC that match the TableB.id column. Any rows from above that do not have a matching TableC record will get a null value for all of the columns from TableC in the results.

现在,如果您从上面得到结果,您将匹配TableC中与TableB.id列匹配的任何记录。上面没有匹配的TableC记录的任何行将在结果中获得TableC中所有列的空值。

ADVICE- I am betting that only part of this made sense to you but my advice is that you start writing some queries, predict the results and then see if your predictions are correct to see if you understand what it is doing.

建议 - 我打赌只有部分内容对您有意义,但我的建议是您开始编写一些查询,预测结果,然后看看您的预测是否正确,看看您是否理解它在做什么。

#2

What you want isn't a JOIN but a UNION.

你想要的不是JOIN而是UNION。

SELECT * FROM TableA INNER JOIN TableB ON ...
UNION
SELECT * FROM TableC INNER JOIN TableD ON ...

#3

You can actually add an ordering to your joins just like in a math equation where you might do this: (5 + 4) * (3 + 1).

实际上,您可以在数学等式中为连接添加排序:(5 + 4)*(3 + 1)。

Given the second part of your question, give this a try:

鉴于问题的第二部分,请尝试一下:

SELECT
     <your columns>
FROM
     (TableA INNER JOIN Table B ON <join criteria for A to B>)
LEFT OUTER JOIN
     (TableC INNER JOIN Table D ON <join criteria for C to D>) ON
     <join criteria for AxB to CxD>

#4

Select * from ((((TableA a inner join TableB b on a.id = b.id) 
                left outer join TableC c on b.id = c.id)
                full outer join TableD d on c.id = d.id)
                right outer join TableE e on e.id = d.id)
                /* etc, etc... */

You can lose the brackets if you want.

如果需要,您可以丢失括号。

#5

try this..

SELECT *
  FROM TableA              a 
       INNER JOIN TableB   b ON a.id=b.id
       LEFT OUTER JOIN (SELECT *
                            FROM TableC            c
                                INNER JOIN TableD  d on c.id=d.id
                       ) dt on b.id=dt.id

#6

You didn't give your join conditions or explain how the tables are intended to be related, so it's not obvious how this might be simplified.

您没有给出连接条件或解释表格是如何相关的,因此如何简化这一点并不明显。

SELECT a.a_id, b1.b_id b1_id, b2_id, bc.c_id
FROM TableA a JOIN TableB b1 on a.b_id = b1.b_id
LEFT JOIN (SELECT c.c_id, b2.b_id b2_id
    FROM TableC c JOIN TableB b2 ON c.b_id = b2.b_id
  ) bc ON bc.c_id = a.c_id;

Looking at your latest edit, you can do something along the lines of:

查看最新的编辑,您可以执行以下操作:

SELECT <columns>
FROM (SELECT <columns> FROM TableA JOIN TableB ON <A-B join conditions>)
           LEFT JOIN
           (SELECT <columns> FROM TableC JOIN TableD ON <C-D join conditions>)
           ON <AB-CD join conditions>

Although you don't actually need the inner projections, and can do:

虽然你实际上并不需要内部投影,但可以做到:

SELECT <columns>
FROM (TableA a JOIN TableB b ON <A-B join conditions>)
           LEFT JOIN
           (TableC c JOIN TableD d ON <C-D join conditions>)
           ON <AB-CD join conditions>

Where the AB-CD join conditions are written in terms of columns of a, b, c, d etc directly.

AB-CD连接条件是直接用a,b,c,d等列写的。

#7

Since you're using Sql Server, why not create views that help you? Stuffing everything in a gigantic Sql statement can become hard to read. An example view might look like:

既然您正在使用Sql Server,为什么不创建可以帮助您的视图?在巨大的Sql语句中填充所有内容可能会变得难以阅读。示例视图可能如下所示:

create view AandB
as
select *
from A
inner join B on B.aid = A.aid

And the same for CandD. Then you can retrieve the optional join with simple Sql:

CandD也是如此。然后你可以用简单的Sql检索可选的连接:

select *
from AndB
left outer join CandD on AndB.cid = CandD.cid

If you're interested in rows from both sets, you can do a full join:

如果您对两个集合中的行感兴趣,可以进行完全加入:

select *
from AndB
full outer join CandD on AndB.cid = CandD.cid

#8

Assuming I Understand your question, I think this is what you're asking for:

假设我理解你的问题,我认为这就是你要求的:

SELECT * 
FROM TableA INNER JOIN TableB on TableA.JoinColumn = TableB.JoinColumn
LEFT OUTER JOIN TableC on TableB.JoinColum = TableC.JoinColumn
INNER JOIN TableD on TableC.JoinColumn = TableD.JoinColumn

Note that the JoinColumn used to join A & B doesn't necesarilly have to be the same column as the one used to join B & C, and so on for C & D.

请注意,用于加入A&B的JoinColumn不必与用于加入B&C的列相同,依此类推C&D。

#9

SELECT *
  FROM TableA A
 INNER JOIN TableB B  ON B.?? = A.??  AND ...
  LEFT JOIN TableC C  ON C.?? = B.??  AND ...
  LEFT JOIN TableB B2 ON B2.?? = C.?? AND ...
  LEFT JOIN TableD D  ON D.?? = C.??  AND ...

So here's the thing: logically, joins aren't actually between specific tables, they are between a table and the rest of the "set" (of joins and tables). So while you know that there is a 1-to-1 relationship between C and B2 or between C and D, you can't INNER JOIN to C because C could be null from it's LEFT JOIN to B, which will eliminate those rows, effectively undoing your LEFT join.

所以这就是事情:从逻辑上讲,连接实际上并不在特定的表之间,它们位于表和“set”(连接和表)的其余部分之间。因此,虽然你知道C和B2之间或C和D之间存在一对一的关系,但是你不能INNER JOIN到C,因为C可以从它的LEFT JOIN到B为空,这将消除那些行,有效地撤消你的左连接。

So basically, any joins to a table that's LEFT outer joined must also be LEFT outer joined. Does this make sense?

所以基本上,对于LEFT外连接的表的任何连接也必须是LEFT外连接。这有意义吗?

#1

 SELECT * FROM TableA 
       INNER JOIN TableB ON TableB.id = TableA.id
       LEFT JOIN TABLEC ON TABLEC.id = TABLEB.id