Datasheet

This time, the result set is different, as shown in Figure 1-11.
Figure 1-11: The result of a right outer join between Product and
ProductCategory based on ProductCategoryId columns
The additional row (row 4 in Figure 1-11) includes the data from the new column in
ProductCategory
combined with a bunch of null values in the columns taken from Product.
A full outer join’s keywords are, predictably,
FULL OUTER JOIN:
SELECT * FROM Product FULL OUTER JOIN ProductCategory
ON Product.ProductCategoryId = ProductCategory.ProductCategoryId
This gives the same result as a right outer join. It actually includes non-joined rows from both tables, but
because this applies only to
ProductCategory it is the same as a right outer join in this instance. That is
not always the case.
Outer joins of all kinds are less commonly used than inner joins, but can still be useful. As a simple
example, only by performing an outer join can you tell that there is a product category called
Extras in
the preceding results. This has important ramifications in many situations and can address issues that
could not be solved using, for instance, only inner joins.
Bear in mind that this discussion has barely scratched the surface of what is possible with select queries.
Select statements are capable of ordering data, limiting the total number of rows returned, grouping data
and performing statistical analysis on these groups, calculating columns in the result set based on col-
umn data and the results of functions, renaming columns, and much more. Again, you’ll see these fea-
tures in action later in the book.
Adding Data
After the section on retrieving data, it may come as somewhat of a relief to learn that adding, deleting,
and updating data are typically much simpler.
Adding data is achieved using the
INSERT keyword. The basic syntax of an insert query that inserts a
single row into a table is as follows:
INSERT INTO [Table] ([Column(s)]) VALUES ([Value(s)])
Here, [Table] is the table to insert into, [Column(s)] is a comma-separated list of columns to insert
data into, and
[Value(s)] is a comma-separated list of values to insert into those columns. For
example:
INSERT INTO ProductCategory (ProductCategoryId, CategoryName)
VALUES (‘3bd514c0-97a1-11da-a72b-0800200c9a66’, ‘Doodads’)
25
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 25