Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 255
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.