During an INNER JOIN, all records that are unmatched are discarded.
Only the matched rows are displayed.
This is the default type of join, so the word INNER is optional.
This type of join is based on the criteria in the JOIN clause.
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> create table job (
-> id int,
-> title VARCHAR(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,'Jason', 'Martin', '19960725', '20060725', 1234.56, 'Toronto', 'Programmer');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,'Alison', 'Mathews', '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,'James', 'Smith', '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,'Celia', 'Rice', '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,'Robert', 'Black', '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York', 'Tester');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York', 'Manager');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into job (id, title) values (1,'Tester');
Query OK, 1 row affected (0.02 sec)
mysql> insert into job (id, title) values (2,'Accountant');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (3,'Developer');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (4,'Coder');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (5,'Director');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (6,'Mediator');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (7,'Proffessor');
Query OK, 1 row affected (0.02 sec)
mysql> insert into job (id, title) values (8,'Programmer');
Query OK, 1 row affected (0.00 sec)
mysql> insert into job (id, title) values (9,'Developer');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from job;
+------+------------+
| id | title |
+------+------------+
| 1 | Tester |
| 2 | Accountant |
| 3 | Developer |
| 4 | Coder |
| 5 | Director |
| 6 | Mediator |
| 7 | Proffessor |
| 8 | Programmer |
| 9 | Developer |
+------+------------+
9 rows in set (0.00 sec)
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM employee as C
-> INNER JOIN job as O ON C.ID = O.ID;
+------------+-----------+------------+
| First_Name | Last_Name | title |
+------------+-----------+------------+
| Jason | Martin | Tester |
| Alison | Mathews | Accountant |
| James | Smith | Developer |
| Celia | Rice | Coder |
| Robert | Black | Director |
| Linda | Green | Mediator |
| David | Larry | Proffessor |
| James | Cat | Programmer |
+------------+-----------+------------+
8 rows in set (0.00 sec)
mysql>
The old way to create an INNER JOIN would look like the following:
mysql>
mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee as C, job as O
-> WHERE C.ID = O.ID;
+------------+-----------+------------+
| First_Name | Last_Name | title |
+------------+-----------+------------+
| Jason | Martin | Tester |
| Alison | Mathews | Accountant |
| James | Smith | Developer |
| Celia | Rice | Coder |
| Robert | Black | Director |
| Linda | Green | Mediator |
| David | Larry | Proffessor |
| James | Cat | Programmer |
+------------+-----------+------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table job;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>