Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 267
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 267 The percent sign in this example is the modulo operator: it returns the remainder of the first number divided by the second number. Basically, what this SELECT statement does is return the names of all the odd-numbered (divisible by 2 with a remainder of 1) objects in the current database. When the ID modulo 2 is 1, then it s an odd number, and the CASE statement returns 1, which the WHERE clause then compares to the number 1, and the row is included in the resultset. Otherwise, the CASE statement returns 0, which does not equal one, so the row is not included in the resultset. The keen of wit will note that a better way to write this would be: SELECT name FROM sysobjects WHERE id % 2 = 1 That, however, would not demonstrate the point of using CASE statements in a WHERE clause, nor would it be nearly as convoluted. It would, however, be readable and efficient. You should be aware of a couple of shortcuts. The ISNULL function is a great way to handle NULL values without using a CASE statement. Instead of writing this: SELECT CASE sid WHEN null THEN 0 ELSE sid END FROM sysusers you could write this statement, which does the same thing: SELECT isnull(sid, 0) FROM sysusers Another statement that s a shortcut for a CASE statement is called COALESCE. It takes a series of values and returns the first one that s not null. You could rewrite the preceding statement with: SELECT coalesce(sid, 0) FROM sysusers and get the same results. Now that you ve got the CASE statement down, you can learn how to join tables together end-to-end with the UNION operator. The UNION Operator The UNION operator is used to join two queries together end-toend, instead of side-by-side. A UNION operator takes the output of two or more SELECT statements and creates one recordset. Each
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.