Joins and Compound Queries - Java Database Programming Bible - page 275

The result, as it stands, is not quite what you are looking for, in that Kay Adams appears twice because
she bought cookies twice. The solution is to insert the keyword DISTINCT into the SELECT clause,
telling the SQL engine to return only one instance of each record, as shown in Figure 9-4 .
Figure 9-4: Using DISTINCT to eliminate duplicate records
Using Non-Equi-Joins
The Joins used up to this point have all been Equi-Joins, or Joins where the values of the keys used to
make the join have been equal to each other. However, it seems reasonable that you should be able to
do Non-Equi-Joins or Joins where the relationship is not equal. For example, since there are only two
orders in the Orders Table used in the previous example, you can get the other order using the
Non-Equi-Join. Here's an example:
SELECT c.Last_Name + ', ' + c.First_Name AS Customer, oi.Qty,
i.Name, i.Description, i.Cost * 1.6 AS Price_Each,
i.Cost * 1.6 * oi.Qty AS Price
FROM Orders o, Customers c, Ordered_Items oi, Inventory i
WHERE o.Order_number = oi.Order_number AND
c.Customer_Number = o.Customer_Number AND
i.Item_Number = oi.Item_Number AND o.Order_number <> 2;
Using Outer Joins
The Joins discussed so far have been Inner Joins . An Inner Join is a Join between two tables. An inner
Join includes only rows with matching rows in the both tables. A set oriented way of visualising Joins
was shown in Figure 9-1 . Another easy way to visualize this is by drawing a diagram like Figure 9-5 ,
where the Customer_Number columns in the Customers and Orders Tables intersect in the shaded
area to identify an Inner Join.