Joining multiple tables
two (or more) tables are joined together, typically on common fields.
Form pairs of rows by matching the contents of related columns.
Joins exercise the data relationships between tables. Are the only way
to exercise cross-table relationships.(?)


Any pair of columns from the two tables can serve as matching columns,
provided they have comparable data types but most often the common
fields are the primary key of "parent" table and foreign key in the
"child" table that references that primary key.  One-to-many (1:M)
parent/child relationship.
SELECT ... FROM parent,child WHERE parent.pk=child.fk ...
The From clause lists the tables being joined.  The Where clause
 specifies the matching condition, and possibly further restrictions.
 order of parent,child makes no difference.

SELECT p_descript,v_name FROM vendor,product WHERE vendor.v_code=product.v_code;

Any columnname that's in both tables must be qualified by tablename to
disambiguate it:  table1.fi  if there's a fi in table2 too.
Sometimes qualifiers helpful for readability.

Without a Where clause, Cartesian product is formed (all possible
combos of rows from each of the two tables). N*M combos where N and M
are number of rows of the two tables, respectively.  Not useful.
--Cartesian product of product and vendor tables:
SELECT p_descript,v_name FROM vendor,product;
--alternate syntax:
SELECT p_descript,v_name FROM vendor CROSS JOIN product;
Sometimes useful if joining tables to form all pairs of rows:...



A [natural] join is the Cartesian product with the rows that do not meet the
matching column condition removed.


--products whose vendor's order flag is 'Y':
SELECT p_code,p_descript,v_name FROM product,vendor WHERE v_order='Y' AND product.v_code=vendor.v_code;
--contacts of products that are less than $20
SELECT v_contact,p_price,p_code FROM vendor,product WHERE vendor.v_code=product.v_code AND p_price<20;
--cheapest product from Tennessee
SELECT Min(p_price) FROM vendor,product WHERE vendor.v_code=product.v_code AND v_state='TN';
--products per state:
SELECT v_state,count(*) FROM product,vendor WHERE
product.v_code=vendor.v_code GROUP BY v_state;
--number of products each vendor supplies
SELECT v_name,Count(*) FROM vendor,product WHERE vendor.v_code=product.v_code GROUP BY v_name ORDER BY Count(*);


SQL/92 alternate syntaxes:
--join on (all) column(s) of same name and datatype:
SELECT p_descript,v_name FROM vendor NATURAL JOIN product;
--(if no common column, is cartesian product!)

SELECT p_descript,v_name FROM vendor INNER JOIN product ON vendor.v_code=product.v_code;
--if joining columns have same name: (equijoin implied):
SELECT p_descript,v_name FROM vendor INNER JOIN product USING (v_code);
--v_code can not be table aliased in Using or Select clause

****************************************
"outer join": mismatches (rows in one table not matched by any row in
the other table) are included too.  superset of inner join.
SELECT ... FROM t1 LEFT JOIN t2 ON fi=fj...
  fi is column in t1, fj is column in t2

left table t1 is reference table; output is produced for each of its
rows, those with matching row(s) in the right table (the same as inner
join) and those with no matching row(s) in the right table t2 and in
which all selected columns of t2 will be displayed as NULL.

SELECT p_code,p_descript,v_name FROM product LEFT JOIN vendor ON product.v_code=vendor.v_code;

To find only the mismatches, use Where clause to restrict result to
only those rows that have Nulls in right table's column(s):
SELECT p_code,p_descript,v_name FROM product LEFT JOIN vendor ON product.v_code=vendor.v_code  WHERE v_name IS NULL;

Parent rows that are not matched by any child: i.e. childless parents.
SELECT parentFields FROM parent LEFT JOIN child ON pk=fk WHERE childField IS NULL;
(parent parent child child)
--vendors that aren't supplying any products now:
SELECT vendor.v_code,v_name FROM vendor LEFT JOIN product ON vendor.v_code=product.v_code WHERE p_descript IS NULL;

can be done with subquery instead of join: Not In returns false if any
Nulls, so have it return 0s for nulls:
SELECT v_code,v_name FROM vendor WHERE v_code NOT IN (SELECT Nvl(v_code,0) FROM product);
#note that qualifying code not needed because only vendor table is used in outer select.

**************
Another use of outer join:
Find referential integrity problems of imported data: left table's
foreign key does not match primary key of any row of right table, (or FK
is Null).
i.e. orphan child.

SELECT childFields FROM child LEFT JOIN parent ON fk=pk WHERE parentField IS NULL;
(child child parent parent)

SELECT p_code,p_descript FROM product LEFT JOIN vendor ON product.v_code=vendor.v_code WHERE v_name IS NULL;
-- AND product.v_code IS NOT NULL  to eliminate Null FKs



alternate syntax:
SELECT p_code,p_descript,v_name,v_code FROM product LEFT JOIN vendor USING(v_code);
--note: join column can not be qualified

full join: both left and right joins: null in either column
SELECT p_code,p_descript,v_name FROM product FULL JOIN vendor ON product.v_code=vendor.v_code;
SELECT p_code,p_descript,v_name FROM product FULL JOIN vendor USING(v_code);

**************
self-join (recursive join): table joined with itself.  need table alias[es].
SELECT ... FROM t1 AS alias1,t1 AS alias2 WHERE alias1.pk=alias2.fk...
--each employee's manager's name
SELECT e.employee_id,e.last_name,e.manager_id,m.last_name FROM employees e,employees m WHERE e.manager_id=m.employee_id ORDER BY e.manager_id ;

alternate syntaxes:
Select e.employee_id,e.last_name,e.manager_id,m.last_name From employees e INNER JOIN employees m ON e.manager_id=m.employee_id;