SQL Server Magazine

One reason why DBAs often use relational (i.e., related) databases is that they avoid data duplication and hence save disk space. In relational databases, you use identifiers for items when possible. That way, you need to record the items’ textual descriptions in only one table. When you need to refer to an item in another table, you simply specify that item’s identifier. For example, suppose you have a database of movie data. In the database, you have a table named Genre that lists genre textual descriptions (e.g., Comedy, Documentary) and their identifiers (e.g., 1, 2). You now want to include a table similar to the MovieReview table in Lesson 4. The MovieReview table includes a column named Genre, which contains genre textual descriptions. Rather than list the genre textual descriptions, you can use the genre identifiers provided in the Genres table. When you need to display data from the MovieReview table with the genre textual descriptions in the Genre table, you can use a JOIN clause to join the tables.

JOIN clauses, which were first incorporated into SQL Server 7.0, let you build queries that retrieve data from two related tables. Prior to SQL Server 7.0, you had to join tables using a technique that consisted of listing the tables in a FROM clause and joining them in a WHERE clause that used special notations to represent different types of joins. SQL Server 2005 still lets you use the old join technique. However, future versions of SQL Server might not support it. Thus, you should learn how to use the JOIN clauses, no matter whether you’re first learning how to join tables or have joined tables for years using the old technique.

There are five types of JOIN clauses available in T-SQL:

•INNER JOIN
•LEFT OUTER JOIN
•RIGHT OUTER JOIN
•FULL OUTER JOIN
•CROSS JOIN

At first glance, these names might seem confusing. To help you understand the differences and which type of join is suitable for which type of queries, I’ll walk you each type of join. But first, you need to create and populate two tables so that you can follow along with the examples.

The Prerequisites
The examples in this lesson rely on objects in the MyDB database, which was created in Lesson 2. If you didn’t previously create the MyDB database, you’ll need to do so. Go to “T-SQL 101, Lesson 2” (April 2008, InstantDoc ID 98105) to get the code and instructions.

You also need to create two new tables: Movie and Genre. You can create and populate these tables by following these steps:

1. Download the CodeToCreateMovie&-GenreTables. sql and CodeToPopulate- Movie&GenreTables.sql files. Go to www.sqlmag .com, enter 99137 in the InstantDoc ID text box, click Go, then click the 99137.zip hotlink.

2. Create the Movie and Genre tables. Open SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer and copy the code in CodeToCreateMovie&GenreTables.sql into the query window. Execute the code.

3. Populate the Movie and Genre tables by executing the code in CodeToPopulateMovie&GenreTables .sql in SSMS or Query Analyzer.

The INNER JOIN Clause
An inner join is the most common type of join between two tables. A column or columns in the first table are compared to a column or columns in the second table using a comparison operator such as the equals (=) operator. Whenever a match is found, the rows from the first table are connected to the rows in the second table. Unmatched rows are discarded. The structure you end up with after the join is almost a new table unto itself. It will contain the selected columns from both tables. For example, if you have Table1, which has ColumnA and ColumnB, and Table2, which has ColumnC, an inner join will produce a structure with ColumnA, ColumnB, and ColumnC. Whether the individual rows contain data depends on the result of the join.

Let’s explore inner joins further with the Genre and Movie tables. To begin, execute the code

SELECT * FROM Genre
SELECT * FROM Movie

As Figure 1 shows, both tables have a GenreID column, which makes them an excellent join candidate. Each GenreID in the Movie table points to the appropriate genre record in the Genre table. (Note that there is one GenreID—10—in the Movie table that doesn’t have a corresponding record in the Genre table. This is intentional and will be used for demonstration purposes in “The OUTER JOIN Clauses” section.)

To create a list of movies sorted by their genres, you can use the INNER JOIN clause to join the Movie table to the Genre table in code such as

SELECT g.Genre, m.Movie
  FROM Movie m
    INNER JOIN Genre g
    ON g.GenreID = m.GenreID
ORDER BY g.Genre, m.Movie

Let’s walk through this code line by line. The first line selects the columns you want to see in the result set, which in this case are the Genre column from the Genre table and the Movie column from the Movie table. Notice that the column names are prefixed with the letters g and m. These are table aliases. The alias for each table is specified in the FROM clause immediately following the table name and usually consists of a shortened version of the table name. In this case, m is the alias for Movie and g is the alias for Genre.

Aliases are used in this query because both tables have columns named GenreID. Alternatively, you could use the TableName.ColumnName naming convention (e.g., Movie.GenreID, Genre.GenreID) when specifying the columns, but I find this approach a bit long winded, especially when referencing tables with relatively long names. It really comes down to personal preference, so feel free to use either method.

Continue on Page 2

   Prev. page   [1] 2 3     next page