CODEWITHSUNDEEP
sqloracle-databaseouter-join
Dev Ngron
Dev Ngron

Reputation: 135

Oracle RIGHT OUTER JOIN not working properly?

Ok, so lets say I have two tables: Table1[Rows: id, num, name], Table2[Rows: num, description]. I would like to query Table1 to result the id's for all rows on which Table1.num = Table2.num. So I writte:

SLECT t1.id, t1.num, t2.description
 FROM Table1 t1 RIGHT OUTER JOIN
      Table2 t2 ON (t1.num = t2.num)

/Table2 has only one record which is as follows: Num = 123, Description = 'Abc'/

So my result should be something like this: 1, 123, Abc 2, 123, Abc 5, 123, Abc

But the result I'm getting is; 3, 111, Null 4, 222, Null

Upvotes: 0

Views: 1694

Answers (4)

Dev Ngron
Dev Ngron

Reputation: 135

A SELECT DISTINCT resolved my issue. Thanks to all of you for your help!

Upvotes: 0

HLGEM
HLGEM

Reputation: 96542

When you do a join, you get the number of records from both tables that meet the criteria. If table A only has one record then table B likely has more than one record that meets the join criteria.

So it is unreasonable to expect only one record as a result. If you should, by business rules, have only one record then you need to filter the records in the second table so that only one will result. Which one you pick to return would entirely be dependant on the business rules and is a matter of data meaning not syntax.

Upvotes: 0

Jiri Tousek
Jiri Tousek

Reputation: 12440

With RIGHT OUTER JOIN, there will be at least as many rows as there are rows in the right table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want all rows in a along with matching rows in b, then use LEFT JOIN:

  SELECT count(a.object_id)
  FROM table1 a LEFT OUTER JOIN
       table2 b
       ON a.num = b.num /*I only have one record in table b, which num field = '123'*/

The LEFT JOIN keeps all rows in the first table, regardless of whether the ON clause evaluates to true. The RIGHT JOIN keeps all rows in the second table, regardless of whether the ON clause evaluates to true. You seem to want a LEFT JOIN.

As a general rule, I do not use RIGHT JOIN. I find it much easier to read FROM clauses with the logic "Keep all the rows in the tables I've seen so far".

Upvotes: 2

Related Questions