Does it matter which pub_id you select, as they are the same? No, there's no difference in the results.
I wrote the equijoin and natural join in the ANSI syntax, but I could have written them in the T-SQL syntax. I recommend the ANSI syntax because it is the preferred syntax for SQL Server 7.0 and has a better probability of working on other relational database management systems (RDBMSs).
Cross Joins
I recommend you avoid the CROSS JOIN, or the Cartesian product. First, the output is almost always useless, and second, this join can bring the server to a grinding halt. As an example, if you run two queries on the titles and publishers tables, you find that these tables have 18 and 8 rows, respectively. The cross join gives you 8*18, or 144, rows, as you see in Screen 3. It shows every possible combination of publisher and title, implying that every publisher published every book. Here are the queries to run to test this for yourself. Here's the ANSI join syntax:
SELECT pub_name, title
FROM titles CROSS JOIN publishers
(gets 8x18 = 144 rows)
and the T-SQL join syntax:
SELECT pub_name, title
FROM titles, publishers
(gets 8x18 = 144 rows)
Imagine that you ran a CROSS JOIN query on a table with 1000 rows, such as a list of the top 1000 best-selling authors, with a 2000-row table containing a list of best-selling books. You might expect 2000 rows of output, each listing a book and its author, but instead you will see 2 million rows.
Here's one reason why the ANSI syntax is gaining favor. In the ANSI syntax, users have to type CROSS to run a CROSS JOIN. In the T-SQL syntax, they simply omit the WHERE clause specifying how to join the tables. This omission can happen accidentally, if the users select only the first two lines of a query before running it. A programmer can make a similar mistake, intending to test the query first, and then specify the WHERE clause to restrict the output rows.
Should you always avoid CROSS JOINS? No, they can be useful. If you need to generate a lot of test data quickly, these joins work well. For example, suppose you build a table with 100 last names and another table with 100 first names. Now do a CROSS JOIN. Instantly, you have 10,000 customers with every possible combination of first and last names. Just be careful when you use CROSS JOINS.
Outer Joins
You create an OUTER JOIN when you ask for all the data from one table, and then the corresponding records from a second table. The titles and publishers example would work here, because each title has a publisher, but not every publisher has published at least one title. But let's consider another example, where some books are selling well and others are not selling at all. The business needs to know which books are not selling. So in this example, consider the titles and sales tables, as shown in Screen 4. Here's the ANSI syntax:
SELECT substring (title,1,40) AS Book, stor_id,
ord_num, qty, ord_date
FROM titles LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
and the T-SQL syntax (note the *= for the left OUTER JOIN):
SELECT substring (title,1,40) AS Book, stor_id,
ord_num, qty, ord_date
FROM titles, sales
WHERE titles.title_id *= sales.title_id
These examples use the SUBSTRING function (which I discussed in "Working with Character Data," May 1999) to shorten the displayed output for the title table, and I relabeled the column as Book. The ANSI syntax is clearer. It says LEFT OUTER JOIN. You can just specify LEFT JOIN. It means, "Give me all the data from the table to the left of the JOIN (in this case, titles) and the matching data from the table to the right of the JOIN (in this case, sales)." So you see all the titles, including those with no sales. Where sales data exists for a title, it is output. Where no sales data exists, you see NULLs.
Prev. page
1
[2]
3
next page 
