|
|
|
Description
Inner joins specify how the rows from one table reference are to be joined with the rows of another table reference. Inner joins usually specify a search condition that limits the number of rows from each table reference that become part of the result table generated by the inner join operation.
If an inner join does not specify a search condition, the result table from the join operation is the Cartesian product of rows in the tables, formed by concatenating every row of one table with every row of the other table. Cartesian products (also called cross products or cross joins) are not practically useful, however, FairCom DB SQL logically processes all join operations by first forming the Cartesian products of rows from tables participating in the join.
If specified, the search condition is applied to the Cartesian product of rows from the two tables. Only rows that satisfy the search condition become part of the result table generated by the join.
A query expression can specify inner joins in either its FROM clause or in its WHERE clause. For each formulation in the FROM clause, there is an equivalent syntax formulation in the WHERE clause. Currently, not all syntax specified by the SQL-92 standard is allowed in the FROM clause.
Syntax
from_clause_inner_join ::
| FROM table_ref CROSS JOIN table_ref
| FROM table_ref [ INNER ] JOIN table_ref ON search_condition
where_clause_inner_join ::
FROM table_ref, table_ref WHERE search_condition
Arguments
FROM table_ref CROSS JOIN table_ref
Explicitly specifies that the join generates the Cartesian product of rows in the two table references. This syntax is equivalent to omitting the WHERE clause and a search condition. The following queries illustrate the results of a simple CROSS JOIN operation and an equivalent formulation that does not use the CROSS JOIN syntax:
SELECT * FROM T1; -- Contents of T1
C1 C2
-- --
10 15
20 25
2 records selected
SELECT * FROM T2; -- Contents of T2
C3 C4
-- --
10 BB
15 DD
2 records selected
SELECT * FROM T1 CROSS JOIN T2; -- Cartesian product
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
10 15 15 DD
20 25 10 BB
20 25 15 DD
4 records selected
SELECT * FROM T1, T2; -- Different formulation, same results
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
10 15 15 DD
20 25 10 BB
20 25 15 DD
4 records selected
FROM table_ref [ INNER ] JOIN table_ref ON search_condition
FROM table_ref, table_ref WHERE search_condition
These two equivalent syntax constructions both specify search_condition for restricting rows that will be in the result table generated by the join. In the first format, INNER is optional and has no effect. There is no difference between the WHERE form of inner joins and the JOIN ON form.
Equi-joins
An equi-join specifies that values in one table equal some corresponding column’s values in the other:
-- For customers with orders, get their name and order info, :
SELECT customer.cust_no, customer.name,
orders.order_no, orders.order_date
FROM customers INNER JOIN orders
ON customer.cust_no = orders.cust_no ;
-- Different formulation, same results:
SELECT customer.cust_no, customer.name,
orders.order_no, orders.order_date
FROM customers, orders
WHERE customer.cust_no = orders.cust_no ;
Self joins
A self join, or auto join, joins a table with itself. If a WHERE clause specifies a self join, the FROM clause must use aliases to have two different references to the same table:
-- Get all the customers who are from the same city as customer SMITH:
SELECT y.cust_no, y.name
FROM customer AS x INNER JOIN customer AS y
ON x.name = 'SMITH' AND y.city = x.city ;
-- Different formulation, same results:
SELECT y.cust_no, y.name
FROM customer x, customer y
WHERE x.name = 'SMITH' AND y.city = x.city ;
