On Day 9, "Importing and Exporting Data," you learned how to transfer data to and from your SQL Server. Now that you have information in your database, it is time to learn how to extract just the data you want. You are going to begin the day with a simple SELECT statement and learn how to grab specific columns. You then will expand on this simple SELECT with data-manipulation techniques and data-conversion techniques. You will finish the day with a discussion on selecting specific rows and eliminating duplicate information. Day 10, "Data Retrieval," and Day 11, "Subqueries and Data Correlation," go hand in hand. After you build a good understanding of how the SELECT statement works, you will examine more complex data-retrieval techniques.
You can create queries to retrieve information from your database by using the SQL Query Tool in the Enterprise Manager, ISQL/w, ISQL, MSQuery, other tools, and third-party utilities. In this section, you will look at using the SELECT statement to retrieve rows and columns from tables in your database.
The SELECT statement consists of three basic components: SELECT, FROM, and WHERE. The basic syntax follows:
SELECT column_list FROM table_list WHERE search_criteria
The SELECT portion of the statement specifies the columns you want to retrieve. The FROM clause specifies the table(s) from which the columns are to be retrieved. The WHERE clause can be used to limit the amount of data returned by your query.
The complete syntax for the SELECT statement follows:
SELECT [ALL | DISTINCT] column_list [INTO [new_table_name]] [FROM {table_name | view_name}[(optimizer_hints)] [[, {table_name2 | view_name2}[(optimizer_hints)] [..., {table_name16 | view_name16}[(optimizer_hints)]]] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [COMPUTE clause] [FOR BROWSE]
NOTE: These optimizer hints are beyond the scope of this course. You can gather more information about these hints from Microsoft SQL Server 6.5 Unleashed or Microsoft SQL Server 6.5 DBA Survival Guide--both published by Macmillan Computer Publishing.
SELECT * FROM table_name is the most basic of all queries. When you use an asterisk (*) for column_list, it retrieves all columns from the table.
In the pubs database, for example, you can run this query to select all columns and rows from the authors table:
SELECT * FROM employee emp_id fname minit lname job_id job_lvl pub_id hire_date ------ ----- ----- ----- ------ ------- ------ --------- PMA42628M Paolo M Accorti 13 35 877 Aug 27 1992 12:00AM PSA89086M Pedro S Afonso 14 89 1389 Dec 24 1990 12:00AM VPA30890F Victoria P Ashworth 6 140 877 Sep 13 1990 12:00AM H-B39728F Helen Bennett L-B31947F Lesley Brown F-C16315M Francisco Chang GHT50241M Gary H Thomas 9 170 736 Aug 9 1998 12:00AM DBT39435M Daniel B Tonini 11 70 877 Jan 1 1990 12:00AM (43 row(s) affected)
To select specific columns from a table, you must designate the columns in column_list. Each column must be separated by a comma (,). You should not place a comma after the final column.
SELECT column_name [, column_name...] FROM table_name SELECT fname, lname, emp_id FROM employee fname lname emp_id ----- ------ ------ Paolo Accorti PMA42628M Pedro Afonso PSA89086M Victoria Ashworth VPA30890F Helen Bennett H-B39728F Lesley Brown L-B31947F Francisco Chang F-C16315M . . . . . . . . . . . . . . . . . . Gary Thomas GHT50241M Daniel Tonini DBT39435M (43 row(s) affected)
ANALYSIS: This query selects the first name, last name, and employee ID for each employee in the employee table.
When you execute a query with SELECT *, the column order is the same as the column order specified in the CREATE TABLE statement. When you select columns from a table, the column_list order does not have to be the same as the table column order. You can rearrange the column order in your query output by rearranging the columns in column_list.
You can take the previous query and rearrange the column order. The same information is returned, but it is displayed in a different column order.
SELECT emp_id, lname, fname FROM employee emp_id lname fname ------ ------ ------ PMA42628M Accorti Paolo PSA89086M Afonso Pedro VPA30890F Ashworth Victoria H-B39728F Bennett Helen L-B31947F Brown Lesley F-C16315M Chang Francisco . . . . . . . . . . . . . . . . . . GHT50241M Thomas Gary DBT39435M Tonini Daniel (43 row(s) affected)
When the results of a query are displayed, the column headings are the names used in column_list. Instead of using column headings, such as lname and fname, you can produce more readable column headings, such as FirstName and LastName, by aliasing the column headings. You can alias column headings using SQL Server 6.5 syntax or ANSI SQL syntax.
NOTE: SQL Server 6.5 supports both the ANSI '92 SQL syntax standard as well as its own Microsoft SQL Server 6.5 flavor.
You can alias columns with SQL Server syntax in two ways:
SELECT column_heading = column_name FROM table_name
or
SELECT column_name [AS] column_heading FROM table_name
You can rewrite your query by using the following SQL Server 6.5 syntax:
SELECT EmployeeID = emp_id, LastName = lname, FirstName = fname FROM employee
You also can rewrite your query by using ANSI SQL syntax:
SELECT emp_id AS EmployeeID, lname AS LastName, fname AS FirstName FROM employee
Both queries have the same result:
EmployeeID LastName FirstName ---------- -------- --------- PMA42628M Accorti Paolo PSA89086M Afonso Pedro VPA30890F Ashworth Victoria H-B39728F Bennett Helen L-B31947F Brown Lesley F-C16315M Chang Francisco . . . . . . . . . . . . . . . . . . GHT50241M Thomas Gary DBT39435M Tonini Daniel (43 row(s) affected)
NOTE: If the alias you used has spaces or is a SQL Server keyword, you must enclose the alias in single quotation marks.
Here is an example using spaces:SELECT lname AS `Last Name', fname AS `First Name' FROM employeeHere is an example using a SQL keyword:
SELECT `count' = Count(*) FROM employee
You also can use literals to make output more readable. A literal is a string surrounded by single quotation marks included in column_list and displayed as another column in the query result. It looks a lot like creating a label next to your column of information.
The syntax for including a literal value follows:
SELECT `literal' [, `literal'...]
***SELECT fname, lname, `Employee ID:', emp_id FROM employee fname lname emp_id ----- ------ ------- Paolo Accorti Employee ID: PMA42628M Pedro Afonso Employee ID: PSA89086M Victoria Ashworth Employee ID: VPA30890F Helen Bennett Employee ID: H-B39728F . . . . . . . . . . . . . . . . . . Gary Thomas Employee ID: GHT50241M Daniel Tonini Employee ID: DBT39435M (43 row(s) affected)
ANALYSIS: This query returns first name, last name, a column containing the literal string Employee ID:, and the employee ID for all employees in the employee table.
You can manipulate data in your query results to produce new columns that display computed values, new string values, converted dates, and more. Your query results can be manipulated using arithmetic operators, mathematical functions, string functions, datetime functions, and system functions. You also can use the CONVERT function to convert from one datatype to another for easier data manipulation.
You can use arithmetic operators on the following datatypes: int, smallint, tinyint, numeric, decimal, float, real, money, and smallmoney. Table 10.1 shows the arithmetic operators and the datatypes you can use with them.
Addition | Subtraction | Division | Mult. | Modulo | |
Datatype | + | - | / | * | % |
decimal | yes | yes | yes | yes | no |
float | yes | yes | yes | yes | no |
int | yes | yes | yes | yes | yes |
money | yes | yes | yes | yes | no |
numeric | yes | yes | yes | yes | no |
real | yes | yes | yes | yes | no |
smallint | yes | yes | yes | yes | yes |
smallmoney | yes | yes | yes | yes | no |
tinyint | yes | yes | yes | yes | yes |
NOTE: Because modulo is the integer remainder from the division of two integers, it can be used only with the int datatypes.
With arithmetic operations, two levels of precedence exist: datatype precedence and operator precedence.
Datatype precedence is used when arithmetic operations are performed on different datatypes. When you use different datatypes, the smaller datatype is converted to the higher datatype. If you multiply a smallint by an int, for example, the result is an int. The only exception to this rule is when using the money datatype, in which case the result always is of the datatype money.
Operator precedence is used when multiple operators are used. Operators follow the normal rules for operator precedence in which modulo always is evaluated first, followed by multiplication and division, followed by addition and subtraction, as read from right to left.
As with normal arithmetic operations, you can change the order of precedence by placing expressions in parentheses. The innermost expression (the deepest-nested expression) is evaluated first. You also can use parentheses to make the arithmetic operation more readable.
For example,
5 + 5 * 5 = 30 (The multiplication is done first.)
but
(5 + 5) * 5 = 50 (The nested expression is evaluated first.)
Mathematical functions enable you to perform commonly needed operations on mathematical data.
You can return mathematical data by using the following syntax:
SELECT function_name(parameters)
Table 10.2 lists the mathematical functions, their parameters, and their results. These examples include such operations as finding the absolute value, finding trigonometric functions, deriving square roots, and raising values to an exponential power. Table 10.3 shows some additional examples.
Function | Results |
ABS(numeric_expr) | Absolute value |
ACOS, ASIN, | Angle in radians whose cosine, sine, or tangent is a |
ATAN, ATN2(float_expr) | floating-point value |
COS, SIN, COT, TAN(float_expr) | Cosine, sine, or tangent of the angle (in radians) |
CEILING(numeric_expr) | Smallest integer greater than or equal to the specified value |
DEGREES(numeric_expr) | Conversion from radians to degrees |
EXP(float_expr) | Exponential value of specified value |
FLOOR(numeric_expr) | Largest integer less than or equal to the specified value |
LOG(float_expr) | Natural log |
LOG10(float_expr) | Base-10 log |
PI() | Constant 3.141592653589793 |
POWER(numeric_expr,y) | Value of numeric_expr to the power of y |
RADIANS(numeric_expr) | Conversion from degrees to radians |
RAND([seed]) | Random float number between 0 and 1 |
ROUND(numeric_expr,len) | Numeric_exp rounded to the specified length; length in an integer value |
SIGN(numeric_expr) | Positive, negative, or zero |
SQRT(float_expr) | Square root of the specified value |
Statement | Result |
SELECT SQRT(9) | 3.0 |
SELECT ROUND(1234.56, 0) | 1235 |
SELECT ROUND(1234.56, 1) | 1234.60 |
SELECT ROUND($1234.56, 1) | 1,234.60 |
SELECT POWER (2,8) | 256.0 |
SELECT FLOOR(1332.39) | 1332 |
SELECT ABS(-365) | 365 |
TIP: When using mathematical functions with monetary datatypes, you always should precede the datatype with a dollar sign ($). Otherwise, the value is treated as a numeric with a scale of 4.
When dealing with character information, you can use various string functions to manipulate the data (see Table 10.4). Most string functions manipulate only char and varchar datatypes; therefore, other datatypes first must be converted. You can return character data by using the following syntax:
SELECT function_name(parameters)
Function | Result |
+(expression, expression) | Concatenates two or more character strings |
ASCII(char_expr) | ASCII code value of the leftmost character |
CHAR(integer_expr) | Character equivalent of an ASCII code value |
CHARINDEX(pattern, expression) | Returns the starting position of a specified pattern |
DIFFERENCE(char_expr1, char_exr2) | Compares two strings and evaluates their similarity; returns a value from 0 to 4, 4 being the best match |
LOWER(char_expr) | Converts to lowercase |
LTRIM(char_expr) | Returns data without leading blanks |
PATINDEX(`%pattern%', expression) | Returns the starting position of the first occurrence in expression |
REPLICATE(char_expr, integer_expr) | Repeats char_expr integer_expr number of times |
REVERSE(char_expr) | Returns the reverse of char_expr |
RIGHT(char_expr, integer_expr) | Returns the character string starting integer_expr characters from the right |
RTRIM(char_expr) | Returns data without trailing blanks |
SOUNDEX(char_expr) | Returns a four-digit (SOUNDEX) code to evaluate the similarity of two character strings |
SPACE(integer_expr) | Returns a string of repeated spaces equal to integer_expr |
STR(float_expr[,length[,decimal]]) | Returns character data converted from numeric data; length is the total length and decimal is the number of spaces to the right of the decimal |
STUFF(char_expr1, start, length, char_expr2) | Deletes length characters from char_expr1 at start and inserts char_expr2 at start |
SUBSTRING(expression, start, length) | Returns part of a character or binary string |
UPPER(char_expr) | Converts to uppercase |
Take a look at the following examples.
You can submit the following to return a column called Name, which is a concatenation of last name, first initial, and employee ID:
SELECT lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name, emp_id as EmployeeID FROM employee Name EmployeeID ----- ----------- Accorti, P. PMA42628M Afonso, P. PSA89086M Ashworth, V. VPA30890F Bennett, H. H-B39728F . . . . . . Sommer, M. MFS52347M Thomas, G. GHT50241M Tonini, D. DBT39435M (43 row(s) affected)
Table 10.5 lists some more examples of string functions.
Statement | Result |
SELECT ASCII(`G') | 71 |
SELECT LOWER(`ABCDE') | abcde |
SELECT PATHINDEX(`%BC%','ABCDE') | 2 |
SELECT RIGHT(`ABCDE',3) | CDE |
SELECT REVERSE(`ABCDE') | EDCBA |
You can manipulate datetime values with date functions. Date functions can be used in the column_list, the WHERE clause, or wherever an expression can be used. Use the following syntax for date functions:
SELECT date_function (parameters)
Datetime values passed as parameters must be enclosed in single quotation marks or double quotation marks. Some functions take a parameter known as a datepart. Table 10.6 lists the datepart values and their abbreviations.
Datepart | Abbreviation | Values |
day | dd | 1-31 |
day of year | dy | 1-366 |
hour | hh | 0-23 |
millisecond | ms | 0-999 |
minute | mi | 0-59 |
month | mm | 1-12 |
quarter | 1-4 | |
second | ss | 0-59 |
week | wk | 0-51 |
weekday | dw | 1-7 |
year | yy | 1753-9999 |
Table 10.7 lists the date functions, their parameters, and their results. Table 10.8 shows some date function examples.
Function | Results |
DATEADD(datepart, number, date) | Adds the number of dateparts to the date |
DATEDIFF(datepart, date1, date2) | Specifies the number of dateparts between two dates |
DATENAME(datepart, date) | Returns the ASCII value for the specified datepart for the date listed |
DATEPART(datepart, date) | Returns an integer value for the specified datepart for the date listed |
GETDATE() | Specifies the current date and time in internal format |
Function | Results |
SELECT DATEDIFF(mm, '1/1/97', '12/31/99') | 35 |
SELECT GETDATE() | Apr 29, 1997 2:10AM |
SELECT DATEADD(mm, 6, `1/1/97') | Jul 1, 1997 2:10AM |
SELECT DATEADD(mm -5, `10/6/97') | May 6, 1997 2:10AM |
Now take a look at a more complex query that involves many of the pieces you have learned so far.
SELECT emp_id AS EmployeeID, lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name,`Has been employed for `, DATEDIFF(year, hire_date, getdate()), ` years.' FROM employee EmployeeID Name ---------- ----- PMA42628M Accorti, P. Has been employed for 5 years. PSA89086M Afonso, P. Has been employed for 7 years. VPA30890F Ashworth, V. Has been employed for 7 years. H-B39728F Bennett, H. Has been employed for 8 years. . . . . . . . . . . . . . . . MFS52347M Sommer, M. Has been employed for 7 years. GHT50241M Thomas, G. Has been employed for 9 years. DBT39435M Tonini, D. Has been employed for 7 years. (43 row(s) affected)
You can use a number of built-in system functions to query the system tables. You can return system data by using the following syntax:
SELECT function_name(parameters)
You can use system functions in the column_list, WHERE clause, and anywhere else an expression can be used.
Table 10.9 lists the system functions, parameters, and results.
Function | Results |
COALESCE(expression1, | Returns the first non-null |
expression2,...expressionN) | expression |
COL_NAME(table_id, column_id) | Returns column name |
COL_LENGTH(`table_name',`column_name') | Returns column length |
DATALENGTH(`expression') | Returns actual length of expression of any datatype |
DB_ID([`database_name']) | Returns database ID |
DB_NAME([database_id]) | Returns database name |
GETANSINULL([`database_name']) | Returns default nullability of the database |
HOST_ID() | Returns host process ID |
HOST_NAME() | Returns host computer name |
IDENT_INCR(`table_name') | Returns increment value specified during creation of identity column |
IDENT_SEED(`table_name') | Returns seed value specified during creation of identity column |
INDEX_COL(`table_name', index_id, key_id) | Returns indexed column name |
ISDATE(variable | column_name) | Checks for a valid date format; returns 1 if valid--otherwise, returns 0 |
ISNULL(expression, value) | Returns specified value in place of null |
ISNUMERIC(variable | column_name) | Checks for a valid numeric format; returns 1 if valid--otherwise, returns 0 |
NULLIF(expression1, expression2) | Returns NULL if expression1 = expression2 |
OBJECT_ID(`object_name'') | Returns database object ID |
OBJECT_NAME(object_id') | Returns database object name |
STATS_DATE(table_id, index_id) | Returns the date at which index statistics were updated |
SUSER_ID([`server_username']) | Returns server user's ID |
SUSER_NAME([server_id]) | Returns server user's name |
USER_ID([`username']) | Returns database user's ID |
USER_NAME([user_id]) | Returns database user's name |
This query uses two system functions to return the name of the second column of the employee table.
SELECT COL_NAME(OBJECT_ID(`employee'),2) fname (1 row(s) affected)
Because many functions require data in a certain format or datatype, you might find it necessary to convert from one datatype to another. You use the CONVERT function to modify your datatypes; you can use CONVERT anywhere expressions are allowed. The CONVERT statement has the following syntax:
CONVERT(datatype[(length)], expression [,style])
Table 10.10 lists the CONVERT style parameters associated with their standards and the format of the output.
Style Without Century yy | Style With Century yyyy | Standard | Date Format Output |
- | 0 or 100 | Default | mon dd yyyy hh:mi AM (or PM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | British/French | dd/mm/yy |
4 | 104 | German | dd.mm.yy |
5 | 105 | Italian | dd-mm-yy |
6 | 106 | - | dd mon yy |
7 | 107 | - | mon dd, yy |
8 | 108 | - | hh:mi:ss |
- | 9 or 109 | Default + milliseconds | mon dd, yyyy hh:mi:ss:ms AM (or PM) |
10 | 110 | USA | mm-dd-yy |
11 | 111 | Japan | yy/mm/dd |
12 | 112 | ISO | yymmdd |
- | 13 or 113 | Europe default + milliseconds |
dd mon yyyy hh:mi:ss:ms(24h) |
14 | 114 | - | hh:mi:ss:ms(24h) |
You can submit the following query to convert the current date to a character string of eight and a date style of ANSI:
SELECT CONVERT(CHAR(8),GETDATE(),2) -------- 97.07.06 (1 row(s) affected) SELECT emp_id AS EmployeeID, lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name, `Has been employed for ` + CONVERT(CHAR(2), (DATEDIFF(year, hire_date, getdate()))) + ` years.' FROM employee EmployeeID Name ---------- ---- PMA42628M Accorti, P. Has been employed for 5 years. PSA89086M Afonso, P. Has been employed for 7 years. VPA30890F Ashworth, V. Has been employed for 7 years. H-B39728F Bennett, H. Has been employed for 8 years. . . . . . . . . . MFS52347M Sommer, M. Has been employed for 7 years. GHT50241M Thomas, G. Has been employed for 9 years. DBT39435M Tonini, D. Has been employed for 7 years. (43 row(s)affected)
ANALYSIS: This example is built on the query you ran in the section "Date Functions," earlier in this chapter. In this example, you combined the last three columns into one column by using the CONVERT function and string concatenation.
You have looked at various ways to retrieve, format, and manipulate the columns in the result set of a query. Now you will learn how to specify which rows to retrieve based on search conditions. You can do this by using the WHERE clause of your SELECT statement. Search conditions include comparison operators, ranges, lists, string matching, unknown values, combinations, and negations of these conditions.
The basic syntax for specifying which rows to retrieve follows:
SELECT column_list FROM table_list WHERE search_conditions
You can implement the search conditions by using comparison operators (see Table 10.11). You can select rows by comparing column values to a certain expression or value. Expressions can contain constants, column names, functions, or nested subqueries. If you are comparing two different character datatypes (such as char and varchar), or you are comparing date datatypes (such as datetime and smalldatetime), these must be enclosed in single quotation marks. Double quotation marks are acceptable, but single quotation marks maintain ANSI compliance.
The syntax for the WHERE clause using comparison operators follows:
SELECT column_list FROM table_list WHERE column_name comparison_operator expression
Operator | Description |
= | Equal to |
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
<> | Not equal to (preferred) |
!= | Not equal to |
!> | Not greater than |
!< | Not less than |
() | Order of precedence |
Here's an example:
SELECT emp_id, lname, fname FROM employee WHERE pub_id = `0877' emp_id lname fname ------- ------ ------ PMA42628M Accorti Paolo VPA30890F Ashworth Victoria H-B39728F Bennett Helen . . . . . . . . . M-R38834F Rance Martine DBT39435M Tonini Daniel (10 row(s) affected)
ANALYSIS: This query returns the employee ID, last name, and first name for all employees employed by the publisher with a pub_id of 0877.
You can retrieve rows based on a range of values using the BETWEEN keyword. As with the comparison operator, if you are specifying ranges based on character datatypes (such as char and varchar) or date datatypes (such as datetime and smalldatetime), these must be enclosed in single quotation marks.
The syntax for the WHERE clause using comparisons follows:
SELECT column_list FROM table_list WHERE column_name [NOT] BETWEEN expression AND expression SELECT lname, emp_id FROM employee WHERE hire_date BETWEEN `10/1/92' AND `12/31/92' lname emp_id ----- ------- Josephs KFJ64308F Paolino MAP77183M (2 row(s) affected)
This query returns the last name and employee ID for all employees hired between 10/1/92 and 12/31/92.
You can retrieve rows with values that match those in a list by using the IN keyword. If you are specifying ranges based on character datatypes (such as char and varchar) or date types (such as datetime and smalldatetime), you must enclose these types in single quotation marks.
The syntax for the WHERE clause using comparisons follows:
SELECT column_list FROM table_list WHERE [NOT] column_name [NOT] IN (value_list)
If you want to find employees who work for publishers with a pub_id of 0877 or 9999, you submit the following query:
SELECT emp_id, lname, fname FROM employee WHERE pub_id IN (`0877', `9999') emp_id lname fname ------ ----- ----- PMA42628M Accorti Paolo VPA30890F Ashworth Victoria H-B39728F Bennett Helen . . . . . . . . . A-R89858F Roulet Annette DBT39435M Tonini Daniel (17 row(s) affected)
You also can retrieve rows not in the list by using the NOT operator. If you want to find all employees who do not work for publishers with a pub_id of 0877 or 9999, you can submit the following query:
SELECT emp_id, lname, fname FROM employee WHERE pub_id NOT IN (`0877', `9999') emp_id lname fname ------ ----- ------ PSA89086M Afonso Pedro F-C16315M Chang Francisco PTC11962M Cramer Philip A-C71970F Cruz Aria AMD15433F Devon Ann ....... CGS88322F Schmitt Carine MAS70474F Smith Margaret HAS54740M Snyder Howard MFS52347M Sommer Martin GHT50241M Thomas Gary (26 row(s) affected)
TIP: Try using positive search conditions whenever possible. Avoid using NOT, because the query optimizer does not recognize negative search conditions. In other words, SQL Server has to do a lot more work to return your result set when you use NOT. You could rewrite the preceding query by using BETWEEN and AND statements.
You can retrieve rows based on portions of character strings by using the LIKE keyword. LIKE is used with char, varchar, text, datetime, and smalldatetime data. You also can use four wildcard characters in the form of regular expressions.
The syntax for the WHERE clause using the LIKE keyword follows:
SELECT column_list FROM table_list WHERE column_name [NOT] LIKE `string'
The available wildcards follow: % A string of zero or more characters
_ A single character
[] A single character within the specified range [^] A single character not within the specified range When you use the LIKE clause, make sure that you enclose the wildcard and/or characters in single quotation marks.
Take a look at the following examples.
You can submit the following query to return the title_id and title of all books with "computer" anywhere in the title from the title table.
SELECT title_id, title FROM titles WHERE title LIKE `%computer%' title_id title -------- ----- BU1111 Cooking with Computers: Surreptitious Balance Sheets BU2075 You Can Combat Computer Stress! BU7832 Straight Talk About Computers MC3026 The Psychology of Computer Cooking PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations (5 row(s) affected)
You can submit the following query to return the au_id, au_lname, and au_fname of all authors whose names begin with B or M from the authors table.
SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname LIKE `[BM]%' au_id au_lname au_fname ----- -------- -------- 409-56-7008 Bennet Abraham 648-92-1872 Blotchet-Halls Reginald 724-80-9391 MacFeather Stearns 893-72-1158 McBadden Heather (4 row(s) affected)
What is an unknown or null value? A null value is not the same as a blank character string, and it is not the same as a 0 when dealing with numeric data. A null occurs when a value is not assigned to a field. In such cases, a null fails all comparisons to blanks, zeros, and other nulls (when using the greater than (>) or less than (<) comparison operators). So how do you find rows based on null values? You can discriminate between rows in your tables containing null values by using the IS NULL and IS NOT NULL keywords.
The syntax for the WHERE clause using the IS NULL and IS NOT NULL operators follows:
SELECT column_list FROM table_list WHERE column_name IS [NOT] NULL
Examine the following examples.
You can submit this query to find all books that have no sales:
SELECT title_id, title FROM titles WHERE ytd_sales IS NULL
or
SELECT title_id, title FROM titles WHERE ytd_sales = NULL title_id title -------- ----- -------- ----- MC3026 The Psychology of Computer Cooking PC9999 Net Etiquette (2 row(s) affected)
In contrast to the preceding query, you can use the IS NOT NULL clause to find all books that do have ytd_sales values by submitting the following query.
SELECT title_id, title FROM titles WHERE ytd_sales IS NOT NULL
or
SELECT title_id, title FROM titles WHERE ytd_sales <> NULL title_id title -------- ----- BU1032 The Busy Executive's Database Guide BU1111 Cooking with Computers: Surreptitious Balance Sheets BU2075 You Can Combat Computer Stress! BU7832 Straight Talk About Computers . . . . . . TC3218 Onions, Leeks, and Garlic: Cooking Secrets of the Mediter- ranean TC4203 Fifty Years in Buckingham Palace Kitchens TC7777 Sushi, Anyone? (16 row(s) affected)
You have looked at selecting rows based on specific values, ranges, lists, string comparisons, and unknown values. Now, you'll look at retrieving rows using multiple search criteria.
You can combine multiple search criteria using the logical operators AND, OR, and NOT. Using AND and OR operators enables you to join two or more expressions. AND returns results when all conditions are true. OR returns results when any of the conditions are true.
When more than one of the logical operators are used in the WHERE clause, the order of precedence can be significant. NOT is followed by AND and then OR.
Here is the syntax for a WHERE clause using multiple criteria:
SELECT column_list FROM table_list WHERE [NOT] expression {AND|OR} [NOT] expression
NOTE: If you are using arithmetic operators joined by logical operators, arithmetic operators are processed first. Of course, you can always change the order of precedence by using parentheses.
Take a look at some examples.
Query 1
Consider the following: You want to retrieve the title_id, title, and price for all books that have a publisher ID of 0877 or computer in the title, and for which the price is NOT NULL.
SELECT title_id, title, price, pub_id FROM titles WHERE title LIKE `%computer%' OR pub_id = `0877' AND price IS NOT NULL title_id title price pub_id -------- ----- ------ ------ BU1111 Cooking with Computers: Surreptitious 11.95 1389 Balance Sheets BU2075 You Can Combat Computer Stress! 2.99 736 BU7832 Straight Talk About Computers 19.99 1389 MC2222 Silicon Valley Gastronomic Treats 19.99 877 MC3021 The Gourmet Microwave 2.99 877 MC3026 The Psychology of Computer Cooking (null) 877 PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.59 877 TC3218 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.95 877 TC4203 Fifty Years in Buckingham Palace Kitchens 11.95 877 TC7777 Sushi, Anyone? 14.99 877 (10 row(s) affected)
Query 2
Now run the query again and see whether you can get rid of that null value in your price field:
SELECT title_id, title, price, pub_id FROM titles WHERE (title LIKE `%computer%' OR pub_id = `0877') AND price IS NOT NULL title_id title price pub_id -------- ----- ----- ------ BU1111 Cooking with Computers: Surreptitious Balance Sheets 11.95 1389 BU2075 You Can Combat Computer Stress! 2.99 736 BU7832 Straight Talk About Computers 19.99 1389 MC2222 Silicon Valley Gastronomic Treats 19.99 877 MC3021 The Gourmet Microwave 2.99 877 PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.59 877 TC3218 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.95 877 TC4203 Fifty Years in Buckingham Palace Kitchens 11.95 877 TC7777 Sushi, Anyone? 14.99 877 (9 row(s)affected)
ANALYSIS: Note that Query 2 returns the desired results by changing the order of precedence.
When selecting certain information from a table, you may receive duplicate rows of information. You can eliminate duplicates by using the DISTINCT clause in the SELECT portion of the SELECT statement. If you do not specify the DISTINCT clause, all rows that meet the WHERE clause criteria are returned.
The syntax for the DISTINCT clause follows:
SELECT DISTINCT column_list FROM table_name WHERE search_conditions
Distinctness is determined by the combination of all the columns in column_list. Null values are treated as duplicates of each other; therefore, only one NULL is returned.
Table 10.12 shows some SELECT statements with and without the DISTINCT clause. Query 1 lists states in which authors live without listing duplicates. Query 2 lists cities authors live in without listing duplicates. Query 3 lists distinct combinations of cities and states.
Query 1 | Query 2 | Query 3 |
SELECT DISTINCT | SELECT DISTINCT | SELECT DISTINCT |
state | city | city, state |
FROM authors | FROM authors | FROM authors |
STATE | CITY | CITY and State |
CA | Ann Arbor | Ann Arbor, MI |
IN | Berkeley | Berkeley, CA |
KS | Corvallis | Corvallis, OR |
MD | Covelo | Covelo, CA |
MI | Gary | Gary, IN |
OR | Lawrence | Lawrence, KS |
TN | Menlo Park | Menlo Park, CA |
UT | Nashville | Nashville, TN |
Oakland | Oakland, CA | |
Palo Alto | Palo Alto, CA | |
Rockville | Rockville, MD | |
Salt Lake City | Salt Lake City, UT | |
San Francisco | San Francisco, CA | |
San Jose | San Jose, CA | |
Vacaville | Vacaville, CA | |
Walnut Creek | Walnut Creek, CA |
If you just want to list the different cities where authors live, why not just use Query 2? It gives you the same cities as Query 3. To answer that question, suppose that two authors in your database live in Portland. If you run Query 2, it returns Portland as one of the distinct values. However, one author lives in Portland, Oregon and the other lives in Portland, Maine. Obviously, these are two distinct locations, so submitting Query 3 using the DISTINCT combination of city and state returns both Portland, OR and Portland, ME.
Notice that the results in all three queries are sorted. This is not by chance. The values are sorted first so that the first value can be compared to the next value to make it easier to remove duplicate values. Also note that if you have multiple columns in the DISTINCT clause, the results are sorted in the order of your column_list.
Take a closer look at Query 3:
SELECT DISTINCT city, state FROM authors
If the results returned Portland, OR and Portland, ME, it would look like this:
city state ---- ------ Portland ME Portland OR (2 row(s) affected)
You can sort your query results by using the ORDER BY clause in your SELECT statement.
The basic syntax for using the ORDER BY clause follows:
SELECT column_list FROM table_list [ORDER BY column_name | column_list_number [ASC|DESC]]
You can have up to 16 columns in your ORDER BY list. You also can specify column names or use the ordinal number of the columns in column_list.
Both these queries return the same ordered result sets:
Query 1
SELECT title_id, au_id FROM titleauthor ORDER BY title_id, au_id
Query 2
SELECT title_id, au_id FROM titleauthor ORDER BY 1, 2
You can use column names and ordinal numbers together in the ORDER BY clause. You also can specify whether you want the results sorted in ascending (ASC) or descending (DESC) order. If you do not specify ASC or DESC, ASC is used.
If you are sorting results based on a column that has null values, and ASC order is used, the rows containing nulls are displayed first.
When using the ORDER BY clause, the sort order of your SQL Server can make a difference in your result sets. The default sort order for SQL Server is dictionary order and not case sensitive. If your SQL Server is using a sort order that is case sensitive, it can affect the overall query response time and the ordering of your result sets. This is because a capital A is not considered the same as a lowercase a when you are using a case-sensitive sort order.
To find out what your server's current sort order is, you can execute the system-stored procedure sp_helpsort.
NOTE: You cannot use the ORDER BY clause on columns that are of text or image datatypes. This is true for text datatypes, because they are stored in a different location on your database and can range from 0 Gig to 2.15 Gig. Attempting to sort on a field this size is not allowed in SQL Server. Image datatypes also are stored in their own separate 2KB data pages and are not sortable.
In this chapter, you built a foundation for data retrieval using the SELECT statement. You then learned how to change your column headings and add string literals to your output.
You expanded on your understanding of the SELECT statement using arithmetic, mathematical, string, and date functions. You then learned about system functions to further manipulate your data.
Many times, the data you want to work with is not expressed in the format and datatype you must use. You learned how to use data conversion to alter your data from one datatype to another.
You then continued expanding on the SELECT statement with a discussion and examples of choosing different rows of information by applying comparison operators, ranges of values, lists, and character strings.
You learned how to eliminate rows with null values. You also learned how to select DISTINCT rows of information.
You finished the chapter by learning how to sort your data using the ORDER BY clause.
This Workshop consists of a short quiz to test your understanding of the chapter and some exercises for you to put into practice what you have learned.
SELECT * FROM authors WHERE au_lname LIKE `M%'
SELECT emp_id AS EmployeeID, lname AS LastName, fname AS FirstName FROM employee
SELECT ROUND ($7725.53, 1)
SELECT lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name, emp_id AS EmployeeID FROM employee
1. SELECT title_id, title, price FROM titles WHERE pub_id = `0877' OR title LIKE `%computer%'(returns 14 rows)
2. SELECT * FROM titles WHERE price IS NOT NULL(returns 16 rows)
3. SELECT title, price FROM titles ORDER BY price DESC(returns 18 rows)
© Copyright, Macmillan Computer Publishing. All rights reserved.