CODEWITHSUNDEEP
sqloracle-database
Rene
Rene

Reputation: 10541

SQL ANSI joins and the order of tables in it

The following query is automatically translated from the "old" syntax to ANSI syntax and gives an error:

select *    
  from ods_trf_pnb_stuf_lijst_adrsrt2 lst    
  join ods_stg_pnb_stuf_pers_adr pas    
    on (pas.soort_adres = lst.soort_adres)    
 right outer join ods_stg_pnb_stuf_pers_nat nat    
    on (prs.id = nat.prs_id)                     <<<prs.id invalid identifier
      join ods_stg_pnb_stuf_adr adr
        on (adr.id = pas.adr_id)
      join ods_stg_pnb_stuf_np prs
        on (prs.id = pas.prs_id)

I guess this is because table prs is referenced before it has been declared. Moving the prs join up in the query solves the problem:

select *
  from ods_trf_pnb_stuf_lijst_adrsrt2 lst
  join ods_stg_pnb_stuf_pers_adr pas
    on (pas.soort_adres = lst.soort_adres)
  join ods_stg_pnb_stuf_np prs               <<< this first
    on (prs.id = pas.prs_id)
 right outer join ods_stg_pnb_stuf_pers_nat nat
    on (prs.id = nat.prs_id)                 <<< now prs.id is known
  join ods_stg_pnb_stuf_adr adr
    on (adr.id = pas.adr_id)
 where lst.persoonssoort = 'PERSOON'
   and pas.einddatumrelatie is null

Is there a way to write this query so that the order is less restrictive, still using the ANSI syntax?

Upvotes: 1

Views: 8726

Answers (4)

James Wang
James Wang

Reputation: 483

I think the original SQL code should be something looks like this, 

select *
from  ods_trf_pnb_stuf_lijst_adrsrt2 lst
    , ods_stg_pnb_stuf_pers_adr pas
    , ods_stg_pnb_stuf_pers_nat nat
    , ods_stg_pnb_stuf_adr adr
    , ods_stg_pnb_stuf_np prs
where 
    pas.soort_adres = lst.soort_adres
and prs.id(+) = nat.prs_id
and adr.id = pas.adr_id
and prs.id = pas.prs_id
and lst.persoonssoort = 'PERSOON'
and pas.einddatumrelatie is null

ods_stg_pnb_stuf_np prs is at the end of from clause which is valid in Oracle proprietary joins, But when convert this to ANSI SQL syntax, table prs should be joined first before it was referenced. This is a common mistake that people made when convert Oracle proprietary joins to ANSI SQL syntax.

There are some other issues when convert Oracle proprietary joins to ANSI SQL syntax:

  1. additional join condition was missing.
  2. condition in where clause was broken after moving some conditions to join clause.

If your colleague need to rewrite Oracle proprietary joins to ANSI SQL syntax, demos(both in java and C#) listed in this article should be helpful.

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74297

If the broken query was generated by a tool from the old non-ANSI syntax, the tools is generated broken code. However, using ANSI-style joins should yield the same result regardless of the order of tables in the from clause. That is

select *
from      t1
join      t2 on t2.id = t1.id
left join t3 on t3.id = t1.id

will give you the same results (albeit a different ordering of columns in the result set) as

select *
from      t1
left join t3 on t3.id = t1.id
join      t2 on t2.id = t1.id

Note that the from clause can't be reordered in such a way as to break the dependencies implied by the join criteria. However, you may also, restate/refactor the from clause so as to express the query in a different way that will yield the same result set. For instance, the above query is equivalent to

select *
from       t3
right join t1 on t1.id = t3.id
join       t2 on t2.id = t1.id

Upvotes: 3

HLGEM
HLGEM

Reputation: 96590

You simply cannot reference a table unless it has been in the join list earlier. That is normal and expected behavior. Why is this a problem?

Upvotes: 3

wildplasser
wildplasser

Reputation: 44250

A normal ("INNER") JOIN 

 SELECT ...
 FROM a
 JOIN b ON (a.x = b.y)

is equivalent to a SELECT with two tables and an appropiate WHERE clause

SELECT ...
FROM a, b
WHERE a.x = b.y

For left/right/outer joins, you are still handicapped by "the asymmetric" join syntax.

Upvotes: 0

Related Questions