Home » SQL & PL/SQL » SQL & PL/SQL » Full outer join with ZERO rows in Second table (10.2)
Full outer join with ZERO rows in Second table [message #660198] Sat, 11 February 2017 14:10 Go to next message
bluetooth420
Messages: 139
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 #660199 is a reply to message #660198] Sat, 11 February 2017 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 26195
Registered: January 2009
Location: SoCal
Senior Member
>Assuming two tables M and N,
>is this possible to get one table M out put even if N table has ZERO row?

But neither table M nor table N ever appear in the remainder of the post.

You don't explain or show us any relationship between table M & table N or the relationship between table t1 & table emp.
You don't show us what the expected or desired results should be.
Re: Full outer join with ZERO rows in Second table [message #660200 is a reply to message #660198] Sat, 11 February 2017 18:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Adding (+) doesn't always mean you have outer join. It takes two to tango Cool. 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 #660206 is a reply to message #660199] Sun, 12 February 2017 05:40 Go to previous messageGo to next message
bluetooth420
Messages: 139
Registered: November 2011
Senior Member
BlackSwan wrote on Sun, 12 February 2017 02:00
>Assuming two tables M and N,
>is this possible to get one table M out put even if N table has ZERO row?

But neither table M nor table N ever appear in the remainder of the post.

You don't explain or show us any relationship between table M & table N or the relationship between table t1 & table emp.
You don't show us what the expected or desired results should be.
My mistake.

Before posting, i was searching the solution and i read an article having M and N table. So i started writing under that scenario.


Anyhow, let me represent my requirement.

1) there is no direct link between emp and t1
2) If there is a record in t1 with f1='sur' then value of f2 (Mr. in this case) must be shown with every record of emp
3) Else if f1='sur' is not present then Dr. should be shown.


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.


--Now removing record from t1 with f1='sur'
Delete from t1 where f1='sur';

1 row deleted.

commit;

My requirement is to write a query to show Dr. as there is no record of f1='sur' present .i.e.



    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO F2
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------
      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.

--Thanks
Re: Full outer join with ZERO rows in Second table [message #660207 is a reply to message #660206] Sun, 12 February 2017 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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 #660211 is a reply to message #660207] Sun, 12 February 2017 08:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 #660214 is a reply to message #660212] Sun, 12 February 2017 09:32 Go to previous messageGo to next message
bluetooth420
Messages: 139
Registered: November 2011
Senior Member
Respected 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).
Re: Full outer join with ZERO rows in Second table [message #660217 is a reply to message #660211] Sun, 12 February 2017 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon Yakobson wrote on Sun, 12 February 2017 15:25
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.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bluetooth420 wrote on Sun, 12 February 2017 16:32
Respected 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
bluetooth420 wrote on Sun, 12 February 2017 10:32
Respected 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.
Re: Full outer join with ZERO rows in Second table [message #660223 is a reply to message #660221] Sun, 12 February 2017 13:05 Go to previous message
bluetooth420
Messages: 139
Registered: November 2011
Senior Member
I got it.

Thank you SY and Michel Cadot for your respective precious time.
Previous Topic: Using Oracle UTL_MAIL to send email through Kerio email server
Next Topic: Counting minutes in a date/time range
Goto Forum:
  


Current Time: Fri Oct 19 10:12:14 CDT 2018