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.