Friday, November 23

SQL- Where Clause Part(1)



Where Clause
This clause is used to retrieve data on certain criteria/condition as we need it. We use Where Clause with =,<>, AND, IN, OR, <, > and also for comparing numeric as well as non-numeric columns.

Here use AdventureWorksDW2008 database.




Where =
See the below query and its result

        " select FirstName, LastName, EnglishEducation,  

          EnglishOccupation from DimCustomer where 
          englisheducation  =  'Graduate Degree'  "

Result:

Here you could see 3189 rows instead of 18484 rows, because its satisfies our condition " where englisheducation = 'Graduate Degree' "

Note: The column(s) used in the Where clause do not necessarily have to also be in the Select list of columns.



Where<>

<> stands for not equal to/inequality. See the below query to know how it works.

        " select FirstName, LastName, EnglishEducation, 

          EnglishOccupation from DimCustomer where 
          englisheducation <> 'Graduate Degree'  "

Result:


This show 15295 rows, Of those who don't have a graduate degree.





Where And
Here where clause is used with And operator

        " select FirstName, LastName, EnglishEducation, 

          EnglishOccupation from DimCustomer where     
          englisheducation = 'Partial College'
          and englisheducation = 'High School' "

Result: Error, because the And operator is working on the same column, EnglishEducation, twice.



Try this query out.

        " select FirstName, LastName, EnglishEducation, 

          EnglishOccupation from DimCustomer
          where englisheducation  <> 'Partial College'
          and englisheducation  <> 'High School' "

Result:

Here you could see 10126 rows returned, for who do not have englisheducation as 'High School' and as 'Partial College'.


Where Or
Or operator along with where clause

        " select FirstName, LastName, EnglishEducation, 
          EnglishOccupation from DimCustomer
          where englisheducation = 'Partial College'
          or englisheducation = 'High School' "

This will give an output for which And operator in the earlier query didn't give.

Result:
This returns 8358 rows satisfying the query. You can add more 'or ' operator to satisfy multiple conditions.


Where IN
Instead of using multipe Or operators, We can use IN operator for  working on multiple conditions. See the below query.

       " select FirstName, LastName, EnglishEducation, 
         EnglishOccupation from DimCustomer
         where englisheducation in ('partial college','high 
         school','graduate degree') "

Result:
This returns 11547 rows, Satisfying the condition of  "partial college, high school, graduate degree" in column englisheducation.


Where Not In
Here we use a NOT operator along with IN, See the query below

      " select FirstName, LastName, EnglishEducation, 
        EnglishOccupation from DimCustomer
        where englisheducation not in ('partial college','high 
        school','graduate degree') "





Result:
Here you see 6937 rows returned without partial college, high school, graduate degree from column englisheducation.

**Related topics