Here you run into something called a RIGHT OUTER JOIN. In the example above, no sales data could appear if no entries were in the titles table. Thus, if you just change LEFT to RIGHT and run the query, you see what is really an INNER JOIN. But if you flip the tables
SELECT substring (title,1,40) AS Book, stor_id,
ord_num, qty, ord_date
FROM sales RIGHT OUTER JOIN titles
ON sales.title_id = titles.title_id
you have a RIGHT OUTER JOIN. In other words,
titles LEFT JOIN sales
produces the same results as
sales RIGHT JOIN titles
In fact, internally, SQL Server doesn't process RIGHT JOINS. It just flips them around and handles them as LEFT JOINS.
The T-SQL syntax is subtler. Note the asterisk before the equals sign in the WHERE clause. In a SELECT clause, the asterisk means, "Get everything," so the *= means, "Get everything from the table on the left of the equals sign and the matching data from the right": a LEFT OUTER JOIN, in other words. What does the =* mean? It's a RIGHT OUTER JOIN, which returns all the rows from the table to the right of the join and only the matching rows from the table on the left.
ANSI join syntax lets you specify a type of join that wasn't possible in the old-style syntax, the full OUTER JOIN. It does exactly what you would expect if you combined the LEFT and RIGHT OUTER JOINS. It returns all the rows in each table, showing the matching data where appropriate, and filling in with NULLS whenever no match occurs in the other table. An example is joining the titles and sales tables to find all book sales, plus all books that are not selling, and all stores that have not sold any books. Note that you must write the full OUTER JOIN using the ANSI syntax. T-SQL has no equivalent.
Nulls in Outer Joins
Here's one more reason to adopt the ANSI syntax for your joins. When you use an OUTER JOIN, some rows show null for the data from the inner table. The question is whether this null is really data from the inner table or whether the join inserted it. So when you put in a condition WHERE xxx IS NULL, the T-SQL query returns contradictory or inaccurate results. Run the first query below, but leave out AND qty IS NULL.
SELECT Book = substring (title,1,40), stor_id,
ord_num, qty, ord_date
FROM titles, sales
WHERE titles.title_id *= sales.title_id
AND qty IS NULL
You will see a couple of books with no sales. Now try to query for just those books by including the search condition. T-SQL syntax produces unexpected results, as you see in Screen 5. ANSI syntax works correctly:
SELECT Book = substring (title,1,40), stor_id,
ord_num, qty, ord_date
FROM titles LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
WHERE qty IS NULL
These two queries illustrate the fundamental problem with the T-SQL query syntax, which the ANSI syntax resolves. The WHERE clause is doing double duty. It specifies the JOIN columns, and it acts as the search condition. In the ANSI syntax, you specify the join as a JOIN, and the WHERE clause acts only as a search condition to limit which rows are returned. In the examples in this article, the ANSI query usually doesn't even have a WHERE clause, but the T-SQL query always has this clause whenever two or more tables are joined.
Self Joins
Do you ever get multiple copies of a mailing, and the only difference is a small change in the spelling of your name? How can you prevent this scenario from happening in your database? Look for similar records by using a SELF JOIN, which joins the table to itself and looks for similar data in each copy of the table. As Screen 6 shows, the query finds authors with the same last name. Here's the ANSI join syntax:
SELECT au1.au_fname, au1.au_lname,
au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
ON au1.au_lname = au2.au_lname
WHERE au1.city = 'Salt Lake City'
AND au1.state = 'UT'
AND au1.au_id < au2.au_id
T-SQL syntax (the JOIN condition is mixed in with the WHERE conditions):
SELECT au1.au_fname, au1.au_lname, au2.au_fname,
au2.au_lname
FROM authors au1, authors au2
WHERE au1.city = 'Salt Lake City'
AND au1.state = 'UT'
AND au1.au_lname = au2.au_lname
AND au1.au_id < au2.au_id
The last line, stating that the author IDs must be different, simply stops matching each author's name with itself. I used a less-than symbol, rather than a not-equals symbol, because otherwise the results show both matches—Albert Ringer to Anne Ringer and vice versa. You must alias the table, so that the query can handle references to what now appear to be two distinct tables.
End of Article
Prev. page
1
2
[3]
next page -->
