Chapter 3 – The WHERE Clause
“I saw the angel in the marble and carved until I set him free.”
- Michelangelo
The WHERE Clause limits Returning Rows
The WHERE Clause here filters how many ROWS are coming back. In this example, I am asking for the report to only include rows WHERE the first name is Henry.
Double Quoted Aliases are for Reserved Words and Spaces
“Write a wise saying and your name will live forever.”
- Anonymous
When you ALIAS a column, you give it a new name for the report header, but a good rule of thumb is to refer to the column by the alias throughout the query. Whoever wrote the above quote was way off. "Write a wise alias and it will live until the query ends – bummer".
Using A Column ALIAS In A WHERE Clause
error
When you ALIAS a column, you give it a new name. An ALIAS created in a SELECT clause does not work in a WHERE clause.
Using A Column ALIAS In An ORDER BY Clause
perfect
When you ALIAS a column, you give it a new name. An ALIAS created in a SELECT clause works in an ORDER BY clause.
In What Order Does SQL Server Process A Query?
Nothing, even an alias, does not exist until it is created. This explains why ALIASES which are created in a SELECT clause can only be used again in an ORDER BY clause.
Character Data needs Single Quotes in the WHERE Clause
In the WHERE clause, if you search for Character data such as first name, you need single quotes around it. You don’t single-quote integers.
Character Data needs Single Quotes, but Numbers Don’t
Character data (letters) need single quotes, but you need NO Single Quotes for Integers (numbers). Remember, you never use double quotes except for aliasing.
Declaring a Variable
You can declare a variable and then use it in the select. WARNING: You must highlight these two queries together and run as one query. You cannot run the first query and then run the second query or it will error.
Comparisons against a Null Value
The above chart should help on any advanced NULL questions.
NULL means UNKNOWN DATA so Equal (=) won’t Work
First thing you need to know about a NULL is it is unknown data. It is NOT a zero. It is missing data. Since we don’t know what is in a NULL, you can’t use an = sign. You must use IS NULL or IS NOT NULL.
Use IS NULL or IS NOT NULL when dealing with NULLs
SELECT *
FROM Student_Table
WHERE Class_Code IS NULL ;
If you are looking for a row that holds NULL value, you need to put ‘IS NULL’. This will only bring back the rows with a NULL value in it.
NULL is UNKNOWN DATA so NOT Equal won’t Work
The same goes with = NOT NULL. We can’t compare a NULL with any equal sign. We can only deal with NULL values with IS NULL and IS NOT NULL.
Use IS NULL or IS NOT NULL when dealing with NULLs
SELECT *
FROM Student_Table
WHERE Class_Code IS NOT NULL ;
Much like before, when you want to bring back the rows that do not have NULLs in them, you put an ‘IS NOT NULL’ in the WHERE Clause.
Using Greater Than or Equal To (>=)
All rows returned have a Grade_Pt >= 3.0
The WHERE Clause doesn’t just deal with ‘Equals’. You can look for things that are GREATER or LESSER THAN along with asking for things that are GREATER/LESSER THAN or EQUAL to.
AND in the WHERE Clause
Notice the WHERE statement and the word AND. In this example, qualifying rows must have a Class_Code = ‘FR’ and also must have a First_Name of ‘Henry’. Notice how the WHERE and the AND clause are on their own line. Good practice!
Troubleshooting AND
What is going wrong here? You are using an AND to check the same column. What you are basically asking with this syntax is to see the rows that have BOTH a Grade_Pt of 3.0 and a 4.0. That is impossible, so no rows will be returned.
OR in the WHERE Clause
Notice above in the WHERE Clause we use OR. Or allows for either of the parameters to be TRUE in order for the data to qualify and return.
Troubleshooting Or
Notice above in the WHERE Clause we use OR. Or allows for either of the parameters to be TRUE in order for the data to qualify and return. The first example errors and is a common mistake. The second example is perfect.
Troubleshooting Character Data
This query errors! What is WRONG with this syntax? No Single quotes around SR.
Using Different Columns in an AND Statement
SELECT * FROM Student_Table
WHERE Grade_Pt = 3.0
AND Class_Code = 'SR' ;
Notice that AND separates two different columns, and the data will come back if both are TRUE.
Quiz – How many rows will return?
SELECT * FROM Student_Table
WHERE Grade_Pt = 4.0 OR Grade_Pt = 3.0
AND Class_Code = 'SR' ;
Which Seniors have a 3.0 or a 4.0 Grade_Pt average. How many rows will return?
A) 2 |
C) Error |
B) 1 |
D) 3 |
Answer to Quiz – How many rows will return?
SELECT * FROM Student_Table
WHERE Grade_Pt = 4.0 OR Grade_Pt = 3.0
AND Class_Code = 'SR' ;
We had two rows return! Isn’t that a mystery? Why?
What is the Order of Precedence?
|
( ) |
|
NOT |
|
AND |
|
OR |
SELECT *
FROM Student_Table
WHERE Grade_Pt = 4.0 OR Grade_Pt = 3.0
AND Class_Code = 'SR' ;
Syntax has an ORDER OF PRECEDENCE. It will read anything with parentheses around it first. Then, it will read all the NOT statements. Then, the AND statements. FINALLY, the OR Statements. This is why the last query came out odd. Let’s fix it and bring back the right answer set.
Using Parentheses to change the Order of Precedence
This is the proper way of looking for rows that have both a Grade_Pt of 3.0 or 4.0 AND also having a Class_Code of ‘SR’. Only ONE row comes back. Parentheses are evaluated first, so this allows you to direct exactly what you want to work first.
Using an IN List in place of OR
Using an IN List is a great way of looking for rows that have both a Grade_Pt of 3.0 or 4.0 AND also have a Class_Code of ‘SR’. Only ONE row comes back.
The IN List is an Excellent Technique
SELECT * FROM Student_Table
WHERE Grade_Pt IN (2.0, 3.0, 4.0) ;
The IN Statement avoids retyping the same column name separated by an OR. The IN allows you to search the same column for a list of values. Both queries above are equal, but the IN list is a nice way to keep things easy and organized.
IN List vs. OR brings the same Results
The IN Statement avoids retyping the same column name separated by an OR. The IN allows you to search the same column for a list of values. Both queries above are equal, but the IN list is a nice way to keep things easy and organized.
The IN List Can Use Character Data
SELECT * FROM Student_Table
WHERE Last_Name IN ('Larkins', 'Bond') ;
The IN Statement avoids retyping the same column name separated by an OR. The IN allows you to search the same column for a list of values. This works with character data as long as you use single quotes.
Using a NOT IN List
SELECT *
FROM Student_Table
WHERE Grade_Pt NOT IN (2.0, 3.0, 4.0) ;
“First you imitate, then you innovate.”
- Miles Davis
You can also ask to see the results that ARE NOT IN your parameter list. That requires the column name and a NOT IN. Neither the IN nor NOT IN can search for NULLs! Miles Davis got this IT quote all wrong. First you innovate, and then you sue anyone who imitates. Please make a note of it!
Null Values in a NOT IN List Bring Back No Rows
Few people know that when a NOT IN is used and a null value is encountered that no data returns. This is because a null value equals nothing so it can't compare and eliminate values.
A Technique for Handling Nulls with a NOT IN List
This is a great technique to look for a NULL when using a NOT IN List.
BETWEEN is Inclusive
SELECT *
FROM Student_Table
WHERE Grade_Pt BETWEEN 2.0 AND 4.0 ;
This is a BETWEEN. What this allows you to do is see if a column falls in a range. It is inclusive meaning that in our example, we will be getting the rows that also have a 2.0 and 4.0 in their column!
NOT BETWEEN is Also Inclusive
"The difference between genius and stupidity is that genius has its limits."
Albert Einstein
This is a NOT BETWEEN example. What this allows you to do is see if a column does not fall in a range. It is inclusive meaning that in our example, we will be getting no rows where the grade_pt is between a 2.0 and 4.0 in their column! The 2.0 and the 4.0 will also not return.
LIKE command Underscore is Wildcard for one Character
The _ underscore sign is a wildcard for any single character. We are looking for anyone who has an 'a' as the second letter of their last name.
LIKE command using a Range of Values
The above syntax allows us to use a range of values (a-f in this example). Any First_Name that starts with an a, b, c, d, e or f will return. How about that for clever SQL?
LIKE command Using a NOT Range of Values
The above syntax allows us to use a NOT range of values (a-f in this example). Any First_Name that starts with the letter a, b, c, d, e or f will not return.
LIKE Command Works Differently on Char Vs Varchar
It is important that you know the data type of the column you are using with your LIKE command. VARCHAR and CHAR data differ slightly.
Troubleshooting LIKE Command on Character Data
This is a CHAR (20) data type. That means that any words under 20 characters will pad spaces behind them until they reach 20 characters. You will not get any rows back from this example because technically, no row ends in an ‘N’, but instead ends in a space.
Introducing the RTRIM Command
This is a CHAR(20) data type. That means that every Last_Name is going to be 20 characters long. Most names are not really 20 characters long, so spaces are padded at the end to ensure filling up all 20 characters. We need to do the RTRIM command to remove the trailing spaces. Once the spaces are trimmed, we can find out whose name ends in 'n'.
Quiz – What Data is Left Justified and What is Right?
SELECT *
FROM Sample_Table
WHERE Column1 IS NULL
AND Column2 IS NULL ;
Which Column from the Answer Set could have a DATA TYPE of INTEGER, and which could have Character Data?
Numbers are Right Justified and Character Data is Left
SELECT *
FROM Sample_Table
WHERE Column1 IS NULL
AND Column2 IS NULL ;
All Integers will start from the right and move left. Thus, Col1 was defined during the table create statement to hold an INTEGER. The next page shows a clear example.
Answer – What Data is Left Justified and What is Right?
SELECT *
FROM Sample_Table
WHERE Column1 IS NULL
AND Column2 IS NULL ;
“Always remember that you are unique just like everyone else.”
– Anonymous
All Integers will start from the right and move left. All Character data will start from the left and move to the right.
An Example of Data with Left and Right Justification
SELECT |
Student_ID, Last_Name |
FROM |
Student_Table ; |
This is how a standard result set will look. Notice that the integer type in Student_ID starts from the right and goes left. Character data type in Last_Name moves left to right like we are use to seeing while reading English.
A Visual of CHARACTER Data vs. VARCHAR Data
Character data pads spaces to the right and Varchar uses a 1 or 2-byte Variable Length Indicator (VLI) instead.
RTRIM command Removes Trailing spaces on CHAR Data
By using the TRIM command on the Last_Name column, you are able to trim off any spaces from the end. Once we use the TRIM on Last_Name, we have eliminated any spaces at the end, so now we are set to bring back anyone with a Last_Name that truly ends in ‘n’!
Using Like with an AND Clause to Find Multiple Letters
The above uses an additional AND clause to find anyone with both an 'M' and an 'S' in their last name. Notice that SQL Server is not case sensitive.
Using Like with an OR Clause to Find Either Letters
The above uses an additional OR clause to find anyone with either an 'M' and an 'S' in their last name. Notice that SQL Server is not case sensitive.
Declaring a Variable and Using it with the LIKE Command
The equivalent statement would be
Select Street, City, State from Addresses WHERE street like '%Appreciate% ;
In the above example, we have declare a variable and placed a value in it with the word 'Appreciate'. We use it in our LIKE query in conjunction with concatenation. WARNING: The DECLARE and SELECT must be highlighted and run together.
Escape Character in the LIKE Command changes Wildcards
/* We just pretended to add a new row to the Student_Table */
/* Can you use the LIKE command to find S% above? */
Here you will have to utilize a Wildcard Escape Character. Turn the page for more.
Escape Characters Turn off Wildcards in the LIKE Command
Can you use the LIKE command to find S% above?
SELECT *
FROM Student_Table
WHERE First_Name LIKE 'S@%' Escape '@';
We can pick our Escape character and we have chosen the @ sign. This turns the wildcard off for 1 character so we find ‘S%’, without bringing back Stanley or Susie.
Quiz – Turn off that Wildcard
Can you use the LIKE command to find the Last_Name of T_? (pronounced Tunderscore!)
This is a little trickier than you might think so be on your toes. . . . And get a haircut!
ANSWER – To Find that Wildcard
Can you use the LIKE command to find the Last_Name of T_? (pronounced Tunderscore!)
SELECT * FROM Student_Table
WHERE RTRIM(Last_Name) LIKE 'T@_' Escape '@' ;
You didn’t really need to get a full haircut, but just a RTRIM Command and the Escape!