FULL OUTER JOIN

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 11 Oct 2004 2:48 pm

I'm doing a select with 2 tables .... sometimes in 1 table has information and 2 not .... and sometimes has 2 and not 1 ... .... I tried to use the FULL OUTER JOIN but isn't working ... maybe I'm doing it the right way. Anyone know how I can do this using the FULL OUTER JOIN?!
LC_JK
Location: São Paulo

Poston Mon, 11 Oct 2004 4:12 pm

If you are using Oracle 8i or earlier, you must use a UNION to emulate a full-outer-join ...

See below-Oracle 8i or earlier:
Code: Select all
SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id (+)
  4  union
  5  select p.part_id, s.supplier_name
  6  from part p, supplier s
  7  where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P3
P4
     Supplier#3
can also use the new feature of Oracle 9i:
Code: Select all
SQL> select p.part_id, s.supplier_name
  2  from part p full outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3
     Supplier#3
this example I took from oreillynet: http://www.oreillynet.com/pub/a/network ... ljoin.html I hope I helped!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 11 Oct 2004 4:56 pm

I'vê done this ... I'm doing equal to q model gave me ... but this giving the following error:
16:59:17 ORA-00933: SQL command not properly ended
q this wrong?!
LC_JK
Location: São Paulo

Poston Mon, 11 Oct 2004 5:03 pm

Send the SQL for us to see! :-o
tfg
Location: Novo Hamburgo - RS

Poston Mon, 11 Oct 2004 5:25 pm

here goes the code. ...

Code: Select all
select a.organization_id,
       a.inventory_item_id,
       a.subinventory_code,
       a.locator_id,
       b.organization_id,
       b.inventory_item_id,
       b.subinventory_code,
       b.locator_id
from nv_una_moviment a FULL OUTER JOIN inv_una_contagens b
   on a.organization_id   = b.organization_id
  and a.inventory_item_id = b.inventory_item_id
  and a.subinventory_code = b.subinventory
  and a.locator_id        = b.locator_id
  and a.lot_number        = b.lot_number;
LC_JK
Location: São Paulo

Poston Wed, 13 Oct 2004 11:24 am

That's weird ... did a similar test here with Oracle 9.0.1.4.0 and worked perfectly.

What is the version of its oracle?

Tries to enter the SCOTT and run this sql below.
Code: Select all
SQL> SELECT A.ENAME, A.DEPTNO, B.DEPTNO
  2  FROM EMP A FULL OUTER JOIN DEPT B
  3    ON A.DEPTNO=B.DEPTNO
  4  /

ENAME         DEPTNO    DEPTNO
---------- --------- ---------
SMITH             20        20
ALLEN             30        30
WARD              30        30
JONES             20        20
MARTIN            30        30
BLAKE             30        30
CLARK             10        10
SCOTT             20        20
KING              10        10
TURNER            30        30
ADAMS             20        20
JAMES             30        30
FORD              20        20
MILLER            10        10
                            40

15 rows selected.

SQL>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 13 Mar 2008 8:11 am

dr_gori, this tip was very good, I'vê eliminated several union in my queries. In this way the code is much cleaner and more maintainable.

Thank you very much.
jobson
Location: São Paulo

Job

Poston Wed, 07 May 2008 4:23 pm

Look, I built with full outer join and all right (for two tables), but I have a few queries that I need in a single sql use the full outer join, pore for 4 tables.

Can you help me?
Mauli
mauli
Location: Joinville


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 2 guests