·  SQL WHERE

 

- Info:

  - WHERE clause defines subset of the records from a table by defining different conditions those records should fulfill.

  - It is used in SELECT, INSERT and UPDATE statements.

  - All conditons that compare NULL value result in NULL. (6-17)

 

- Comparison Operators:

  - All comparison operators will be demonstrated using SELECT statement.

    SELECT * FROM PEOPLE WHERE  NAME =        'Ivor';              -- equal

    SELECT * FROM PEOPLE WHERE  AGE  >         30;                 -- greater then

    SELECT * FROM PEOPLE WHERE  AGE  <         30;                 -- less then

    SELECT * FROM PEOPLE WHERE  AGE  <=        30;                 -- less then or equal

    SELECT * FROM PEOPLE WHERE  AGE  =>        30;                 -- equal or greater then

    SELECT * FROM PEOPLE WHERE  AGE  <>        30;                 -- different, same as != and ^=

    SELECT * FROM PEOPLE WHERE  AGE  !=        30;                 -- different, same as <> and ^=

    SELECT * FROM PEOPLE WHERE  AGE  ^=        30;                 -- different, same as <> and !=

    SELECT * FROM PEOPLE WHERE  AGE  =  ANY   (10,30);             -- the same as IN

    SELECT * FROM PEOPLE WHERE  AGE  <  ALL   (10,30);             -- less then minimum

    SELECT * FROM PEOPLE WHERE  AGE  >  ALL   (10,30);             -- more then maximum

    SELECT * FROM PEOPLE WHERE  AGE  <> ALL   (10,30);             -- different then all, same as NOT IN

    SELECT * FROM PEOPLE WHERE  AGE  IN       (10,30);             -- the same as =ANY

    SELECT * FROM PEOPLE WHERE  AGE  IS NULL;

    SELECT * FROM PEOPLE WHERE  AGE  BETWEEN  2500 AND 3500;       -- inclusive

    SELECT * FROM PEOPLE WHERE  NAME LIKE     "%ohn_";             -- %=any sequence, _=any char

    SELECT * FROM PEOPLE WHERE  AGE  LIKE     "\%100%" ESCAPE '\'; -- %100,%10078,%1008979    

 

- Logical NOT Operator:

  - All comparison operators can be prefixed with NOT to invert their result. Here are some examples.

    SELECT * FROM PEOPLE WHERE  AGE  NOT IN       (10,30);

    SELECT * FROM PEOPLE WHERE  AGE  NOT BETWEEN  2500 AND 3500;    

    SELECT * FROM PEOPLE WHERE  NAME NOT LIKE     "%ohn_";              

    SELECT * FROM PEOPLE WHERE  AGE  IS NOT NULL;

 

- Logical Operators:

  - Conditions can be combined using logical operators AND, OR and NOT.

    SELECT *

    FROM   PEOPLE

    WHERE  (     (AGE NOT IN (10,30)) AND (NAME LIKE '%vor%'  ) )

    OR     ( NOT ( WEIGHT > 100     ) OR  ( BORN < SYSDATE-50 ) )

 

- Using SELECT statement inside condition:

  - On both sides of condition you can use SELECT statements.

  - It must return only one column which can have one or more values dependin on condition operator used.

    SELECT NAME      

    FROM   PEOPLE

    WHERE  (SELECT MIN(WEIGHT)

            FROM   PEOPLE    ) = WEIGHT  

 

    SELECT NAME      

    FROM   PEOPLE

    WHERE  COUNTRY_ID IN (SELECT COUNTRY_ID

                          FROM   PEOPLE   

                          WHERE  AGE >30 );