Joins,
Peter Komisar © Conestoga College version 1.2

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC Publi.
Core MySQL, Leon Atkinson, Prentice-Hall Publishing, 'Joins Note,
http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html

MySQL Character Set Support Example

mysql> show character set;

+----------+-----------------------------+---------------------+
| Charset  | Description                 | Default collation   |
+----------+-----------------------------+---------------------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |
| dec8     | DEC West European           | dec8_swedish_ci     |
| cp850    | DOS West European           | cp850_general_ci    |
| hp8      | HP West European            | hp8_english_ci      |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |
| latin1   | cp1252 West European        | latin1_swedish_ci   |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |
| ascii    | US ASCII                    | ascii_general_ci    |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |
| cp1250   | Windows Central European    | cp1250_general_ci   |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |
| cp866    | DOS Russian                 | cp866_general_ci    |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |
| macce    | Mac Central European        | macce_general_ci    |
| macroman | Mac West European           | macroman_general_ci |
| cp852    | DOS Central European        | cp852_general_ci    |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |
| cp1256   | Windows Arabic              | cp1256_general_ci   |
| cp1257   | Windows Baltic              | cp1257_general_ci   |
| binary   | Binary pseudo charset       | binary              |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |
+----------+-----------------------------+---------------------+

Sometimes these encoding will be used with File loads.
Notice the values that are used are listed in the first column.

Example

LOAD DATA INFILE "C:\\ONMgr.txt" INTO TABLE
mgr_contacts CHARACTER SET 'latin1';

Example

CREATE TABLE mgr_contacts(
MANAGER_ID int(11),
NAME varchar (40),
STREET varchar(40),
CITY varchar(30),
PROVINCE char(2),
POSTAL_CODE char(6),
AREA_CODE char(3),
PHONE_NUMBER char(7)
);

Execution

mysql> CREATE TABLE mgr_contacts(
-> MANAGER_ID int(11),
-> NAME varchar (40),
-> STREET varchar(40),
-> CITY varchar(30),
-> PROVINCE char(2),
-> POSTAL_CODE char(6),
-> AREA_CODE char(3),
-> PHONE_NUMBER char(7)
-> );

Query OK, 0 rows affected (0.17 sec)

The new table is populated the long way with
a series of inserts.

Insert Statements

INSERT INTO mgr_contacts values(
1105,'Allen Jackson','41 Memorial Ave.','Orillia','ON','KOJ1K0','705','71
74448');

INSERT INTO mgr_contacts values(
1011,'Wally Chester','2044 Beverly St.','Aylmer','ON','L2J4T0','613','94
27456');

INSERT INTO mgr_contacts values(
2019,'Gordon Teasdale','21-B Jelly Ave.','Shelburne','ON','J9K1B0','519',
'9392565');

INSERT INTO mgr_contacts values(
2001,'Alison Kettle','101 Lakeshore Ave.','Meaford','ON','L1T2D8','705','
7675554');

INSERT INTO mgr_contacts values(
9001,'Maynard Whistle','1001 MacKenzie Ave.','Richmond Hill','ON','905','
4350701');

INSERT INTO mgr_contacts values(
-> 8786,'Wesley East','44 King St.W.','Hamilton','ON','H2T2W3','905','345234
3');

Following are some of the rows of the table with the
data entered so far. The managers do not always live
in the same cities as their stores. The last two of these
managers do not have stores at this time.

SELECTION ON the mgr_contacts Table after INSERTS

mysql> select manager_id,name,city, area_code, phone_number from mgr_contacts;

+------------+-----------------+-----------+-----------+--------------+
| manager_id | name            | city      | area_code | phone_number |
+------------+-----------------+-----------+-----------+--------------+
| 1011       | Wally Chester   | Aylmer    | 613       | 9427456      |
| 1012       | Betty Davis     | Toronto   | 416       | 6544078      |
| 1105       | Allen Jackson   | Orillia   | 705       | 7174448      |
| 2001       | Alison Kettle   | Meaford   | 705       | 7675554      |
| 2019       | Gordon Teasdale | Shelburne | 519       | 9392565      |
| 8786       | Wesley East     | Hamilton  | 905       | 3452343      |
| 7767       | Anwar Sudat     | Barrie    | 705       | 2457343      |
+------------+-----------------+-----------+-----------+--------------+

6 rows in set (0.00 sec)

JOIN Examples

Essentially a JOIN is a SELECT on more than one tables.
Consider a problem whose answer takes data from both
tables.  We would like to know individuals who are associated
with a store in one city but who lives in another city.

Example

SELECT DISTINCT mgr_contacts.name from mgr_contacts, store
where store.city !=mgr_contacts.city;

Execution

mysql> SELECT mgr_contacts.name from mgr_contacts, store
    -> where store.city=mgr_contacts.city;
+-------------+
| name        |
+-------------+
| Betty Davis |
| Betty Davis |
| Anwar Sudat |
+-------------+
3 rows in set (0.00 sec)

The DISTINCT keyword can hide duplications.

Execution

mysql> SELECT DISTINCT mgr_contacts.name from mgr_contacts, store
    -> where store.city=mgr_contacts.city;
+-------------+
| name        |
+-------------+
| Betty Davis |
| Anwar Sudat |
+-------------+
2 rows in set (0.00 sec)

In the following query, store number and manager ids
are taken from the store table, and names are taken
from the mgr_contact table based on equating the
manager id in each table.

These are the names of the managers in the mgr_contact
table that have manager_ids associated with stores.

Example

Notice we only entered managers from Ontario. We would
have all the store managers names if we had entered them
into our table.

INNER JOINS

The above example where a condition is used to
specified a value that is found in both tables is called
an 'inner join'.

"INNER JOIN and , (comma) are semantically equivalent
in the absence of a join condition.

The above statement could have been made explicitly
a JOIN as follows.

Example

SELECT DISTINCT store.store_number,store.manager_id,
mgr_contacts.name from store INNER JOIN mgr_contacts
ON mgr_contacts.manager_id=store.manager_id;

Example

mysql> SELECT DISTINCT store.store_number,store.manager_id,
    -> mgr_contacts.name from store INNER JOIN mgr_contacts
    -> ON mgr_contacts.manager_id=store.manager_id;

+--------------+------------+-----------------+
| store_number | manager_id | name            |
+--------------+------------+-----------------+
|         1009 |       1011 | Wally Chester   |
|         1010 |       1012 | Betty Davis     |
|         1012 |       1105 | Allen Jackson   |
|         1013 |       2019 | Gordon Teasdale |
+--------------+------------+-----------------+
4 rows in set (0.17 sec)

• INNER
  
• intersection
• OUTER
  
• LEFT
• RIGHT
• FULL
• CROSS
  

+--------------------------+
| table 1                  |
|            +---------------------------+
|            |             |             |
+--------------------------+             |
             |                 table 2   |
             +---------------------------+ 

+--------------------------+
| table 1                  |
|            +---------------------------+
|            |             |             |
+--------------------------+             |
             |                 table 2   |
             +---------------------------+ 

// intersect plus non-matching left rows

+--------------------------+
| table 1                  |
|            +---------------------------+
|            |             |             |
+--------------------------+             |
             |                 table 2   |
             +---------------------------+

+--------------------------+
| table 1                  |
|            +---------------------------+
|            |             |             |
+--------------------------+             |
             |                 table 2   |
             +---------------------------+

// intersect plus non-matching left and right rows

• JOIN
  
• ON
  

• INNER 
  
• CROSS
• STRAIGHT_JOIN
  
• LEFT [OUTER] JOIN
  
• NATURAL [LEFT [OUTER]] JOIN
  
• RIGHT [OUTER] JOIN
  
• NATURAL [RIGHT [OUTER]] JOIN 
  

• CROSS JOIN
• FULL OUTER JOIN
  
• INTERSECT
• MINUS
  

Assignment

Create two tables, one called 'A' and one
called 'B'.  Create an  Integer column called
'numeral' and  a varchar column limited to 20
characters called word.  Populate the  word
column of table A with words starting with the
letter 'a'.Populate the  word column of table B
with words starting with the letter 'b'.

Use unique values in the numeral column where
7 of the numeric values are common to both tables.

Demonstrate the following joins on the tables.
Where needed use the numeral column values
in the conditional clauses.

a ) CROSS JOIN
// screen shot the first and last 5 values of the result set
b ) INNER JOIN
c ) LEFT OUTER JOIN
d ) RIGHT OUTER JOIN
e ) OUTER JOIN  // in MySQL use a UNION