Creating Inner Joins and Cross Joins

    
mysql>
mysql> CREATE TABLE Books
    -> (
    ->     BookID SMALLINT NOT NULL PRIMARY KEY,
    ->     BookTitle VARCHAR(60) NOT NULL,
    ->     Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Books VALUES
    -> (12786, 'Notebook', 1934),
    -> (13331, 'C++', 1919),
    -> (14356, 'Opera', 1966),
    -> (15729, 'Sql Server', 1932),
    -> (16284, 'C', 1996),
    -> (17695, 'Pascal', 1980),
    -> (19264, 'Postcards', 1992),
    -> (19354, 'Oracle', 1993);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE Authors
    -> (
    ->     AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->     AuthFN VARCHAR(20),
    ->     AuthMN VARCHAR(20),
    ->     AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Authors VALUES
    -> (1006, 'Hunter', 'S.', 'Thompson'),
    -> (1007, 'Joyce', 'Carol', 'Oates'),
    -> (1008, 'Black', NULL, 'Elk'),
    -> (1009, 'Rainer', 'Maria', 'Rilke'),
    -> (1010, 'John', 'Kennedy', 'Toole'),
    -> (1011, 'John', 'G.', 'Neihardt'),
    -> (1012, 'Annie', NULL, 'Proulx'),
    -> (1013, 'Alan', NULL, 'Watts'),
    -> (1014, 'Nelson', NULL, 'Algren');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE AuthorBook
    -> (
    ->     BookID SMALLINT NOT NULL,
    ->     AuthID SMALLINT NOT NULL,
    ->     PRIMARY KEY (AuthID, BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO AuthorBook VALUES
    -> (1006, 14356),
    -> (1008, 15729),
    -> (1009, 12786),
    -> (1010, 17695),
    -> (1011, 15729),
    -> (1012, 19264),
    -> (1012, 19354),
    -> (1014, 16284);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> SELECT BookTitle, AuthID FROM Books, AuthorBook;
+------------+--------+
| BookTitle  | AuthID |
+------------+--------+
| Notebook   |  12786 |
| C++        |  12786 |
| Opera      |  12786 |
| Sql Server |  12786 |
| C          |  12786 |
| Pascal     |  12786 |
| Postcards  |  12786 |
| Oracle     |  12786 |
| Notebook   |  14356 |
| C++        |  14356 |
| Opera      |  14356 |
| Sql Server |  14356 |
| C          |  14356 |
| Pascal     |  14356 |
| Postcards  |  14356 |
| Oracle     |  14356 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  16284 |
| C++        |  16284 |
| Opera      |  16284 |
| Sql Server |  16284 |
| C          |  16284 |
| Pascal     |  16284 |
| Postcards  |  16284 |
| Oracle     |  16284 |
| Notebook   |  17695 |
| C++        |  17695 |
| Opera      |  17695 |
| Sql Server |  17695 |
| C          |  17695 |
| Pascal     |  17695 |
| Postcards  |  17695 |
| Oracle     |  17695 |
| Notebook   |  19264 |
| C++        |  19264 |
| Opera      |  19264 |
| Sql Server |  19264 |
| C          |  19264 |
| Pascal     |  19264 |
| Postcards  |  19264 |
| Oracle     |  19264 |
| Notebook   |  19354 |
| C++        |  19354 |
| Opera      |  19354 |
| Sql Server |  19354 |
| C          |  19354 |
| Pascal     |  19354 |
| Postcards  |  19354 |
| Oracle     |  19354 |
+------------+--------+
64 rows in set (0.00 sec)

mysql> SELECT BookTitle, AuthID FROM Books JOIN AuthorBook;
+------------+--------+
| BookTitle  | AuthID |
+------------+--------+
| Notebook   |  12786 |
| C++        |  12786 |
| Opera      |  12786 |
| Sql Server |  12786 |
| C          |  12786 |
| Pascal     |  12786 |
| Postcards  |  12786 |
| Oracle     |  12786 |
| Notebook   |  14356 |
| C++        |  14356 |
| Opera      |  14356 |
| Sql Server |  14356 |
| C          |  14356 |
| Pascal     |  14356 |
| Postcards  |  14356 |
| Oracle     |  14356 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  16284 |
| C++        |  16284 |
| Opera      |  16284 |
| Sql Server |  16284 |
| C          |  16284 |
| Pascal     |  16284 |
| Postcards  |  16284 |
| Oracle     |  16284 |
| Notebook   |  17695 |
| C++        |  17695 |
| Opera      |  17695 |
| Sql Server |  17695 |
| C          |  17695 |
| Pascal     |  17695 |
| Postcards  |  17695 |
| Oracle     |  17695 |
| Notebook   |  19264 |
| C++        |  19264 |
| Opera      |  19264 |
| Sql Server |  19264 |
| C          |  19264 |
| Pascal     |  19264 |
| Postcards  |  19264 |
| Oracle     |  19264 |
| Notebook   |  19354 |
| C++        |  19354 |
| Opera      |  19354 |
| Sql Server |  19354 |
| C          |  19354 |
| Pascal     |  19354 |
| Postcards  |  19354 |
| Oracle     |  19354 |
+------------+--------+
64 rows in set (0.00 sec)

mysql> SELECT BookTitle, AuthID FROM Books INNER JOIN AuthorBook;
+------------+--------+
| BookTitle  | AuthID |
+------------+--------+
| Notebook   |  12786 |
| C++        |  12786 |
| Opera      |  12786 |
| Sql Server |  12786 |
| C          |  12786 |
| Pascal     |  12786 |
| Postcards  |  12786 |
| Oracle     |  12786 |
| Notebook   |  14356 |
| C++        |  14356 |
| Opera      |  14356 |
| Sql Server |  14356 |
| C          |  14356 |
| Pascal     |  14356 |
| Postcards  |  14356 |
| Oracle     |  14356 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  16284 |
| C++        |  16284 |
| Opera      |  16284 |
| Sql Server |  16284 |
| C          |  16284 |
| Pascal     |  16284 |
| Postcards  |  16284 |
| Oracle     |  16284 |
| Notebook   |  17695 |
| C++        |  17695 |
| Opera      |  17695 |
| Sql Server |  17695 |
| C          |  17695 |
| Pascal     |  17695 |
| Postcards  |  17695 |
| Oracle     |  17695 |
| Notebook   |  19264 |
| C++        |  19264 |
| Opera      |  19264 |
| Sql Server |  19264 |
| C          |  19264 |
| Pascal     |  19264 |
| Postcards  |  19264 |
| Oracle     |  19264 |
| Notebook   |  19354 |
| C++        |  19354 |
| Opera      |  19354 |
| Sql Server |  19354 |
| C          |  19354 |
| Pascal     |  19354 |
| Postcards  |  19354 |
| Oracle     |  19354 |
+------------+--------+
64 rows in set (0.00 sec)

mysql> SELECT BookTitle, AuthID FROM Books CROSS JOIN AuthorBook;
+------------+--------+
| BookTitle  | AuthID |
+------------+--------+
| Notebook   |  12786 |
| C++        |  12786 |
| Opera      |  12786 |
| Sql Server |  12786 |
| C          |  12786 |
| Pascal     |  12786 |
| Postcards  |  12786 |
| Oracle     |  12786 |
| Notebook   |  14356 |
| C++        |  14356 |
| Opera      |  14356 |
| Sql Server |  14356 |
| C          |  14356 |
| Pascal     |  14356 |
| Postcards  |  14356 |
| Oracle     |  14356 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  15729 |
| C++        |  15729 |
| Opera      |  15729 |
| Sql Server |  15729 |
| C          |  15729 |
| Pascal     |  15729 |
| Postcards  |  15729 |
| Oracle     |  15729 |
| Notebook   |  16284 |
| C++        |  16284 |
| Opera      |  16284 |
| Sql Server |  16284 |
| C          |  16284 |
| Pascal     |  16284 |
| Postcards  |  16284 |
| Oracle     |  16284 |
| Notebook   |  17695 |
| C++        |  17695 |
| Opera      |  17695 |
| Sql Server |  17695 |
| C          |  17695 |
| Pascal     |  17695 |
| Postcards  |  17695 |
| Oracle     |  17695 |
| Notebook   |  19264 |
| C++        |  19264 |
| Opera      |  19264 |
| Sql Server |  19264 |
| C          |  19264 |
| Pascal     |  19264 |
| Postcards  |  19264 |
| Oracle     |  19264 |
| Notebook   |  19354 |
| C++        |  19354 |
| Opera      |  19354 |
| Sql Server |  19354 |
| C          |  19354 |
| Pascal     |  19354 |
| Postcards  |  19354 |
| Oracle     |  19354 |
+------------+--------+
64 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table AuthorBook;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Authors;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Books;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
Related examples in the same category

1.	INNER JOIN in MySQL
2.	To qualify the inner or cross join, you can use the ON or USING clause rather than the WHERE clause.
3.	Qualify an inner or full join by specifying a condition in the WHERE clause
4.	Inner join and Delete
5.	Inner Joins
6.	Inner joins 2
7.	Outer join then inner join
8.	Inner join syntax
9.	Query from a inner join query
Creating Inner Joins and Cross Joins : Inner Join « Join « SQL / MySQL

Related examples in the same category
