Chapter 12
The WHERE Clause
“I have a dream that my four little children will one day live in a nation where they will not be judged by the color of their skin, but by the content of their character.
Table of Contents Chapter 12 – The WHERE Clause
– The WHERE Clause limits Returning Rows
– Using a Column ALIAS throughout the SQL
– Double Quoted Aliases are for Reserved Words and Spaces
– Character Data needs Single Quotes in the WHERE Clause
– Character Data needs Single Quotes, but Numbers Don't
– NULL means UNKNOWN DATA so Equal (=) won't Work
– Use IS NULL or IS NOT NULL when dealing with NULLs
– NULL is UNKNOWN DATA so NOT Equal won't Work
– Use IS NULL or IS NOT NULL when dealing with NULLs
– Using Greater Than Or EQUAL To (>=)
– Using GE as Greater Than or Equal To (>=)
– OR must utilize the Column Name Each Time
– Troubleshooting Character Data
– Using Different Columns in an AND Statement
– Quiz – How many rows will return?
– Answer to Quiz – How many rows will return?
– What is the Order of Precedence?
– Using Parenthesis to change the Order of Precedence
– Using an IN List in place of OR
– IN List vs. OR brings the same Results
– A Technique for Handling Nulls with a NOT IN List
– An IN List with the Keyword ANY
– A NOT IN List with the Keywords NOT = ALL
– BETWEEN works for Character Data
– LIKE Command uses Wildcards Percent % and Underscore _
– LIKE command Underscore is Wildcard for one Character
– LIKE ALL means ALL conditions must be Met
– LIKE ANY means ANY of the Conditions can be Met
– IN ANSI Transaction Mode Case Matters
– In Teradata Transaction Mode Case Doesn't Matter
– LIKE Command Works Differently on Char Vs Varchar
– Troubleshooting LIKE Command on Character Data
– Quiz – What Data is Left Justified and What is Right?
– Numbers are Right Justified and Character Data is Left?
– Answer – What Data is Left Justified and What is Right?
– An Example of Data with Left and Right Justification
– A Visual of CHARACTER Data vs. VARCHAR Data
– Use the TRIM command to remove spaces on CHAR Data
– TRIM Eliminates Leading and Trailing Spaces
– Escape Character in the LIKE Command changes Wildcards
– Escape Characters Turn off Wildcards in the LIKE Command
– Quiz – Turn off that Wildcard
– ANSWER – To Find that Wildcard
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 rows WHERE the first name is Henry.
Using a Column ALIAS throughout the SQL
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.
Double Quoted Aliases are for Reserved Words and Spaces
Use the ALIAS in your remaining SQL!
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.
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.
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
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 (>=)
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 and EQUAL to.
Using GE as Greater Than or Equal To (>=)
The syntax above uses a Teradata extension (GE) for Greater 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’.
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
This causes an error! Why? You need to state the column name again before the 4.0.
OR must utilize the Column Name Each Time
Notice that you must always state the COLUMN NAME along with the parameter. Even if you are using the same Column Name, you must specify it over again.
Troubleshooting Character Data
SELECT *
FROM Student_Table
WHERE Grade_Pt = 3.0 AND Class_Code = SR ;
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 OR separates two different columns and the data will come back if either 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?
 |
C) Error |
| B) 1 | D) 3 |
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 parenthesis 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 Parenthesis 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. Parenthesis evaluated first.so this allows you the user 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.
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.
Using a NOT IN List
SELECT *
FROM Student_Table
WHERE Grade_Pt NOT IN (2.0, 3.0, 4.0) ;
You can also ask to see the results that ARE NOT IN your parameter list. That requires the column name and a NOT IN. Both the IN and NOT IN cannot search for NULLs!
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.
An IN List with the Keyword ANY
This is the same thing as using an IN. It's just another way of writing your SQL.
A NOT IN List with the Keywords NOT = ALL
This is an other way of doing a NOT IN. Notice the NOT = ALL and then the 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!
BETWEEN works for Character Data
SELECT *
FROM Student_Table
WHERE Last_Name BETWEEN ‘L’ AND ‘LZ’ ;
The BETWEEN isn't just used with numbers. You can look to see if words falls between certain letters.
LIKE Command uses Wildcards Percent % and Underscore _
SELECT *
FROM Student_Table
WHERE Last_Name LIKE ‘SM%’ ;
The wildcard percentage sign (%) is a wildcard for any number of characters. We are looking for anyone whose name starts with SM! In this example, the only row that would come back is ‘Smith’.
LIKE command Underscore is Wildcard for one Character
The second wild card is an ‘_’ (underscore). An underscore represents a one character wildcard. Our search finds anyone with an ‘a’ in the second letter of their last name.
LIKE ALL means ALL conditions must be Met
SELECT *
FROM Student_Table
WHERE Last_Name LIKE ALL (‘%S%’, ‘%M%’) ;
What this syntax is looking for any row that has a Last_Name with a ‘S’ AND an ‘M’ in it. It isn't looking for these in any order. As long as the Last_Name has a ‘S’ and an ‘M’ somewhere, it'll come back.
LIKE ANY means ANY of the Conditions can be Met
The word ANY means either a ‘S’ OR an ‘M’ in the Last_Name, in any order.
IN ANSI Transaction Mode Case Matters
/* This query is in ANSI Transaction mode */
When in ANSI Transaction Mode, the system is CASE SENSITIVE, but it is not case sensitive in Teradata mode, also referred to as BTET for Begin and End Transaction.
In Teradata Transaction Mode Case Doesn't Matter
/* This query is in Teradata (BT/ET) Transaction mode */
Case Sensitivity in Teradata (BTET) Transaction mode is not an issue.
LIKE Command Works Differently on Char Vs Varchar
/* First_Name has a Data Type of VARCHAR (20) */
SELECT *
FROM Student_Table
WHERE First_Name LIKE ‘%y’ ;
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
/* Last_Name has a Data Type of CHAR (20) */
SELECT *
FROM Student_Table
WHERE Last_Name LIKE ‘%n’ ;
This is a CHAR(20) data type. That means that any words under 20 characters will pad spaces behind them until it reaches 20 characters. You will not get any rows back from this because technically, no row ends in a ‘N’, but ends in a space.
Quiz – What Data is Left Justified and What is Right?
SELECT *
FROM Sample_Table
WHERE Col1 IS NULL
AND Col2 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 Col1 IS NULL
AND Col2 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. Next page shows a clear example.
Answer – What Data is Left Justified and What is Right?
SELECT Employee_No, First_Name
FROM Employee_Table
WHERE Employee_No = 2000000;
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 ;
Integers are Right Justified!
| Student_ID | Last_Name | |
| 423400 | Larkins | |
| 125634 | Hanson | |
| 280023 | McRoberts | |
| 260000 | Johnson | |
| 231222 | Wilson | |
| 234121 | Thomas | |
| 324652 | Delaney | |
| 123250 | Phillips | |
| 322133 | Bond | |
| 333450 | Smith |
Character Data is Left Justified!
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 2-byte VLI instead.
Use the TRIM command to remove spaces on CHAR Data
/* Last_Name has a Data Type of CHAR (20) */
By using the TRIM command on the Last_Name column, you are able to trim off any spaces from the end.
TRIM Eliminates Leading and Trailing Spaces
/* Last_Name has a Data Type of CHAR (20) */
SELECT *
FROM Student_Table
WHERE TRIM(Last_Name) LIKE ‘%n’ ;
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’!
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 B@ 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 TRIM(Last_Name) LIKE ‘T@_’ Escape ‘@’;
You didn't really need to get a full haircut, but just a TRIM Command and the Escape!