Web Hosting Java Blog

262 Part I EXAM PREPARATION in Sales, which would mean a person who hadn t bought anything yet, you could run a query like this one: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person LEFT JOIN Sales ON Person.PersonID = Sales.PersonID WHERE Sales.QtyPurchased IS NULL This query exploits the fact that when you do a LEFT JOIN, the columns that are returned for the table on the right when there is no correlation are all NULL. The fact that the QtyPurchased column in the Sales table is used is just coincidence; any column in the Sales table that doesn t allow NULL values could be used. Whenever there is no correlation between the data rows, the columns for the table on the inside of the join are all set to NULL, making it a very easy target for filtering. If you re using this data for a report, you probably don t want the word NULL showing up on the report. The managers and sales people who end up reading the report probably won t understand the word NULL. So, to steal some thunder from a bit later in the blog, here s how you can get rid of those annoying NULLs: SELECT FirstName, LastName, ISNULL(ProductID, 0), .ISNULL(QtyPurchased, 0) FROM Person LEFT JOIN Sales ON Person.PersonID = Sales.PersonID WHERE Sales.QtyPurchased IS NULL The ISNULL() function returns the second argument if the first argument is NULL. So, for the cases where the QtyPurchased is NULL, it substitutes in a zero. ISNULL() is covered a bit later during the discussion about CASE expressions. Cross Joins Cross joins are joins that return all the rows from both tables associated with each other. For example, currently the Person table has 9 records and the Product table has 7 records. A CROSS JOIN of these two tables would return 63 rows: each of the 9 records in the Person table with each of the 7 records in the Product table. Whenever you use a CROSS JOIN, you should be aware that the returned resultset is likely to be very large. A CROSS JOIN does not have an ON clause; there is no correlation, so it s not needed.

For high quality website hosting services please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 261 join type. Now here s a list of all the addresses in the database and what they bought, if they bought anything: SELECT A.*, S.ProductID, S.QtyPurchased FROM Person P LEFT JOIN Sales S ON P.PersonID = S.PersonID INNER JOIN PersonAddress PA ON PA.PersonID = P.PersonID INNER JOIN Address A on PA.AddressID = A.AddressID Now, the FULL OUTER JOIN. Imagine that you want to query all the records from Person and Sales, and have the tables put together where possible, but you want all records shown from both tables, regardless of whether there was a correlation or not. Here s how you can do it: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person FULL JOIN Sales ON Person.PersonID = Sales.PersonID That returns the following recordset: FirstName LastName ProductID QtyPurchased Danny Jones 37 4 Danny Jones 38 3 Melissa Jones NULL NULL Scott Smith 39 1 Alex Riley 51 1 Chris Avery NULL NULL Jennifer Avery NULL NULL Bryan Decker NULL NULL Robin Decker NULL NULL Shelly Alexander 37 10 Shelly Alexander 38 5 NULL NULL 41 6 Notice that you have examples where the data that comes from the Sales table (ProductID and QtyPurchased) is NULL, for Chris Avery and others, along with an example of ProductID of 41 and QtyPurchased of 6 that was purchased by someone not in the Person table. Filtering with Outer Joins One common use of an outer join is to find places where there aren t correlations in data. For example, if you wanted to find all the rows in the Person table that didn t have a corresponding row

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

260 Part I EXAM PREPARATION a matching record in Sales? This example returns all the records in Person, and matching records in Sales where they exist. First, using a LEFT JOIN: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person LEFT JOIN Sales ON Person.PersonID = Sales.PersonID And then a RIGHT JOIN: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Sales RIGHT JOIN Person ON Person.PersonID = Sales.PersonID These two queries both return exactly the same resultset, because the order of the tables is different. The LEFT JOIN example does Person LEFT JOIN Sales and the RIGHT JOIN example does Sales RIGHT JOIN Person. IN THE FIELD RIGHT JOIN AND LEFT JOIN As you ve just read, LEFT JOIN and RIGHT JOIN both do the same thing. Why does Transact-SQL include both, and which should you use? Typically, you should use LEFT JOIN whenever possible. There s no reason for this other than convention, and most Western languages read left to right, so people in Western cultures are more trained to think in that direction. So using LEFT JOIN will make your query easier for someone else to understand. In other order-related issues, the order that the ON clause is in does not change how the clause works. For example, saying ON P.PersonID = S.PersonID is the same as S.PersonID = P.PersonID. Now it s time to put together everything you ve learned so far. You ve got an INNER JOIN and an OUTER JOIN. You can use table aliases in an OUTER JOIN the same way as you do in an INNER JOIN, by the way. Table aliasing is a feature of the FROM clause, not the

For high quality java hosting services please check tomcat web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 259 One more variation on the theme of inner joins. Imagine that you need to join a table back onto itself. For example, you want to find all the people with the same last name in the Person table. You d write a query like this one: SELECT P1.FirstName, P2.FirstName, P1.LastName FROM Person P1 INNER JOIN Person P2 ON P1.LastName = P2.LastName AND P1.FirstName < P2.FirstName In this example, the same table is used twice, joined to itself. The example produces the following output: FirstName FirstName LastName Danny Melissa Jones Chris Jennifer Avery Bryan Robin Decker The join also has an extra line in it. The P1.FirstName < P2.FirstName part prevents SQL Server from producing duplicate rows. Without that line in the JOIN clause, or at least in the WHERE clause, you d have each couple showing up twice, once with each name first. Now that you ve seen how INNER JOIN works, it s time to move on to the outer joins. Outer Joins You can use an outer join when you want to return the entire list of rows from one side of the join, and the matching rows from the other side of the join. There are three types of outer joins: LEFT, RIGHT, and FULL. A RIGHT OUTER JOIN, often abbreviated RIGHT JOIN, returns all the rows belonging to the table on the right side of the join, and where there is a matching row on the table on the left side, it returns the matching data. Conversely, LEFT OUTER JOIN returns all the rows from the table on the left side, and where there is a matching row on the table on the right side, it returns the matching data. A FULL OUTER JOIN returns all the rows from both sides with correlations where they exist. Most people read that for the first time and their eyes cross. Here are some examples to help you get back on track. Remember the query you saw earlier that returned all the records from Person with

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

NOTE 258 Part I EXAM PREPARATION This is an example of using table aliases. When you re doing complex joins, and you don t want to type the table name over and over again, you can alias the table to a different name. In this case, the Person table is aliased to P. After doing that, the only way you can access column names in the Person table is by using the P alias. You can t mix aliased and non-aliased names, so this does not work: SELECT A.* FROM Person P INNER JOIN Sales S ON P.PersonID = S.PersonID INNER JOIN PersonAddress PA ON PA.PersonID = P.PersonID INNER JOIN Address A ON PA.AddressID = A.AddressID If you attempt to run that query, you get an error that says, the column prefix Person does not match with a table name or alias name used in the query. That s because you aliased the table name to something else, so SQL Server can t use the real name for it anymore; it can use only the alias. Another form of table aliasing syntax is SELECT A.* FROM Person AS P INNER JOIN Sales AS S ON P.PersonID = S.PersonID INNER JOIN PersonAddress AS PA ON PA.PersonID = P.PersonID INNER JOIN Address AS A ON PA.AddressID = A.AddressID The AS keyword is always optional, and if the point is to type less by Using Aliases Effectively You should using aliases, it should probably be left out. use aliases that somehow abbreviate On the topic of optional syntax, the keyword INNER is not required the table name in some consistent in the queries, either. If you want to write the query as follows, it and standard way. For example, don t alias the first table as A, the second works the same way: table as B, and so on, because you ll SELECT A.* spend more time figuring out what the FROM Person P JOIN Sales S aliases mean than it would have ON P.PersonID = S.PersonID taken to type in the full names in the JOIN PersonAddress PA first place. You should carefully mea-ON PA.PersonID = P.PersonID sure tradeoffs between readability and JOIN Address A ON PA.AddressID = A.AddressID brevity, and if you have to guess, lean toward readability. Arguably, this example requires less typing, but many people feel that the keyword INNER provides a little more insight into how the query works and is easier to read.

For high quality website hosting services please check cheap web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 257 PersonID column, so SQL Server gives you an error if you don t specify which table the column comes from. SQL Server also gives you an error if you write this: SELECT PersonID, FirstName, LastName, ProductID, .QtyPurchased FROM Person INNER JOIN Sales ON Person.PersonID = Sales.PersonID This results in an error of Ambiguous column name PersonID . Once again, because the PersonID column appears more than once in the set of joined tables, you need to specify which PersonID you want, even though they are both going to have the same value after the join. The code sample is also representative of one way to indent and style the SQL. It s valid to put the entire SQL statement on one line, but it s also valid to wear dark socks with sandals, striped shorts, and a plaid shirt. Use a consistent style when you are writing queries; it makes them easier for you to read and for others to read. That s all well and good, but how can you get a list of the addresses for all the people to whom you ve sold things? That goes something like this: SELECT Address.* FROM Person INNER JOIN Sales ON Person.PersonID = Sales.PersonID INNER JOIN PersonAddress ON PersonAddress.PersonID = Person.PersonID INNER JOIN Address ON PersonAddress.AddressID = Address.AddressID The Address.* in that statement specifies that you want to return all the fields in the Address table, but you don t want the fields in the rest of the tables. This is a convenient shortcut. Here s another neat way to reduce typing: SELECT A.* FROM Person P INNER JOIN Sales S ON P.PersonID = S.PersonID INNER JOIN PersonAddress PA ON PA.PersonID = P.PersonID INNER JOIN Address A ON PA.AddressID = A.AddressID

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

256 Par t I EXAM PREPARATION Using INNER JOIN The INNER JOIN statement is the easiest and most often used join statement. It s very simple; it relates one table to another table; and it returns a rowset where the two tables match up. For example: SELECT * FROM Person INNER JOIN Sales on Person.PersonID = Sales.PersonID What s that going to do? It returns all the rows where there is a person who has made a purchase. The columns returned are all the columns from both tables, with the columns from the Person table first. The resultset is wider than you probably want: PersonID FirstName LastName PersonID ProductID QtyPurchased DatePurchased 1 Danny Jones 1 37 4 2001-07-22 16:50 1 Danny Jones 1 38 3 2001-07-22 16:50 3 Scott Smith 3 39 1 2001-07-22 16:50 4 Alex Riley 4 51 1 2001-07-22 16:50 4 Alex Riley 4 47 1 2001-07-22 16:50 9 Shelly Alexander 9 37 10 2001-07-22 16:50 9 Shelly Alexander 9 38 5 2001-07-22 16:50 That s probably not what you want in a resultset. You ll get a fairly useful resultset if you write the SELECT statement like this, specifying the column names, instead: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person INNER JOIN Sales ON Person.PersonID = Sales.PersonID This query shows the people in the database to whom you have sold things. It doesn t show all the people in the database, because not everyone has bought something. It also shows some people more than once, because they bought several things. Now look more closely at the statement after the keyword FROM. First, data is selected from the Person table, and followed by an INNER JOIN to the Sales table. The ON clause then tells SQL Server how the tables are related. That ON clause says to return rows where the PersonID column in the two tables is the same. Notice that the column name is specified as Person.PersonID. Both tables have a

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 255 of purchases they ve made, and show NULL values for people who haven t made purchases yet, but are in the Person table. An outer join shows all the same records as an inner join, plus all the records that don t match. The final type of join is a cross join. A cross join returns all possible combinations of rows between the two sides of the join. The number of records in the resultset is equal to the number of records on one side of the join multiplied by the number of records on the other side of the join. No correlation is attempted between the two records; all the records from both sides are returned. Performing a cross join on two large tables is probably not a good idea. The number of rows in the return set, or in intermediate sets used by SQL Server, can get out of hand quickly, causing server-wide performance degradation. IN THE FIELD HOW MANY RECORDS? If you have 100 records in the Person table, and 100 records in the Sales table, and then perform a cross join between the two tables, you d end up with 10,000 records in your resultset (100 100 = 10,000). Scale that up to a 50,000 record table and a 100 record table (5 million records) and you ll see some typical bad news that can result from a cross join. If you figure that each of those returned records contains about 300 bytes, you can end up with a resultset that weighs in at 1.5 gigabytes, which would probably overwhelm any client computer at which you aimed it. In the grand scheme of SQL, joins are processed first. In other words, each join type description says, will return all the records from one side… , which means the join returns all those records. What you put in the WHERE clause is applied after the joins are processed. So, bear in mind that when a join returns a specified set of records, the SQL statement may or may not return all those records, depending on what you ve specified in the WHERE clause. With all of that out of the way, let s look at each of the different join operators, starting with INNER JOIN. NOTE Order of Operations Before some purist out there starts screaming and writing inflammatory post-publishing e- mails, a bit of clarification: Physically, SQL Server performs operations such as joining tables and filtering data in a WHERE clause in whatever order it decides is fastest. Logically, however, you can feel free to think of it doing things in join, then WHERE order. SQL Server doesn t re-order how it does things in such a way that will violate that rule.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

254 Part I EXAM PREPARATION INSERT INTO Product VALUES (38, Grommet ) INSERT INTO Product VALUES (39, Spackle ) INSERT INTO Product VALUES (51, Sparkle Dust ) INSERT INTO Product VALUES (47, Shoe Polish ) INSERT INTO Product VALUES (38, Varnish ) INSERT INTO Product VALUES (41, Lava ) go SELECT COUNT(*) FROM person –9 rows SELECT COUNT(*) FROM address –6 rows SELECT COUNT(*) FROM PersonAddress –9 rows SELECT COUNT(*) FROM Sales –8 rows SELECT COUNT(*) FROM Product –7 rows That s a lot of typing, but you ll end up using that data all the way through the chapter. Notice that several tables have data in them. You re probably wondering how to make them all work together. So now it s time to talk about join mechanics. Join Mechanics Whenever you query data from two tables, you need to find some way to relate the two tables together. If you need to select out the name and address of everyone in the example database, how would you go about doing it? You d want to write a query that would relate the Person table to the Address table through the PersonAddress table. Whenever you want to use a SQL statement to relate one table to another, it s called a join. In this case, you need two joins: one from the Person table to the PersonAddress table, and another one from the PersonAddress table to the Address table. Whenever you want to see a resultset that includes columns from several tables, you need to use a join. There are three basic join types. An inner join shows results only where there are matches between the elements. In other words, if you query the database and want to see only the people who have addresses, you use an inner join. An inner join leaves out all the records that don t have a match. An outer join can show all the records from one side of the relationship, records that match where they are available, and NULL values for records that do not have a match. An outer join between the Person and Sales tables can show you each person and the amount

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 253 addition, there is also a one-to-many relationship between Person and Sales, which means one person may have made several purchases, which are recorded in the Sales table. You may want to go ahead and build this structure in its own database on the server on which you re doing the examples. Now insert some data into the sample schema and try it again. Some SELECT COUNT statements are at the bottom of the script to help you make sure you ve got the correct number of rows in each table. INSERT INTO Person VALUES ( Danny , Jones ) INSERT INTO Person VALUES ( Melissa , Jones ) INSERT INTO Person VALUES ( Scott , Smith ) INSERT INTO Person VALUES ( Alex , Riley ) INSERT INTO Person VALUES ( Chris , Avery ) INSERT INTO Person VALUES ( Jennifer , Avery ) INSERT INTO Person VALUES ( Bryan , Decker ) INSERT INTO Person VALUES ( Robin , Decker ) INSERT INTO Person VALUES ( Shelly , Alexander ) INSERT INTO Address VALUES ( 1213 NW 97th Ct , SQL Town , . MO , 64131 ) INSERT INTO Address VALUES ( 2721 SW 42nd Terr , Server .City , KS , 66212 ) INSERT INTO Address VALUES ( 1939 Overland St , Dell .Village , KS , 66213 ) INSERT INTO Address VALUES ( 9391 Nall Ave , Parrot .Township , MO , 64331 ) INSERT INTO Address VALUES ( 7737 Miner Dr , SQL Town , . MO , 64132 ) INSERT INTO Address VALUES ( 5334 Shamrock Ln , Orange , . KS , 66441 ) INSERT INTO PersonAddress VALUES (1, 1) INSERT INTO PersonAddress VALUES (2, 1) INSERT INTO PersonAddress VALUES (3, 2) INSERT INTO PersonAddress VALUES (4, 3) INSERT INTO PersonAddress VALUES (5, 4) INSERT INTO PersonAddress VALUES (6, 4) INSERT INTO PersonAddress VALUES (7, 5) INSERT INTO PersonAddress VALUES (8, 5) INSERT INTO PersonAddress VALUES (9, 6) INSERT INTO Sales VALUES (1, 37, 4, getdate()) INSERT INTO Sales VALUES (1, 38, 3, getdate()) INSERT INTO Sales VALUES (3, 39, 1, getdate()) INSERT INTO Sales VALUES (4, 51, 1, getdate()) INSERT INTO Sales VALUES (4, 47, 1, getdate()) INSERT INTO Sales VALUES (9, 37, 10, getdate()) INSERT INTO Sales VALUES (9, 38, 5, getdate()) INSERT INTO Sales VALUES (10, 41, 6, getdate()) INSERT INTO Product VALUES (37, Widget )

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.