JoinsThere are three basic join types. An inner join shows results only where there are matches between the elements. In other words, if you were querying the database and only wanted to see the people who have addresses, you'd use an inner join. An inner join will leave out all records that don't have a match. An outer join can show all records from one side of the relationship, and records that match if available from the other side and NULL values for records that do not have a match. An outer join between the Person and Sales table could show you each person and the amount of purchases they've made, and also show NULL values for people who haven't made purchases yet, but are in the table. 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 a join. No correlation between the two records is attempted ”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 returnset, or in intermediate sets used by SQL Server, can get out of hand quickly, causing server-wide performance degradation. Using GROUP BYThe GROUP BY operator creates subgroups within a query that can be used by aggregate functions. For example, look at the Sales table. Right now, the Sales table has one record for each sale, but you really want to know how many total items each person bought. Here's how GROUP BY will solve that problem: SELECT PersonID, sum(QtyPurchased) as TotalQtyPurchased FROM Sales GROUP BY PersonID There are, of course, some rules associated with the use of GROUP BY within a query:
Using GROUP BY and HAVINGThe HAVING keyword appears after GROUP BY in a SELECT statement, and it is used to filter data after the GROUP BY . You can actually use HAVING without using GROUP BY , in which case HAVING will act like a part of the WHERE clause. The HAVING operator is typically used with aggregate functions to filter out rows that don't meet certain criteria. Using COMPUTE and COMPUTE BYSo far, the operators you've looked at do not ever create data rows. That changes now. The COMPUTE and COMPUTE BY operators create summary data across your aggregates and add it into your rowset. There are the inevitable rules, however:
Using OPENROWSET and OPENQUERYThe OPENROWSET function returns a rowset to SQL Server. It's used in a SELECT statement in the same place as a table. Here are the parameters for the OPENROWSET function:
OPENQUERY allows for the execution of a pass-through query to be performed on a linked server. The linked server can be any OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query in the same manner as the use of a table name. The OPENQUERY function can also be used as a target for an INSERT , UPDATE , or DELETE statement. The syntax for its use is OPENQUERY (linked_server,'query') where linked_server is the name of the linked server and 'query' is the string executed. Linked ServerYou can think of a linked server as a prebuilt set of arguments for OPENROWSET stored in a database object. You can just create an object called a linked server, and create it with all the attributes that you'd normally use in a call to OPENROWSET . Then, instead of having to type in all those parameters over and over again, you can just use the linked server. When a linked server is used, it is important to remember the four-part naming of objects in queries: server.database.owner.object . XMLXML is breaking into the Internet, setting a new data standard and providing another mechanism of getting data from point A to point B. SQL Server's use of XML is discussed in Chapter 5. To put data into XML format, the SELECT statement includes an operator called FOR XML . This specifies to SQL Server that instead of returning a rowset, it should return an XML document. There are three different options for generating the XML: RAW , AUTO , and EXPLICIT . In AUTO mode, SQL Server returns the rowset in an automatically generated nested XML format. If the query has no joins, it doesn't have a nesting at all. If the query has joins, then it returns the first row from the first table, and then all the correlated rows from each joined table as a nested level. What if you don't want XML that is elaborately formatted? You then can use the RAW mode. RAW mode returns each row as an element with the identifier "row" . Another way to extract data with the FOR XML clause is by using the option EXPLICIT . The EXPLICIT option enables you to specify the format of the XML that will be created. To define the format, you have to alias the first column of output to the name Tag , name the second column Parent , and then each consecutive column has to be aliased to relate it to a specific Tag . The column names after Parent are named like this: [ ElementName ! TagNumber ! AttributeName ! Directive ] , complete with the square brackets around the alias. (See Table 4 for a list of directives.) Table 4. Directives for Use in XML Column Aliases
The OPENXML function works similarly to how OPENROWSET and OPENQUERY work in that it returns a rowset that can be used in a SELECT statement. The full XML process is defined in the following list:
Data Transformation ServicesSQL Server 2000 provides a great tool that will import and export data for you, called the Data Transformation Services Import/Export Wizard. This tool uses SQL Server DTS to copy data into and out of SQL Server using nice, easy-to-understand graphical tools. |