joining inner join and outer join in one query in oracle

Question

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

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?

so here is the scenario,

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

inner join ( a , b, c )

inner join ( a , e , f)

left outer outer join ( f , g)

left outer join ( 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

Answer 1

You can always write a query with INNER and OUTER JOIN together.

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.

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.

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).

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.

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

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:

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.

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.

Answer 2

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

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.

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)

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

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.

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 Fiddle with your questions.

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