Databases Reference
In-Depth Information
table. If you ran this as a RIGHT OUTER JOIN, the difference is that unquali-
fied rows in the RIGHT table (if any) would be returned by the query.
To return all rows and every possible column combination from both tables,
use a cross join. In this case, you would write the SELECT statement as:
SELECT SPNAME, CUSTNAME FROM SALESPERSON CROSS JOIN
CUSTOMER
Notice that there is no qualifying logic when you run a cross join. Rather
than qualifying the rows, you are returning all rows. Because the SALESPERSON
table has 6 rows and the CUSTOMER table has 9 rows, this query's result set
would return a total of 54 rows, every possible combination. Let's limit the result
a bit so we can see this a little easier. We're going to limit our query to the first
three customers and the first three salespersons. The query becomes:
SELECT SPNAME, CUSTNAME
FROM SALESPERSON CROSS JOIN CUSTOMER
WHERE SALESPERSON.SPNUM < 140 AND CUSTNUM < 1000
From what you just learned, you would expect the result to return nine rows,
every combination of three customers with three salespersons. When you check
the result, you find that to be true:
SPNAME CUSTNAME
Smith Main St. Hardware
Smith Jane's Stores
Smith ABC Home Stores
Potter Main St. Hardware
Potter Jane's Stores
Potter ABC Home Stores
Baker Main St. Hardware
Baker Jane's Stores
Baker ABC Home Stores
There are seldom many valid opportunities to use a cross join, especially in
a production database, though it is sometimes used as part of your analysis in
decision support databases.
7.2.3 Using Basic Subqueries
Subqueries, as mentioned earlier, are nested queries. This can occur through sev-
eral levels of SELECT statements, with each successive SELECT statement con-
tained in a pair of parentheses. We're going to limit ourselves to the easiest type
of subquery to understand, where the nested (inner) query is used to retrieve a
value that is passed up to the outer query and used as input to the SELECT
statement. This is known as a
noncorrelated subquery,
because the inner query
does not depend on the outer query.
