| Full outer join with ZERO rows in Second table [message #660198] |
Sat, 11 February 2017 14:10  |
 |
bluetooth420
Messages: 146
Registered: November 2011
|
Senior Member |
|
|
Hi,
Assuming two tables M and N,
is this possible to get one table M out put even if N table has ZERO row?
create table t1 (f1 varchar2(10), f2 varchar2(20));
insert into t1 values ('sur', 'Mr.');
commit;
select a.* , b.f2
from emp a, t1 b
where b.f1(+)='sur';
results:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO F2
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 Mr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Mr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Mr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Mr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Mr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Mr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Mr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Mr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Mr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Mr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Mr.
7900 JAMES CLERK 7698 03-DEC-81 950 30 Mr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Mr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Mr.
--But i need output of second query
select a.* , nvl(b.f2, 'Dr.')
from emp a, t1 b
where b.f1(+)='abc'
results
no rows selected
Any possibility to get out put of second query.
Thanks
|
|
|
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660200 is a reply to message #660198] |
Sat, 11 February 2017 18:14  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Adding (+) doesn't always mean you have outer join. It takes two to tango  . Your query has no conditions outer joining two tables, so it isn't outer join. Oracle syntax allows writing such a meaningless query - try writing it using ANSI syntax. Anyway:
select a.*,
nvl(b.f2,'Dr.')
from emp a,
t1 b,
(select 'abc' val from dual) c
where b.f1(+)=c.val
/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 Dr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Dr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Dr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Dr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Dr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Dr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Dr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Dr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Dr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Dr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Dr.
7900 JAMES CLERK 7698 03-DEC-81 950 30 Dr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Dr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Dr.
14 rows selected.
SQL>
SY.
[Updated on: Sat, 11 February 2017 18:18] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660211 is a reply to message #660207] |
Sun, 12 February 2017 08:25 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Not sure what you mean. I showed OP's query is meaningless the way it is written. Oracle syntax recognizes outer join only if there is at least one outer join (+) condition against columns of two tables. That's why we are forced creating subquery from the literal. ANSI syntax is more descriptive and it is much simpler to implement query returning OP's desired result using ANSI syntax. I just didn't want writing it for OP.
SY.
|
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660212 is a reply to message #660211] |
Sun, 12 February 2017 08:37 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And after that I was going to tell OP there is absolutely no need for outer join to get desired results:
SQL> variable f1 varchar2(10)
SQL> exec :f1 := 'sur'
PL/SQL procedure successfully completed.
SQL> select e.*,
2 case b.f1
3 when :f1 then b.f2
4 else 'Dr.'
5 end title
6 from emp e,
7 t1 b
8 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TITLE
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- -----
7369 SMITH CLERK 7902 17-DEC-80 800 20 Mr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Mr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Mr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Mr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Mr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Mr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Mr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Mr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Mr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Mr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Mr.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TITLE
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- -----
7900 JAMES CLERK 7698 03-DEC-81 950 30 Mr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Mr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Mr.
14 rows selected.
SQL> exec :f1 := 'abc'
PL/SQL procedure successfully completed.
SQL> select e.*,
2 case b.f1
3 when :f1 then b.f2
4 else 'Dr.'
5 end title
6 from emp e,
7 t1 b
8 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TITLE
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- -----
7369 SMITH CLERK 7902 17-DEC-80 800 20 Dr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Dr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Dr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Dr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Dr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Dr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Dr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Dr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Dr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Dr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Dr.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TITLE
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- -----
7900 JAMES CLERK 7698 03-DEC-81 950 30 Dr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Dr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Dr.
14 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660217 is a reply to message #660211] |
Sun, 12 February 2017 09:58 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Solomon Yakobson wrote on Sun, 12 February 2017 15:25Not sure what you mean. I showed OP's query is meaningless the way it is written. Oracle syntax recognizes outer join only if there is at least one outer join (+) condition against columns of two tables. That's why we are forced creating subquery from the literal. ANSI syntax is more descriptive and it is much simpler to implement query returning OP's desired result using ANSI syntax. I just didn't want writing it for OP.
SY.
I just repeated the key points of your post OP has to follow. I understood what you wanted to do and this is why I didn't either write the query just giving him a hint to think.
It was an answer to OP not to your post.
[Updated on: Sun, 12 February 2017 10:00] Report message to a moderator |
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660218 is a reply to message #660214] |
Sun, 12 February 2017 10:01 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
bluetooth420 wrote on Sun, 12 February 2017 16:32Respected SY,
Your query works fine as you have mentioned but it does not work when there is no record in t1;
delete from t1 where f1='sur';
I still need output from emp table even there is no record present in t1 table with f1='sur'. In that case i need Dr. as output.
(Practically, for your understanding, i am adding surname before every name of emp by reading from t1 table. Sur name will be present at f1='sur'. In case if the record is missing in t1 table with f1='sur', i want to show Dr. as surname).
We understood what you want, did you try to understand what we posted?
Michel Cadot wrote on Sun, 12 February 2017 14:44
Solomon Yakobson wrote on Sun, 12 February 2017 01:14...Oracle syntax allows writing such a meaningless query - try writing it using ANSI syntax.
...
|
|
|
|
| Re: Full outer join with ZERO rows in Second table [message #660221 is a reply to message #660214] |
Sun, 12 February 2017 10:12 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
bluetooth420 wrote on Sun, 12 February 2017 10:32Respected SY,
Your query works fine as you have mentioned but it does not work when there is no record in t1;
still need output from emp table even there is no record present in t1 table with f1='sur'. In that case i need Dr. as output.
Which part of my reply you didn't understand? Your query isn't outer join query. Outer join query using Oracle native syntax requires outer join conditions between two table columns. column(+) = literal doesn't constitute outer join. You need to change that condition to table1.column(+) = table2.column:
select a.*,
nvl(b.f2,'Dr.')
from emp a,
t1 b,
(select 'abc' val from dual) c
where b.f1(+)=c.val
/
I already provided that query output. Or use ANSI syntax:
SQL> select a.*,
2 nvl(b.f2,'Dr.')
3 from emp a
4 left join
5 t1 b
6 on b.f1 = 'abc'
7 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 Dr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Dr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Dr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Dr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Dr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Dr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Dr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Dr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Dr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Dr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Dr.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7900 JAMES CLERK 7698 03-DEC-81 950 30 Dr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Dr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Dr.
14 rows selected.
SQL>
But, as I also showed, there is no need for outer join - simple CASE will do. However if table t1 is truly empty (has no rows) CASE will not work. Then you do need outer join:
SQL> delete t1;
1 row deleted.
SQL> select a.*,
2 nvl(b.f2,'Dr.')
3 from emp a
4 left join
5 t1 b
6 on b.f1 = 'abc'
7 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 Dr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Dr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Dr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Dr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Dr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Dr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Dr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Dr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Dr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Dr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Dr.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7900 JAMES CLERK 7698 03-DEC-81 950 30 Dr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Dr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Dr.
14 rows selected.
SQL> select a.*,
2 nvl(b.f2,'Dr.')
3 from emp a,
4 t1 b,
5 (select 'abc' val from dual) c
6 where b.f1(+)=c.val
7 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 Dr.
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 Dr.
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 Dr.
7566 JONES MANAGER 7839 02-APR-81 2975 20 Dr.
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Dr.
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 Dr.
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 Dr.
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 Dr.
7839 KING PRESIDENT 17-NOV-81 5000 10 Dr.
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 Dr.
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 Dr.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(B.F2,'DR.')
---------- ------------------------------ --------- ---------- --------- ---------- ---------- ---------- ---------------
7900 JAMES CLERK 7698 03-DEC-81 950 30 Dr.
7902 FORD ANALYST 7566 03-DEC-81 3000 20 Dr.
7934 MILLER CLERK 7782 23-JAN-82 1300 10 Dr.
14 rows selected.
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
