在 oracle 中的一个查询中加入内连接和外连接

joining inner join and outer join in one query in oracle

提问by Nitin_Sen

I am trying to join 7 tables in a select query four with inner join and two with outer join.

我试图在一个选择查询中加入 7 个表，其中四个使用内部连接，两个使用外部连接。

Can I combine outer and inner join in the same query? Because when I am doing so i am not getting proper results. I tried with both ANSI joins ( INNER JOIN , LEFT OUTER JOIN) and with + sign as well. I am wondering is the order of joining is important in ANSI joins?

我可以在同一个查询中组合外连接和内连接吗？因为当我这样做时，我没有得到正确的结果。我尝试了 ANSI 连接（ INNER JOIN ， LEFT OUTER JOIN ）和 + 符号。我想知道在 ANSI 连接中加入的顺序很重要吗？

so here is the scenario,

所以这是场景，

table a table b table c table e table f tanle g table h

表a 表b 表c 表e 表f tanle g 表h

inner join ( a , b, c )

内连接（ a ， b ， c ）

inner join ( a , e , f)

内连接 ( a , e , f)

left outer outer join ( f , g)

左外外连接 ( f , g )

left outer join ( f , h)

左外连接 ( f , h )

My query ( which looks wrong) _==>

我的查询（看起来不对）_==>

FROM a inner join   b on (a.col_1 = b.col_1)
  inner join  c on (b.y = c.y)
  inner join  e on ( a.col_1      = e.col_1)
  inner join   f on (e.col_4   = f.col_4)
  left outer join  g on (g.col_5= f.col_5)
  left outer join  h on (h.col_6 = f.col_6)

Could any one please help me with the correct joining query? Any lead would be highly appreciated

任何人都可以帮助我进行正确的加入查询吗？任何线索将不胜感激

回答by Joe Taras

You can always write a query with INNERand OUTER JOINtogether.

您始终可以使用INNER和OUTER JOIN一起编写查询。

Your example is not clear because is very important, when you write a query knows your goal.

您的示例不清楚，因为它非常重要，当您编写查询时就知道您的目标。

INNER JOIN: You use this operation when you want to extract rows across two (or more) tables, and for you is important the presence of those data values in both tables.

INNER JOIN：当您想要跨两个（或多个）表提取行时，您可以使用此操作，并且对于您而言，这两个表中都存在这些数据值很重要。

OUTER JOIN: You use this operation when you want to extract rows from a main table independent if the corresponding row is presents in linked table.

OUTER JOIN: 如果相应行存在于链接表中，则当您想从主表中独立提取行时，可以使用此操作。

I try to make an example:

我试着举个例子：

I have a table (PERSON) with a list of persons. This table has a foreign key to point a table (COUNTRY) to know information about birth place. I have another table (BANK_ACCOUNT) where I store the bank account for every person (if a person has).

我有一张表 ( PERSON)，其中包含人员列表。该表有一个外键指向一个表（COUNTRY）以了解有关出生地点的信息。我有另一个表 ( BANK_ACCOUNT)，我在其中存储每个人的银行帐户（如果一个人有）。

My result wants to know: all person information (included the birth place name) and if a person has a bank account, knows it.

我的结果想知道：所有人的信息（包括出生地名），如果一个人有银行账户，就知道。

The query:

查询：

SELECT p.*, b.name, b.account_no
FROM person p
INNER JOIN country c <-- Here I apply an INNER JOIN
    ON p.fk_country = c.id
LEFT OUTER JOIN bank_account b <-- Here I apply an OUTER JOIN
    ON b.fk_person = p.id 

In this case is very important to know the goal! About another goal the upper query can be wrong.

在这种情况下，了解目标非常重要！关于另一个目标，上层查询可能是错误的。

About the order of JOIN: Is not important the order but the type yes.

关于顺序JOIN：顺序不重要，类型是的。

INNER JOIN: Is commutative. If you have table A and table B if you write

INNER JOIN: 是可交换的。如果你有表 A 和表 B 如果你写

A INNER JOIN B is the same if you write B INNER JOIN A

OUTER JOIN: Is not commutative. If you have table A and table B the follows queries are differences:

OUTER JOIN: 不是可交换的。如果您有表 A 和表 B，则以下查询是不同的：

A LEFT OUTER JOIN B

B LEFT OUTER JOIN A

Because the first tells: Get all A rows and if there exists a corresponding row in B give me those information, instead return NULL value.

因为第一个告诉：获取所有 A 行，如果 B 中存在相应的行，请给我这些信息，而不是返回 NULL 值。

The second query tells: get all B rows and if there exists a corresponding row in A give me those information, instead return NULL value.

第二个查询告诉：获取所有 B 行，如果 A 中存在相应的行，请给我这些信息，而不是返回 NULL 值。

回答by Simo Erkinheimo

Some of your INNER JOINs (="requirements") probably aren't returning anything.

您的某些 INNER JOIN (="requirements") 可能没有返回任何内容。

Inner join returns nothing from the source table (a) if the "join on -condition" can't be fullfilled. Left join returns rows (from table a) and fills the joined row's columns with nulls if not found. In both cases, if there are multiple matches, multiple rows are also returned.

如果“条件连接”无法完成，则内部连接从源表 (a) 中不返回任何内容。左连接返回行（来自表 a），如果未找到，则用空值填充连接行的列。在这两种情况下，如果有多个匹配项，也会返回多行。

Example with one row in each table:

每个表中有一行的示例：

• table A values (col_1, ..., col_4) = (1, 2, 3, 4)
• table b values (col_1, x, y, z) = (1, 3, 5, 7)
• table c values (col_1, x, y, z) = (1, 3, 5, 7)
• table e values (col_1, ..., col_4) = (1, ..., 6)
• table f values (col_1, ..., col_6) = (8, ..., 7, 4, 3)
• table g values (col_1, ..., col_6) = (7, ..., 4, 6)
• table h values (col_1, ..., col_6) = (..., 9)

• 表 A 值 (col_1, ..., col_4) = (1, 2, 3, 4)
• 表 b 值 (col_1, x, y, z) = (1, 3, 5, 7)
• 表 c 值 (col_1, x, y, z) = (1, 3, 5, 7)
• 表 e 值 (col_1, ..., col_4) = (1, ..., 6)
• 表 f 值 (col_1, ..., col_6) = (8, ..., 7, 4, 3)
• 表 g 值 (col_1, ..., col_6) = (7, ..., 4, 6)
• 表 h 值 (col_1, ..., col_6) = (..., 9)

And our query:

我们的查询：

FROM a 
inner join b on (a.col_1 = b.col_1)       -- requirement 1
inner join c on (b.y = c.y)              -- requirement 2
inner join e on ( a.col_1 = e.col_1)     -- requirement 3
inner join f on (e.col_4 = f.col_4)     -- requirement 4
left outer join g on (g.col_5= f.col_5)  -- optional 1
left outer join h on (h.col_6 = f.col_6) -- optional 2

So do we return anything?

那么我们返回任何东西吗？

• Requirement 1: a.col_1 = b.col_1; 1 = 1 --> OK
• Requirement 2: b.y = c.y; 5 = 5 -- > OK
• Requirement 3: a.col_1 = e.col_1; 1 = 1 -> OK
• Requirement 4: e.col_4 = f.col_4; 6 != 7 -> NOT OK

• 要求1：a.col_1 = b.col_1；1 = 1 --> 好的
• 要求2：by = cy; 5 = 5 --> 好的
• 要求3：a.col_1 = e.col_1；1 = 1 -> 好
• 要求4：e.col_4 = f.col_4；6 != 7 -> 不正常

Already at this point the query won't return anything and we don't event need to check left joins (there's nothing to join on).

此时查询将不会返回任何内容，并且我们不需要检查左连接（没有任何连接）。

If f.col4 would have been 6 instead of 7, the example row would be returned. Then we would also join the row(s) from g if the condition can be matched (g.col_5= f.col_5; 4 = 4 -> OK). In this example, selected colums from table h would all have value null, because the condition (optional 2) isn't met.

如果 f.col4 是 6 而不是 7，则将返回示例行。然后，如果条件可以匹配（g.col_5= f.col_5; 4 = 4 -> OK），我们也将加入来自 g 的行。在此示例中，表 h 中选定的列的值都将为 null，因为不满足条件（可选 2）。

I hope this helps you finding the issues. It's really hard to see the actual problem without valid data. In the future, consider using for example SQL Fiddlewith your questions.

我希望这可以帮助您找到问题。如果没有有效数据，真的很难看出实际问题。将来，请考虑使用SQL Fiddle 来解决您的问题。

PS. OUTER and INNER are optional words and don't make any difference in the query. So LEFT OUTER JOINis same as LEFT JOINand INNER JOINis same as JOIN.

附注。OUTER 和 INNER 是可选词，在查询中没有任何区别。所以LEFT OUTER JOIN与LEFT JOIN相同，INNER JOIN与JOIN相同。