Skip to Main Content
  • Questions
  • Self full outer join with additional predicateds

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Boris.

Asked: January 14, 2017 - 11:05 pm UTC

Last updated: January 17, 2017 - 1:17 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,
I was asked to create a query, counting the number of the columns with different names in 2 tables. Say in "Employees" and "Departments" we have DEPARTMENT_ID and MANAGER_ID columns with equal names, and the rest of the columns differs in name, so I shall count them.
I came out with

select a.table_name,a.column_name,b.column_name,b.table_name
from
(select table_name,column_name from dba_tab_columns where table_name='EMPLOYEES') a
full outer join
(select table_name,column_name from dba_tab_columns where table_name='DEPARTMENTS') b
on a.column_name=b.column_name

with the idea to count further the numbet of nulls in a.column_name and b.column_name
This worked fine, however, since I try to avoid subqueries when possible, I created

select a.table_name,a.column_name,b.column_name,b.table_name
from dba_tab_columns a full outer join dba_tab_columns b
on a.column_name=b.column_name
where a.table_name='EMPLOYEES' and b.table_name='DEPARTMENTS'

and the result is

TABLE_NAME COLUMN_NAME COLUMN_NAME TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
EMPLOYEES DEPARTMENT_ID DEPARTMENT_ID DEPARTMENTS
EMPLOYEES MANAGER_ID MANAGER_ID DEPARTMENTS


Well, I do not see any outer join here, it's simply an inner join.


Then, although to my understandig, it shall not change the result, I still decided to move the additional conditions to the join clause

select a.table_name,a.column_name,b.column_name,b.table_name
from dba_tab_columns a full outer join dba_tab_columns b
on a.column_name=b.column_name
and a.table_name='EMPLOYEES' and b.table_name='DEPARTMENTS'

I am not completely sure what I got here. It is a huge result set, containing for sure many values, different than "EMPLOYEES" in the column a.table_name and many values, different than "DEPARTMENS" in b.table_name

Can you explain where I am wrong and what do I miss?

Thanks

and Connor said...

"ON" and "WHERE" are not the same thing.

*ON* defines *how* you will map between one table to the other in the join. So if it's an outer join, it's defining the mapping of whether the target table will return a row or return null.

*WHERE* is about *restricting* or *reducing* the rows that can be returned. Here's a simple example which makes it more self-evident


SQL> create table ta ( a int );

Table created.

SQL> create table tb ( b int );

Table created.

SQL>
SQL> insert into ta values (1);

1 row created.

SQL> insert into ta values (2);

1 row created.

SQL> insert into ta values (3);

1 row created.

SQL> insert into ta values (4);

1 row created.

SQL>
SQL> insert into tb values (3);

1 row created.

SQL> insert into tb values (4);

1 row created.

SQL> insert into tb values (5);

1 row created.

SQL> insert into tb values (6);

1 row created.

SQL>
SQL> select *
  2  from ta full outer join tb
  3  on ta.a = tb.b;

         A          B
---------- ----------
         3          3
         4          4
                    5
                    6
         1
         2

6 rows selected.

--
--  So the "ON" told us that 3 and 4 could be mapped between the two
--

SQL>
SQL> select *
  2  from ta full outer join tb
  3  on ta.a = tb.b
  4  where ta.a <= 2;

         A          B
---------- ----------
         1
         2

2 rows selected.

--
--  The "WHERE" tells us to only consider TA rows of 1 and 2
--


SQL>
SQL> select *
  2  from ta full outer join tb
  3  on ta.a = tb.b
  4  and ta.a <= 2;

         A          B
---------- ----------
                    3
                    4
                    5
                    6
         4
         3
         1
         2

8 rows selected.

--
--  The same condition as an "ON" doesnt restrict the TA rows, it simply changes 
--  the definition of what a successful 'match' into TB will be
--

SQL>



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Boris Gyurov, January 16, 2017 - 7:11 am UTC

Thanks, tha was useful.
Connor McDonald
January 17, 2017 - 1:17 am UTC

glad we could help