Java Reference
In-Depth Information
Figure 9-7:
Full Outer Join
The preceding examples have illustrated the use of
JOINS
to find records from two tables with some
degree of commonality. The
next section
discusses how to obtain result sets which specifically exclude
those matching selected criteria.
Using NOT EXISTS
Now you know how to use
INNER JOINS
to find records from two tables with matching fields and how
to use
OUTER JOINS
to find all records, matching or nonmatching. Next, consider the case where you
want to find records from one table that don't have corresponding records in another.
Using the Customers and Orders Tables again, find all the customers who have not placed an order.
The way to do this is to find customer records with customer numbers that do not exist in the Orders
Table. Do this by using
NOT EXISTS
, as shown in
Figure 9-8
.
Figure 9-8:
Using NOT EXISTS to find records in one table with no corresponding entry in another
table.
SELECT c.Last_Name + ', ' + c.First_Name AS Customer
FROM CUSTOMERS c
WHERE NOT EXISTS
(SELECT *
FROM orders o
WHERE o.customer_number = c.customer_number);
In addition to joining tables to each other, it is sometimes useful to join a table to itself. The
next section
discusses how and why you would perform a Self-Join on a table.
Using Self-Joins
