Inner Joins
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 ;
Outer Joins
Description
An outer join between two tables returns more information than a corresponding inner join. An outer join returns a result table that contains all the rows from one of the tables even if there is no row in the other table that satisfies the join condition.
In a left outer join, the information from the table on the left is preserved: the result table contains all rows from the left table even if some rows do not have matching rows in the right table. Where there are no matching rows in the left table, FairCom DB SQL generates null values.
In a right outer join, the information from the table on the right is preserved: the result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the right table, FairCom DB SQL generates null values.
FairCom DB SQL supports two forms of syntax to support outer joins:
- In the FROM clause, specify one of the outer join clauses between two table names, followed by a search condition:
LEFT OUTER JOIN
RIGHT OUTER JOIN
The search condition can contain only the join condition between the specified tables.
- In the WHERE clause of a query expression, specify the outer join operator (+) after the column name of the table for which rows will not be preserved in the result table. Both sides of an outer-join search condition in a WHERE clause must be simple column references. This syntax allows both left and right outer joins.
FairCom DB SQL does not support full (two-sided) outer joins.
Syntax
from_clause_inner_join ::
FROM table_ref LEFT OUTER JOIN table_ref ON search_condition
| FROM table_ref RIGHT OUTER JOIN table_ref ON search_condition
where_clause_inner_join ::
WHERE [table_name.]column (+) = [table_name.]column
| WHERE [table_name.]column = [table_name.]column (+)
Examples
The following example shows a left outer join. It displays all the customers with their orders. Even if there is not a corresponding row in the orders table for each row in the customer table, NULL values are displayed for the orders.order_no and orders.order_date columns.
SELECT customer.cust_no, customer.name, orders.order_no,
orders.order_date
FROM customers, orders
WHERE customer.cust_no = orders.cust_no (+) ;
The following series of examples illustrates the outer 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
-- Left outer join
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C3;
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
20 25
2 records selected
-- Left outer join: different formulation, same results
SELECT * FROM T1, T2 WHERE T1.C1 = T2.C3 (+);
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
20 25
2 records selected
-- Right outer join
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C3;
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
15 DD
2 records selected
-- Right outer join
SELECT * FROM T1, T2 WHERE T1.C1 (+) = T2.C3;
C1 C2 C3 C4
-- -- -- --
10 15 10 BB
15 DD
2 records selected