Home » SQL & PL/SQL » SQL & PL/SQL » What is this technique called????
What is this technique called???? [message #350310] Wed, 24 September 2008 12:58 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
What is the below technique called?

SELECT
   QUERY1.*
   QUERY2.*
   QUERY3.*
FROM
   (SELECT
      *
    FROM
      table1) query1,
 
   (SELECT
      *
   FROM
      table2) query2,
   
   (SELECT
      *
   FROM
      table3) query3
WHERE
   query1.number = query2.number(+) AND
   query1.number = query2.number(+)


Thanks
Stan

[Updated on: Wed, 24 September 2008 13:00]

Report message to a moderator

Re: What is this technique called???? [message #350311 is a reply to message #350310] Wed, 24 September 2008 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if this has a name (why should it be named?) but the following may help:
SQL Reference
Chapter 9 SQL Queries and Subqueries

Regards
Michel

Re: What is this technique called???? [message #350314 is a reply to message #350310] Wed, 24 September 2008 13:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
staann56 wrote on Wed, 24 September 2008 18:58
What is the below technique called?



It is called Querying. (I can also think of other less polite terms, but this is a family show).
How about you being a little bit more specific.
Re: What is this technique called???? [message #350324 is a reply to message #350314] Wed, 24 September 2008 14:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
"inline view"?
Re: What is this technique called???? [message #350325 is a reply to message #350310] Wed, 24 September 2008 14:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You should consider working your coding style into the use of the WITH clause:

with
     query1 as (select * from table1)
    ,query2 as (select * from table2)
    ,query3 as (select * from table3)
select query1.*
      ,query2.*
      ,query3.*
from query1
    ,query2
    ,query3
where
   query1.number = query2.number(+) AND
   query1.number = query3.number(+)
/


It may not see at all important in this whimpy sample, but when you start to get sophisticated pieces of sql in your query, the WITH clause is an amazing construct for simplification and exposing the building process of your query.

Also, just in case, there is a mistake in your original query joins.

Good luck, Kevin
Re: What is this technique called???? [message #350426 is a reply to message #350325] Thu, 25 September 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You should consider working your coding style into the use of the WITH clause:

What is this technique called???? Razz

Regards
Michel
Re: What is this technique called???? [message #350446 is a reply to message #350310] Thu, 25 September 2008 01:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It is called Withstanding Technology Cool

Thumbs Up
Rajuvan.
Re: What is this technique called???? [message #350469 is a reply to message #350310] Thu, 25 September 2008 03:09 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Actually it is known as 'Subquery factoring' !!! I didn't know this term till this time Confused .


Subquery factoring

Thumbs Up
Rajuvan.

Re: What is this technique called???? [message #350481 is a reply to message #350310] Thu, 25 September 2008 03:35 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Probably you mean outer join.

Bye Alessandro
Re: What is this technique called???? [message #350527 is a reply to message #350310] Thu, 25 September 2008 05:17 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I mean ,

with
     query1 as (select * from table1)
    ,query2 as (select * from table2)
    ,query3 as (select * from table3)


As 'Subquery factoring'

Thumbs Up
Rajuavu
Previous Topic: reg:trigger requirement
Next Topic: SP : expression can't be used as assignment target
Goto Forum:
  


Current Time: Sat Dec 03 18:22:24 CST 2016

Total time taken to generate the page: 0.12067 seconds