Q&A

Q What are the benefits to using ANSI or old style join syntax?

A The ANSI syntax can prevent you from making a simple mistake. You cannot accidentally create a cross join when you wanted an inner join, as you can do with the old style syntax. ANSI style syntax makes the relationships between tables easier to understand because it is explicitly spelled out for someone viewing the code. Also, it requires you to specify join keys together with the tables to which they belong (instead of separately, like with the old style). Finally, the ANSI join syntax is defined in the ANSI SQL standard. If standards compliance is important to you and your company, this style is guaranteed to be supported by MS SQL Server in future releases.

The old style syntax is less verbose than the ANSI style. It is more portable between RDBMS’s because more of them recognize the old style. If you are using a combination of MS 6.0 and 6.5 servers, the old style syntax will work on both, but the ANSI syntax won’t. The old style syntax is also more recognizable by a wider range of people in the industry (it has been around longer), so you will have an easier time getting help if you use it.

There is no performance benefit to using one or the other join syntax.

Q Does the order of the tables in the FROM clause affect performance?

A The short answer is “No.” MS SQL 6.5 was improved to deal with multitable queries in a better way than ever before. In previous versions, some queries that referenced over four tables could, in rare cases, miss a possibility for better join performance.

Q The ER diagram indicates a one-to-many relationship, and a one-to-one, but how about one to “zero or more” or a “one to zero or one” relationship? Aren’t these different?

A Some diagrams do consider these types of relationships and use zero to indicate them. Others lump these relationships in with “many.” For example, authors has a one-to-many relationship with titleauthor which is really a “one to zero or more” relationship. Diagrams that present this information do, in fact, present clearer images of the data relationships between tables.

Q Do the columns composing the common key need to have the same name?

A No, although they frequently do. It can be confusing when a customer number is called cust_num in the invoice table and cust_id in the customer table.

Q Do joins need to use the equals sign?

A No. You can join tables on any mathematical operator (<, >, <>, and so on). This can lead to some bizarre results. For example, what would you get if you asked for “all the rows in titles where the title_id doesn’t match the title_id in sales”—a lot of useless output! Still, these operators are used in some unusual circumstances.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  Identify the primary keys in authors, titleauthor, and titles using either sp_help output or the entity relationship diagram presented earlier.

2.  Using the entity relationship diagram, identify the type of relationship between titles and sales.

3.  Assume you have a customer table with a primary key on cust_id and an invoices table with a foreign key on cust_id to indicate which customer is responsible for the invoice. Considering the nature of customers and invoices, how would you describe the relationship between customer and invoices?

Exercises

1.  Print a list of employees along with their job description. Order the list by max_lvl in jobs, so the highest-paying jobs are at the end of the list.

2.  Limit the list to employees who have job levels over 200. Do not order this result set.

3.  Further limit this list to include only employees who work in the USA.

4.  Produce a list of primary authors’ names. (Hint: A primary author is one who has a 1 in titleauthor.au_ord.)

5.  Generate a list of authors’ names (last and first) along with the number of books that they have sold. Order the results from most books sold to least.