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