CODEWITHSUNDEEP
oracle-databaseselectfull-outer-join
BellaVita
BellaVita

Reputation: 69

Oracle possible Error in Select with FULL OUTER JOIN

I have to modify a select of oracle that someone do... i need to understand... I think that is a bad select but i'm no sure:

FROM user01.myTable FULL OUTER JOIN user01.myTable myTable_alias2

SELECT      
            myTable.field1 AS field1,
            myTable.date_hour AS date_hour,
            myTable.HOUR,
            DECODE (
                    myTable.aux1, 'CAT', 'CAT3', myTable.aux1
                    ) AS aux1,
            myTable.sum_count,
            myTable_alias2.sum_count AS sum_countx,
            myTable.aux2,
            myTable_alias2.aux2 AS aux2x
   FROM user01.myTable FULL OUTER JOIN user01.myTable myTable_alias2
            ON (myTable.field1 = myTable_alias2.field1
                  AND myTable.HOUR = myTable_alias2.HOUR
                  AND myTable.aux1 = myTable_alias2.aux1
                  AND TO_CHAR (TO_DATE (myTable.date_hour, 'YYYYMMDD') - 7,'YYYYMMDD') = myTable_alias2.date_hour
             )

There is in my system a user01.myTable, but in my system there isn't a myTable_alias2. and if i replace all myTable_alias2 with myTable_aliasXXX the select work correctly!

i don't understand, i think that myTable_alias2 is like a copy of user01.myTable, but if is right i can write all like this:

SELECT         
            myTable.field1 AS field1,
            myTable.date_hour AS date_hour,
            myTable.HOUR,
            DECODE (myTable.aux1, 'CAT01L0', 'CAT01L03', myTable.aux1) AS aux1,
            myTable.sum_count,
        myTable.aux2
FROM user01.myTable

in what i'm wrong? what i don't understand? is a particulary full outer join or is a simple error? ThankYou very much for your Time!

Upvotes: 0

Views: 260

Answers (2)

Justin Cave
Justin Cave

Reputation: 231671

This is not unique to full outer joins. Any time you are joining tables, it's a good idea to alias the table names and to use those aliases to qualify the columns names. If you are doing a self-join where you are joining a table to itself, it is necessary to alias at least one of the two instances of the table.

If I have the standard SCOTT tables EMP and DEPT, for example, I can write the query without aliases

SELECT emp.ename,
       dept.dname
  FROM emp 
       JOIN dept ON (emp.deptno = dept.deptno)

or I write the query with aliases

SELECT e.ename,
       d.dname
  FROM emp e
       JOIN dept d ON (e.deptno = d.deptno)

In the latter case, E and D are not objects in the database. They are simply shorthand for the tables they alias, EMP and DEPT, that are available within the scope of the query.

If I want to join a table to itself-- say, I want a report of employees and their managers, I can write a self join

SELECT employee.ename,
       manager.ename
  FROM emp employee
       JOIN emp manager ON (employee.mgr = manager.empno)

In this case, I'm joining the EMP table to itself and the aliases help identify what set of data is represented by each instance of the EMP table. One instance is providing the information about the employee and one instance is providing the information about the maanger.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

i think that myTable_alias2 is like a copy of user01.myTable

That is correct. The table is not actually copied but the query works as if you had 2 copies of the table and the second copy gets another name (alias), so it is distinguished from the first.

but then, i can write all like this: ...

No, your rewriting is not equivalent to the first (FULL JOIN) version. The Join is not done entirely on the same columns.

This part:

   TO_CHAR (TO_DATE (myTable.date_hour, 'YYYYMMDD') - 7,'YYYYMMDD') 
   = myTable_alias2.date_hour

means that rows from the first copy of the table will be matched to different rows from the second copy.

Upvotes: 1

Related Questions