http://sqle.ru/index.php?action=history&feed=atom&title=MySQL_Tutorial%2FTable_Join%2FANSI_92_Join
MySQL Tutorial/Table Join/ANSI 92 Join - История изменений
2026-05-14T13:01:56Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=MySQL_Tutorial/Table_Join/ANSI_92_Join&diff=810&oldid=prev
в 13:44, 26 мая 2010
2010-05-26T13:44:56Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:44, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=MySQL_Tutorial/Table_Join/ANSI_92_Join&diff=811&oldid=prev
Admin: 1 версия
2010-05-26T09:53:47Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>== CROSS JOIN==<br />
<br />
<p>A CROSS JOIN returns all the records from all the tables mentioned in the JOIN.</p><br />
<p>This is also referred to as a Cartesian join.</p><br />
<p>The syntax for a CROSS JOIN would look like the following:</p><br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
mysql><br />
mysql><br />
mysql> CREATE TABLE Employee(<br />
-> id int,<br />
-> first_name VARCHAR(15),<br />
-> last_name VARCHAR(15),<br />
-> start_date DATE,<br />
-> end_date DATE,<br />
-> salary FLOAT(8,2),<br />
-> city VARCHAR(10),<br />
-> description VARCHAR(15)<br />
-> );<br />
Query OK, 0 rows affected (0.03 sec)<br />
mysql><br />
mysql> create table job (<br />
-> id int,<br />
-> title VARCHAR(20)<br />
-> );<br />
Query OK, 0 rows affected (0.03 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");<br />
Query OK, 1 row affected (0.01 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");<br />
Query OK, 1 row affected (0.01 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into job (id, title) values (1,"Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (2,"Accountant");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (3,"Developer");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql> insert into job (id, title) values (4,"Coder");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (5,"Director");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (6,"Mediator");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (7,"Proffessor");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (8,"Programmer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (9,"Developer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> select * from job;<br />
+------+------------+<br />
| id | title |<br />
+------+------------+<br />
| 1 | Tester |<br />
| 2 | Accountant |<br />
| 3 | Developer |<br />
| 4 | Coder |<br />
| 5 | Director |<br />
| 6 | Mediator |<br />
| 7 | Proffessor |<br />
| 8 | Programmer |<br />
| 9 | Developer |<br />
+------+------------+<br />
9 rows in set (0.02 sec)<br />
mysql> select * from Employee;<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| id | first_name | last_name | start_date | end_date | salary | city | description |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |<br />
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |<br />
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |<br />
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |<br />
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |<br />
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |<br />
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |<br />
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
The old way:<br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM Employee as C, job as O;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Tester |<br />
| James | Smith | Tester |<br />
| Celia | Rice | Tester |<br />
| Robert | Black | Tester |<br />
| Linda | Green | Tester |<br />
| David | Larry | Tester |<br />
| James | Cat | Tester |<br />
| Jason | Martin | Accountant |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Accountant |<br />
| Celia | Rice | Accountant |<br />
| Robert | Black | Accountant |<br />
| Linda | Green | Accountant |<br />
| David | Larry | Accountant |<br />
| James | Cat | Accountant |<br />
| Jason | Martin | Developer |<br />
| Alison | Mathews | Developer |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Developer |<br />
| Robert | Black | Developer |<br />
| Linda | Green | Developer |<br />
| David | Larry | Developer |<br />
| James | Cat | Developer |<br />
| Jason | Martin | Coder |<br />
| Alison | Mathews | Coder |<br />
| James | Smith | Coder |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Coder |<br />
| Linda | Green | Coder |<br />
| David | Larry | Coder |<br />
| James | Cat | Coder |<br />
| Jason | Martin | Director |<br />
| Alison | Mathews | Director |<br />
| James | Smith | Director |<br />
| Celia | Rice | Director |<br />
| Robert | Black | Director |<br />
| Linda | Green | Director |<br />
| David | Larry | Director |<br />
| James | Cat | Director |<br />
| Jason | Martin | Mediator |<br />
| Alison | Mathews | Mediator |<br />
| James | Smith | Mediator |<br />
| Celia | Rice | Mediator |<br />
| Robert | Black | Mediator |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Mediator |<br />
| James | Cat | Mediator |<br />
| Jason | Martin | Proffessor |<br />
| Alison | Mathews | Proffessor |<br />
| James | Smith | Proffessor |<br />
| Celia | Rice | Proffessor |<br />
| Robert | Black | Proffessor |<br />
| Linda | Green | Proffessor |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Proffessor |<br />
| Jason | Martin | Programmer |<br />
| Alison | Mathews | Programmer |<br />
| James | Smith | Programmer |<br />
| Celia | Rice | Programmer |<br />
| Robert | Black | Programmer |<br />
| Linda | Green | Programmer |<br />
| David | Larry | Programmer |<br />
| James | Cat | Programmer |<br />
| Jason | Martin | Developer |<br />
| Alison | Mathews | Developer |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Developer |<br />
| Robert | Black | Developer |<br />
| Linda | Green | Developer |<br />
| David | Larry | Developer |<br />
| James | Cat | Developer |<br />
+------------+-----------+------------+<br />
72 rows in set (0.00 sec)<br />
mysql><br />
The ANSI-92 way:<br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM Employee as C<br />
-> CROSS JOIN Job as O;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Tester |<br />
| James | Smith | Tester |<br />
| Celia | Rice | Tester |<br />
| Robert | Black | Tester |<br />
| Linda | Green | Tester |<br />
| David | Larry | Tester |<br />
| James | Cat | Tester |<br />
| Jason | Martin | Accountant |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Accountant |<br />
| Celia | Rice | Accountant |<br />
| Robert | Black | Accountant |<br />
| Linda | Green | Accountant |<br />
| David | Larry | Accountant |<br />
| James | Cat | Accountant |<br />
| Jason | Martin | Developer |<br />
| Alison | Mathews | Developer |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Developer |<br />
| Robert | Black | Developer |<br />
| Linda | Green | Developer |<br />
| David | Larry | Developer |<br />
| James | Cat | Developer |<br />
| Jason | Martin | Coder |<br />
| Alison | Mathews | Coder |<br />
| James | Smith | Coder |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Coder |<br />
| Linda | Green | Coder |<br />
| David | Larry | Coder |<br />
| James | Cat | Coder |<br />
| Jason | Martin | Director |<br />
| Alison | Mathews | Director |<br />
| James | Smith | Director |<br />
| Celia | Rice | Director |<br />
| Robert | Black | Director |<br />
| Linda | Green | Director |<br />
| David | Larry | Director |<br />
| James | Cat | Director |<br />
| Jason | Martin | Mediator |<br />
| Alison | Mathews | Mediator |<br />
| James | Smith | Mediator |<br />
| Celia | Rice | Mediator |<br />
| Robert | Black | Mediator |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Mediator |<br />
| James | Cat | Mediator |<br />
| Jason | Martin | Proffessor |<br />
| Alison | Mathews | Proffessor |<br />
| James | Smith | Proffessor |<br />
| Celia | Rice | Proffessor |<br />
| Robert | Black | Proffessor |<br />
| Linda | Green | Proffessor |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Proffessor |<br />
| Jason | Martin | Programmer |<br />
| Alison | Mathews | Programmer |<br />
| James | Smith | Programmer |<br />
| Celia | Rice | Programmer |<br />
| Robert | Black | Programmer |<br />
| Linda | Green | Programmer |<br />
| David | Larry | Programmer |<br />
| James | Cat | Programmer |<br />
| Jason | Martin | Developer |<br />
| Alison | Mathews | Developer |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Developer |<br />
| Robert | Black | Developer |<br />
| Linda | Green | Developer |<br />
| David | Larry | Developer |<br />
| James | Cat | Developer |<br />
+------------+-----------+------------+<br />
72 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
mysql><br />
mysql><br />
mysql> drop table job;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql> drop table Employee;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql><br />
mysql></source><br />
<br />
<br />
<br />
== INNER JOIN==<br />
<br />
<p>During an INNER JOIN, all records that are unmatched are discarded.</p><br />
<p>Only the matched rows are displayed.</p><br />
<p>This is the default type of join, so the word INNER is optional.</p><br />
<p>This type of join is based on the criteria in the JOIN clause.</p><br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
mysql><br />
mysql> CREATE TABLE Employee(<br />
-> id int,<br />
-> first_name VARCHAR(15),<br />
-> last_name VARCHAR(15),<br />
-> start_date DATE,<br />
-> end_date DATE,<br />
-> salary FLOAT(8,2),<br />
-> city VARCHAR(10),<br />
-> description VARCHAR(15)<br />
-> );<br />
Query OK, 0 rows affected (0.06 sec)<br />
mysql><br />
mysql> create table job (<br />
-> id int,<br />
-> title VARCHAR(20)<br />
-> );<br />
Query OK, 0 rows affected (0.03 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into job (id, title) values (1,"Tester");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql> insert into job (id, title) values (2,"Accountant");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (3,"Developer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (4,"Coder");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (5,"Director");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (6,"Mediator");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (7,"Proffessor");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql> insert into job (id, title) values (8,"Programmer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (9,"Developer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> select * from job;<br />
+------+------------+<br />
| id | title |<br />
+------+------------+<br />
| 1 | Tester |<br />
| 2 | Accountant |<br />
| 3 | Developer |<br />
| 4 | Coder |<br />
| 5 | Director |<br />
| 6 | Mediator |<br />
| 7 | Proffessor |<br />
| 8 | Programmer |<br />
| 9 | Developer |<br />
+------+------------+<br />
9 rows in set (0.00 sec)<br />
mysql> select * from Employee;<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| id | first_name | last_name | start_date | end_date | salary | city | description |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |<br />
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |<br />
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |<br />
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |<br />
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |<br />
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |<br />
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |<br />
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM employee as C<br />
-> INNER JOIN job as O ON C.ID = O.ID;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Director |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Programmer |<br />
+------------+-----------+------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
The old way to create an INNER JOIN would look like the following:<br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM Employee as C, job as O<br />
-> WHERE C.ID = O.ID;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Director |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Programmer |<br />
+------------+-----------+------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
mysql><br />
mysql> drop table job;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql> drop table Employee;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql><br />
mysql></source><br />
<br />
<br />
<br />
== LEFT JOIN==<br />
<br />
<p>A LEFT JOIN returns all rows from the left table in a join.</p><br />
<p>All the rows from the table on the left side of the equation will be returned, regardless of whether they have a match with the table on the right side.</p><br />
<p>If there is no match, a NULL value will be returned.</p><br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
mysql><br />
mysql> CREATE TABLE Employee(<br />
-> id int,<br />
-> first_name VARCHAR(15),<br />
-> last_name VARCHAR(15),<br />
-> start_date DATE,<br />
-> end_date DATE,<br />
-> salary FLOAT(8,2),<br />
-> city VARCHAR(10),<br />
-> description VARCHAR(15)<br />
-> );<br />
Query OK, 0 rows affected (0.02 sec)<br />
mysql><br />
mysql> create table job (<br />
-> id int,<br />
-> title VARCHAR(20)<br />
-> );<br />
Query OK, 0 rows affected (0.02 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)<br />
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> insert into job (id, title) values (1,"Tester");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (2,"Accountant");<br />
Query OK, 1 row affected (0.02 sec)<br />
mysql> insert into job (id, title) values (3,"Developer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (4,"Coder");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (5,"Director");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (6,"Mediator");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (7,"Proffessor");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (8,"Programmer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql> insert into job (id, title) values (9,"Developer");<br />
Query OK, 1 row affected (0.00 sec)<br />
mysql><br />
mysql> select * from job;<br />
+------+------------+<br />
| id | title |<br />
+------+------------+<br />
| 1 | Tester |<br />
| 2 | Accountant |<br />
| 3 | Developer |<br />
| 4 | Coder |<br />
| 5 | Director |<br />
| 6 | Mediator |<br />
| 7 | Proffessor |<br />
| 8 | Programmer |<br />
| 9 | Developer |<br />
+------+------------+<br />
9 rows in set (0.00 sec)<br />
mysql> select * from Employee;<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| id | first_name | last_name | start_date | end_date | salary | city | description |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |<br />
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |<br />
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |<br />
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |<br />
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |<br />
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |<br />
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |<br />
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |<br />
+------+------------+-----------+------------+------------+---------+-----------+-------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM Employee as C<br />
-> LEFT JOIN job as O ON C.ID = O.ID;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Director |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Programmer |<br />
+------------+-----------+------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
The old way:<br />
mysql><br />
mysql> SELECT C.First_Name, C.Last_Name, O.title<br />
-> FROM Employee AS C, job as O<br />
-> WHERE C.ID = O.ID;<br />
+------------+-----------+------------+<br />
| First_Name | Last_Name | title |<br />
+------------+-----------+------------+<br />
| Jason | Martin | Tester |<br />
| Alison | Mathews | Accountant |<br />
| James | Smith | Developer |<br />
| Celia | Rice | Coder |<br />
| Robert | Black | Director |<br />
| Linda | Green | Mediator |<br />
| David | Larry | Proffessor |<br />
| James | Cat | Programmer |<br />
+------------+-----------+------------+<br />
8 rows in set (0.00 sec)<br />
mysql><br />
mysql><br />
mysql><br />
mysql><br />
mysql><br />
mysql> drop table job;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql> drop table Employee;<br />
Query OK, 0 rows affected (0.00 sec)<br />
mysql><br />
mysql></source><br />
<br />
<br />
<br />
== The basic format of the ANSI-92 JOIN==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
SELECT Column List<br />
FROM table name<br />
JOIN table name ON join criteria<br />
WHERE condition criteria</source></div>
Admin