[INFO] Working subquery types

[Ivan Smahin]

Hello All,

//***********************************************************************
// SUBQUERIES in where clause
//

  1.
    // ==, <, > <=, >=, <>

    Example:
    select
          Name
    from Person
    where personID =( select personID from Students where st_level = 3)

  2.
    // between( subquery and subquery )

    Example:
    select
          personID
    from Person
    where
         personID
                 between
                        ( select personID from Students where st_level = 5)
                 and
                        ( select personID from Students where st_level = 5)
  3.
    // between( subquery and const )

    Example:
    select
          personID
    from Person
    where
         personID
                 between
                        ( select personID from Students where st_level = 5)
                 and
                         ( 1 )

  4.
    // between( const and subquery )

    Example:
    select
          personID
    from Person
    where
         personID
                 between
                         1
                 and
                         ( select personID from Students where st_level = 5)

  5.
    // between( (select const) and subquery )

    Example:
    select
          personID
    from Person
    where
         personID
                 between
                        (select 1 )
                 and
                        ( select personID from Students where st_level = 5)

  6.
    // in (subquery)

    Example:
    select
          Name
    from Person
    where
         personID in ( select personID from Students where st_level = 3 )


//***********************************************************************
// SUBQUERIES in from clause
//
// ATTENTION:
//           This is absolutely legal following standard, but it does not work
//           on MS-SQL.
//

  7.
    // select ... from (subquery)

    Example:
    select PERSONID from (select * from Students)
  
  8.
    // select ... from (subquery) where ...

    Example:
    select PERSONID from (select * from Students) where personID = 2


-- 
Best regards,
 Ivan                          mailto:IvanSmahin at public.kherson.ua

_______________________________________________
Vdevelopers mailing list
Vdevelopers at paradigma.ukrcom.kherson.ua
http://192.168.2.1/mailman/listinfo/vdevelopers