4

I have a complex non ansi query and I need to convert in ansi query.

so I will take a small example to describe my problem

input query

 SELECT a.name,
         a.empno,
         b.loc,
         c.inr
    FROM a,
         b,
         c
   WHERE a.deptno = b.deptno(+)
     AND b.empno(+) = 190
     a.deptno = c.deptno(+)
     AND c.empno(+) = 190;

Thanks in advance

Kishore
  • 5,761
  • 5
  • 28
  • 53

2 Answers2

4

Your query is equivalent to the below ANSI compliant query:

SELECT a.name,
       a.empno,
       b.loc,
       c.inr
FROM tab a
LEFT JOIN tab b ON a.deptno = b.deptno AND b.empno = 190
LEFT JOIN tab c ON a.deptno = c.deptno AND c.empno = 190;

You have to place predicates b.empno = 190 and c.empno = 190 inside the ON clauses of the LEFT JOIN operations, otherwise LEFT JOIN becomes an INNER JOIN.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • but a.deptno = b.deptno(+) it means there should be left outer join – Kishore Nov 24 '16 at 09:53
  • don't mind, I am refering this link where it shows it should be left http://stackoverflow.com/questions/19905028/what-is-difference-between-ansi-and-non-ansi-joins-and-which-do-you-recommend – Kishore Nov 24 '16 at 09:55
  • @KishoreKumarSuthar OK, I didn't know about this. Please check the edit I made. – Giorgos Betsos Nov 24 '16 at 09:55
  • @KishoreKumarSuthar actually, your original query is really using inner joins not outer joins, due to the presence of `b.empno = 190` and `c.empno = 190` (instead of `b.empno(+) = 190` and `b.empno(+) = 190`. Please update your question to include sample data from your table(s) and the result you're expecting to see. – Boneist Nov 24 '16 at 10:19
4

(N.B. This isn't an answer, as it looks like Giorgos's answer is what you were really after, but it's too long to go in a comment. I wanted to make sure you understood why your original query isn't doing what (I think) you think it is)

Your original query actually ends up doing an inner join, because you're asking for specific values for the b.empno and c.empno columns without including them in the left outer join. I.e. you're saying "first, left outer join b to a, and then afterwards, filter out any rows which don't have a b.empno = 190". That will get rid of any rows where b.empno is null as well as other b.empno values.

Here's a simple example showing the different behaviours:

Old-style left outer join with non-left outer join filter:

WITH t1 AS (SELECT 1 ID, 10 val FROM dual UNION ALL
            SELECT 2 ID, 20 val FROM dual UNION ALL
            SELECT 3 ID, 30 val FROM dual),
     t2 AS (SELECT 1 ID, 100 val FROM dual UNION ALL
            SELECT 1 ID, 150 val FROM dual UNION ALL
            SELECT 2 ID, 200 val FROM dual UNION ALL
            SELECT 2 ID, 250 val FROM dual)
SELECT t1.id, t1.val, t2.val
FROM   t1,
       t2
WHERE  t1.id = t2.id(+)
AND    t2.val = 200
ORDER BY t1.id, t2.val;

        ID        VAL        VAL
---------- ---------- ----------
         2         20        200

Old-style inner join with filter:

WITH t1 AS (SELECT 1 ID, 10 val FROM dual UNION ALL
            SELECT 2 ID, 20 val FROM dual UNION ALL
            SELECT 3 ID, 30 val FROM dual),
     t2 AS (SELECT 1 ID, 100 val FROM dual UNION ALL
            SELECT 1 ID, 150 val FROM dual UNION ALL
            SELECT 2 ID, 200 val FROM dual UNION ALL
            SELECT 2 ID, 250 val FROM dual)
SELECT t1.id, t1.val, t2.val
FROM   t1,
       t2
WHERE  t1.id = t2.id
AND    t2.val = 200
ORDER BY t1.id, t2.val;

        ID        VAL        VAL
---------- ---------- ----------
         2         20        200

You can see that the results of the above two queries are identical, which means the first query with the left outer joins is really doing an inner join. And the ANSI join syntax equivalent query to this would be:

ANSI-style inner join with filter:

WITH t1 AS (SELECT 1 ID, 10 val FROM dual UNION ALL
            SELECT 2 ID, 20 val FROM dual UNION ALL
            SELECT 3 ID, 30 val FROM dual),
     t2 AS (SELECT 1 ID, 100 val FROM dual UNION ALL
            SELECT 1 ID, 150 val FROM dual UNION ALL
            SELECT 2 ID, 200 val FROM dual UNION ALL
            SELECT 2 ID, 250 val FROM dual)
SELECT t1.id, t1.val, t2.val
FROM   t1
       INNER JOIN t2 ON t1.id = t2.id
WHERE  t2.val = 200;

        ID        VAL        VAL
---------- ---------- ----------
         2         20        200

You can see the difference to the results by including the filtered column in the outer join in the original old-style query:

Old-style left outer join with left outer join filter

WITH t1 AS (SELECT 1 ID, 10 val FROM dual UNION ALL
            SELECT 2 ID, 20 val FROM dual UNION ALL
            SELECT 3 ID, 30 val FROM dual),
     t2 AS (SELECT 1 ID, 100 val FROM dual UNION ALL
            SELECT 1 ID, 150 val FROM dual UNION ALL
            SELECT 2 ID, 200 val FROM dual UNION ALL
            SELECT 2 ID, 250 val FROM dual)
SELECT t1.id, t1.val, t2.val
FROM   t1,
       t2
WHERE  t1.id = t2.id(+)
AND    t2.val(+) = 200
ORDER BY t1.id, t2.val;

        ID        VAL        VAL
---------- ---------- ----------
         1         10 
         2         20        200
         3         30

And, as per Giorgos's answer, the equivalent ANSI join syntax query would be:

ANSI-style outer join with outer join filter:

WITH t1 AS (SELECT 1 ID, 10 val FROM dual UNION ALL
            SELECT 2 ID, 20 val FROM dual UNION ALL
            SELECT 3 ID, 30 val FROM dual),
     t2 AS (SELECT 1 ID, 100 val FROM dual UNION ALL
            SELECT 1 ID, 150 val FROM dual UNION ALL
            SELECT 2 ID, 200 val FROM dual UNION ALL
            SELECT 2 ID, 250 val FROM dual)
SELECT t1.id, t1.val, t2.val
FROM   t1
       left OUTER JOIN t2 ON t1.id = t2.id AND t2.val = 200
ORDER BY t1.id, t2.val;

        ID        VAL        VAL
---------- ---------- ----------
         1         10 
         2         20        200
         3         30
Boneist
  • 22,910
  • 1
  • 25
  • 40
  • can you please make the final query for given input query. I am little bit confused by seeing many answers. – Kishore Nov 24 '16 at 12:04
  • I've given you examples that you can run yourself to see what the differences between the different joins are. You haven't given us any sample data or expected output to go on, so it's difficult for us to say for sure what it is you want your query to do. My answer is not meant as a direct answer to your question, it's meant to give you some information so that you can learn something and hopefully apply it to your own situation yourself. – Boneist Nov 24 '16 at 12:13
  • I have taken just a example. I have complex query query.I have edited the query. Is the 1) is correct for the given input query? – Kishore Nov 24 '16 at 12:25
  • can you convert your Old-style left outer join with left outer join filter example to ansi style. – Kishore Nov 24 '16 at 12:29
  • You mean the 4th example query? If so, the ANSI version is the last example. – Boneist Nov 24 '16 at 12:31