Join Conditions - Microsoft SQL Server Black Book pdf

Now we’ll need to put some information together from more than one table so that the title ID can show the name and author for each sales report line in our new report. We’ll use the same basic query from above and add a join

condition to it so that the server connects the data. See Listing 5.10 for the new query and the sample output.

Microsoft SQL Server supports ANSI join syntax and Transact-SQL syntax (Microsoft SQL Server extended SQL). Use of inner, cross, and outer joins is supported fully on Microsoft SQL Server. The syntax for ANSI joins is slightly different than for Transact-SQL, so a trip to Microsoft SQL Server Books Online would be prudent. Transact-SQL uses join operators instead of actual text to perform the join in a WHERE clause. In the following examples, Transact-SQL methods are used.

Query 1 in Listing 5.10 displays what is in the Titleauthor table. This data is a collection of key values used to join together related pieces of information.

Not all databases need this type of table, but good relational database design will produce a similar table in most situations.

Query 2 joins the Sales, Titleauthor, and Author tables. You specify the tables in a comma-delimited list in the FROM clause. In the SELECT list, you use the table.column reference to specify each column independently of the others.

This allows you to use data from many tables and present it to the user as if it were from one table.

Hiding the complexity of the database structure is very important to your users. If a column name is unique to all tables involved in a query, such as qty in the Sales table, you can list the column without the table reference. The Optimizer will not choke on it because it knows there is only one qty column in all three tables.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch05/151-154.html (3 of 3) [1/27/2000 6:16:08 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

I use the table.column reference in all my join queries to keep myself in check and to track which column comes from which table.

Listing 5.10 Sales by author join example.

/* Query 1 titleauthor table */

SELECT *

FROM titleauthor ORDER by title_id

/* Query 2 Volume sales > 20 by author */

SELECT 'Author' = UPPER(SUBSTRING(authors.au_fname,1,1)) + '. ' + SUBSTRING(authors.au_lname,1, 15), 'Total Sales' = SUM(sales.qty)

FROM sales, titleauthor, authors

WHERE sales.title_id = titleauthor.title_id AND authors.au_id = titleauthor.au_id GROUP BY authors.au_fname, authors.au_lname HAVING SUM(sales.qty) > 20

ORDER BY SUM(qty) DESC

/* Query 3 Volume sales > 20 by author */

SELECT 'Author' = UPPER(SUBSTRING(a.au_fname,1,1)) + '. ' + SUBSTRING(a.au_lname,1, 15), 'Total Sales' = SUM(s.qty)

FROM sales s, titleauthor ta, authors a WHERE s.title_id = ta.title_id

AND a.au_id = ta.au_id

GROUP BY a.au_fname, a.au_lname

http://www.itknowledge.com/reference/standard/1576101495/ch05/154-158.html (1 of 4) [1/27/2000 6:16:09 PM]

Go!

Keyword

---Go!

HAVING SUM(s.qty) > 20 ORDER BY SUM(s.qty) DESC Output Query 1

Notice that in the WHERE clause of these queries I have had to place a join condition for each of the tables in my list. The Sales table has a title_id column that matches a title_id column in the

http://www.itknowledge.com/reference/standard/1576101495/ch05/154-158.html (2 of 4) [1/27/2000 6:16:09 PM]

Titleauthor table. By saying that these two columns are equal, you allow the server to add the information in the Titleauthor table to the list of available columns you can choose from. The Titleauthor table has an au_id column that corresponds to the au_id column of the Authors table.

A good rule of thumb is that for each table that you wish to join, there should be one join condition in the WHERE clause.

NOTE: The formula N = T –1 should come to mind when joining tables. T is the number of tables in your FROM clause and N is the number of join conditions you should have. The wrong number of join conditions can return incorrect result sets to the user.

Aliases

In Query 3 of Listing 5.10, I have introduced aliases to each of the table names in the FROM clause. I do not have to do this, but it prevents me from typing things over and over again. In addition, aliases enhance readability of the code.

Notice in Query 2 the long lines of code, with the table reference repeated many times.

Repeating the table name is not bad; it’s just costly in the amount of typing you must do. Queries 2 and 3 return the same results, yet Query 3 is about 55 characters shorter. This might seem insignificant, but when you have to pass queries over a network or modems many times during the running of an application, it can save time in the communications between client and server.

(I do not typically pass a query string from client to server; instead, I use stored procedures, which keep the requests as short as possible. See Chapter 6 for more on stored procedures and their benefits.)

To use aliases, add a space after each of the tables in the FROM clause and the character or characters you wish to refer or connect to each table. The Query Optimizer checks for the alias in the FROM clause first; then it uses those aliases to parse the SELECT list and WHERE clauses, respectively.

In document Microsoft SQL Server Black Book pdf (Page 140-143)