0

I have a query that is using an explicit JOIN in Oracle, but I would like to transform it to a basic select where.

Query is : 

Select 
    bla bla bla 
from 
    aMeterilisedView c1
join 
    aMeterilisedView c2 on c2.id > c1.id and c2.idclient = c1.idclient
join 
    (select c1.id, AVG(c) as moy 
     from aMeterilisedView c1 
     group by c1.id) m1 on c1.id = m1.id
join 
    (select c1.id, AVG(c) as moy 
     from aMeterilisedView c1 
     group by c1.id) m2 on c2.id = m2.id

But for learning purpose I would like to use something similar to 

Select bla bla bla 
        from aMeterilisedView  c1
where ....


Union (select etc...

The idea is I don't want to use the "join on" even thought it's much better, but I would like to understand the old basic step by step select where process.

napi15
  • 2,354
  • 2
  • 31
  • 55
  • `UNION` has distinctly different purpose from `JOIN` hence, it is not a replacement. `I would like to understand the old basic step by step select where process` - this doesn't name any sense because union is **old basic**. I am sure what you're asking for is already explained in Oracle documentation – T.S. Dec 11 '16 at 00:56
  • @T.S. it's probably not a UNION , and that's why I'm asking because after reading the documentation I understood the purpose Inner, left outer ,right outer join ....however I didn't find anything for a "joint on" ... And that's the reason I'm not able to do it with a basic select where – napi15 Dec 11 '16 at 01:22
  • inner, outer - they **are** what you call "join on". `... Join ... on ...` is just a syntax – T.S. Dec 11 '16 at 01:38
  • 1
    It is not clear from what you posted so far WHY you want to rewrite a query that uses the SQL Standard syntax for joins. The alternative is the old, proprietary and deprecated Oracle syntax (which has nothing to do with "basic" - it's neither more nor less basic than the modern, standard syntax). Oracle itself strongly encourages all users to write queries using the standard syntax, not its own old, proprietary and deprecated syntax. With that said: the old version of `from a inner join b on a.id = b.id` is simply `from a, b where a.id = b.id`. Neither is simpler or "more basic". –  Dec 11 '16 at 03:11
  • @mathguy What you mentioned is EXACTLY why I want . because it's an old proprietary and deprecated Oracle syntax :) .... As I mentioned is for experiment purpose , but A simple question , when A user write : Join on without writing anything next to it , what type of join is it ? – napi15 Dec 11 '16 at 03:17
  • 2
    It may depend on the database product. Since you mention Oracle, let's talk about Oracle (the only product I know anything about). In Oracle, "join" means "inner join". "left join" is short-hand for "left outer join". You can find all of this in Oracle documentation. Regarding "new" and "old" syntax for outer joins: `from a left outer join b on a.id = b.id` is the preferred, new syntax. Oracle's version is `from a, b where a.id = b.id(+)`. That is, add a plus sign in parentheses on the side where you would have to "add more rows" to match the rows in `a` that don't have a match in inner join. –  Dec 11 '16 at 03:32
  • In Oracle (and I assume others as well), you often have "options" (to write things in more than one way). You may write `join` or `inner join`, both are correct in Oracle. Same with `left join` and `left outer join`. No option on the `on` part - that's written in just one way, for all types of join. For the old syntax, there is no `join` keyword of any kind, you just separate the tables with a comma. The condition is in the WHERE clause, and you use the `(+)` notation to show outer join. –  Dec 11 '16 at 03:35
  • @mathguy your comment explains a lot of things , I'm actually using sqlDevelopper , somehow the syntax is really not 100% the sql I'm used on .... the oracle documentation is too old and that what made me lose my mind and come here ask for silly questions again thanks for your info – napi15 Dec 11 '16 at 03:36
  • 2
    I would add the older syntax it is commonly referred to as implicit join syntax, vs the explicit join syntax (JOIN ON, LEFT JOIN ON, etc)... Implicit join is definitely not preferred yet you will still see it on SO anyway, here is one question on the subject that has more topics in it. http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins My desire not to use implicit join is it is to easy to get a Cartesian join (cross join) because that is technically what the comma does it is a cross join between 2 tables that is then only limited by the where clause – Matt Dec 11 '16 at 03:52
  • Why even bother? All standard SQL conforms to sql standards. There is really no reason deal with this. Because independently of, if it is standard sql `(join..on)` or `(from ..., ... where ...)` the execution plan is the same and that is the bottom line! So Oracle interprets `(+)` as outer join. This is just language parsing. All you need to do is to open Oracle 8 or lower documentation and it will be written all over how to do it. – T.S. Dec 11 '16 at 21:54
  • @T.S. Because a classmate copied the same querry as me and he used jointure and to avoid problems , I need to reverse engineer it – napi15 Dec 11 '16 at 21:56
  • @T.S. "All standard SQL conforms to sql standards" -- you just blew my mind :) – onedaywhen Dec 12 '16 at 08:59

0 Answers0