Monday, November 26

SQL- Where Clause Part(2)



Where And Or
Where clause using AND operator and OR operator together. And operator takes precedence over the Or operator. See the below queries and its results.

1)    " select FirstName, LastName, EnglishEducation, 

        EnglishOccupation from dimcustomer
        where englisheducation = 'Partial College'
        and englishoccupation = 'Clerical' or 
        englishoccupation = 'Manual' "

2)   " select FirstName, LastName, EnglishEducation, 

       EnglishOccupation from dimcustomer
       where (englisheducation = 'Partial College'
       and englishoccupation = 'Clerical'or 
       englishoccupation = 'Manual' "

Result:

You could see that both queries returns the same result and Clerical is only with Partial College. This is because And operator takes precedence over the Or operator. Here both queries means the same, Only parentheses shows the precedence of the And operator over the Or operator. Now see the below query, Where the parentheses are placed at different position.


       " select FirstName, LastName, EnglishEducation,   
         EnglishOccupation from dimcustomer
         where englisheducation = 'Partial College'
         and (englishoccupation = 'Clerical' or 
         englishoccupation = 'Manual' )"


Result:
Here you could see that only 'Partial College' with englishoccupation as 'Clerical' or 'Manual' is returned.

Note: Same Where Clause conditions can be applied on numeric Columns.





Wildcards (using Like And Not Like)
SQL wildcards can be used for searching data in a database.


  •    % A substitute for zero or more characters
  •    _  A substitute for exactly one character
  • [charlist] Any single character in charlist
  • [^charlist] / [!charlist] Any single character not in charlist

See the below Queries and its results.

      " select FirstName from DimCustomer where  FirstName like 'C%' "

Result:



 You could see First_name starting with 'C' is returned. Same result can be obtained by using String functions, See the query below.

" select FirstName from DimCustomer where  LeftFirstName,1)='C' "


Now see the rest queries


    1)    " select FirstName from DimCustomer where  FirstName 

            like '%C%' "

    2)    " select FirstName from DimCustomer where  FirstName 
            like '%C' "

Results:

The First query will return First_name having 'C' either at starting or at end or in the middle. While the second query will return  First_name having 'C' at end.




Not Like

 " select FirstName from DimCustomer where  FirstName not like 'C%' "

Result:

This will display names which doesn't start with 'C '.



See the below query.


       " select FirstName from DimCustomer where  FirstName 

         like 'Mar_' "

Result:

You could see that name starting with mar and any one character at end is returned, Here mary and marc are returned. Now if we want to select the persons with a FirstName that starts with "m" or "c". Then see below query.

       "  select FirstName from DimCustomer where  FirstName 

          like '[mc]%'  "

Result:


Now if we want to select the persons with a FirstName that doesn't starts with "m" or "c". Then see below queries, both gives same result.

       " select FirstName from DimCustomer where  FirstName 
         like '[^mc]%' "
       " select FirstName from DimCustomer where  FirstName 
         like '[!mc]%' "

Result:




**Related topics