Full join on 1=2

Database
Enthusiast

Full join on 1=2

Hi !!

I have a view (definition below) which is further used in queries which run really long. I am trying to figure out wat could be the cause. The first indication is this Full join . When a query uses this view, will it perform the Full join every time? Or since the view is already created, there is not underlying full join every time the query uses this view?

What columns id this join on  "Full join on 1=2" mean? Is there a way to replace the full join with an efficient way? 

REPLACE  VIEW dbname.tablename AS

LOCKING ROW FOR ACCESS

SEL d.1,d.2,d.3

FROM

 (SELECT  

       COALESCE(1),2,3, 

      COALESCE(4)

FROM   

(SELECT  ID  FROM  dbname.table1  WHERE TYPE='FLG2' AND VAL ='ABC') AB,

(SELECT  D FROM  dbname.table1  WHERE TYPE='FLG1' AND VAL ='EFG') EF,

dbname.table2 T1 FULL JOIN dbname.table2 T2  ON 1=2) D;

Thanks for reading!

7 REPLIES
Enthusiast

Re: Full join on 1=2

First, I do not understand why you would need to do FULL JOIN between same tables (dbname.table2). I am sure we can avoid that if we know what is the exact requirement. Anyways, for any reason if you want to do that, then you can JOIN them on PI of this table rather than doing 1=2. And to answer your question, yes, it would do full outer join all the time you use this view in any query. It would always perform all the joins written in the view whenever you use it.  

Senior Apprentice

Re: Full join on 1=2

Without showing the actual view source it's impossible to find out *what* this code is supposed to do.

Dieter

Enthusiast

Re: Full join on 1=2

What does "on 1=2"mean? Does is mean first column of table2 with  second column of table2?

Enthusiast

Re: Full join on 1=2

Dieter, what would you like to know?

Senior Apprentice

Re: Full join on 1=2

"1=2" means a join condition which aways evaluates to false.

For an inner join this results in an empty set, but here it's a combination of both tables similar to a UNION:

t1: colB,colB,colC

t2: colD,colE,colF

result:

colB,colB,colC,null,null,null

null,null,null,colD,colE,colF

The query you posted is not valid syntax, so just show the actual source code.

Dieter

Enthusiast

Re: Full join on 1=2

Dieter! 

This is view!

REPLACE  VIEW Vdbname.View1 AS

LOCKING ROW FOR ACCESS

SEL D.PLN_ID,D.CLT_ID,D.CLT_RET_AGE, D.CO_CLT_RET_AGE, D.CO_CLT_WD, D.CTCT_WD,D.PLNDT_WD,

D.TOT_AST,D.TOT_HLD_AST,D.EST_HLD_AST,D.OPEN_BAL,D.HSHLD_EMP_INC,

D.HSHLD_NET_WORTH,D.LBLTS, D.LFSTL_ASTS, D.IN_AFTR_OUTRCH_BAL,D.OUT_AFTR_OUTRCH_BAL,D.TX_MTH_WD,

CASE WHEN D.TOT_AST IS NULL AND OPEN_BAL IS NULL THEN NULL ELSE D.INDCTR_WD  END INDCTR_WD

FROM

 (SELECT  

       COALESCE(T1.PLN_ID ,T2.PLN_ID ) PLN_ID,                   

       COALESCE(T1.CLT_ID ,T2.CLT_ID ) CLT_ID,  

       COALESCE(T1.CLT_RET_AGE,T2.CLT_RET_AGE) CLT_RET_AGE,  

       COALESCE(T1.CO_CLT_RET_AGE,T2.CO_CLT_RET_AGE) CO_CLT_RET_AGE,  

       COALESCE(T1.CO_CLT_WD,T2.CO_CLT_WD) CO_CLT_WD,

       COALESCE(T1.CTCT_WD, T2.CTCT_WD) CTCT_WD,

       COALESCE(T1.PLNDT_WD, T2.PLNDT_WD) PLNDT_WD,

       COALESCE(T1.QL_AST ,T2.NON_QL_AST ) TOT_AST,

       COALESCE(T1.QL_ASTS_HLD ,T2.NON_QL_TAX_AST_HLD ) TOT_HLD_AST,

       COALESCE(T1.QL_EST_HLD_AST ,T2.NON_QL_EST_HLD_AST) EST_HLD_AST ,

       COALESCE(T1.QL_OPEN_BAL,T2.NON_QL_OPEN_BAL ) OPEN_BAL ,

       T1.HSHLD_EMP_INC, 

       T1.HSHLD_NET_WORTH,

       T1.LBLTS,

       T1.LFSTL_ASTS,

       COALESCE(T1.IN_AFTR_OUTRCH_BAL_QL, T2.IN_AFTR_OUTRCH_BAL_NON_QL) IN_AFTR_OUTRCH_BAL,

      COALESCE(T1.OUT_AFTR_OUTRCH_BAL_QL, T2.OUT_AFTR_OUTRCH_BAL_NON_QL) OUT_AFTR_OUTRCH_BAL, 

      COALESCE(T1.TX_MTH_WD, T2.TX_MTH_WD) TX_MTH_WD,

CASE  

WHEN T1.QL_OPEN_BAL IS NOT NULL THEN EF.ROW_WD

WHEN T1.QL_AST IS NOT NULL THEN EF.ROW_WD

ELSE AB.ROW_WD END INDCTR_WD

FROM   

(SELECT  CM.ROW_WD  FROM  Tdbname.TBL1 CM WHERE CM."TYPE"='NON_QL_FLG' AND CM.VAL ='NON_QLIFIED') AB,

(SELECT  CM.ROW_WD FROM  Tdbname.TBL1 CM WHERE CM."TYPE"='QL_FLG' AND CM.VAL ='QLIFIED') EF,

Tdbname.tbl T1 FULL JOIN Tdbname.tbl T2  ON 1=2) D;

Senior Apprentice

Re: Full join on 1=2

Wow, who wrote that?

Hopefully the AB and EF Derived Tables return only a single value, but then they should be rewritten as a Scalar Subquery (TD13+).

And the FULL JOIN ON 1=2 plus COALESCE emulates a UNION ALL of two tables with different number of columns instead of simply writing that UNION.

Dieter