SQLINNERJOIN usage Solution

If at least one match exists in the table, the innerjoin keyword returns the row. Innerjoin keyword syntax column_name (s) fromtable_name1innerjointable_name2ontable_name1.column_nametable_name2.column_name Note: innerjoin is the same as join. If innerj matches at least one table, the inner join keyword returns a line.

Inner join keyword syntax
Column_name (s)
From table_name1
Inner join table_name2
On table_name1.column_name = table_name2.column_name
Note: inner join is the same as join. If there is at least one matching keyword in the table, return rows. If the rows in "a" do not match in "B", these rows are not listed.

Multi-table joint Query

>
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.09 sec)

Mysql>
Mysql>
Mysql> insert into books values (12786, 'java', 1934 ),
-> (13331, 'mysql', 1919 ),
-> (14356, '', 1966 ),
-> (15729, 'perl ', 1932 ),
-> (16284, 'oracle ', 1996 ),
-> (17695, 'pl/SQL ', 1980 ),
-> (19264, '', 1992 ),
-> (19354, 'www .zhutiai.com ', 1993 );
Query OK, 8 rows affected (0.05 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.05 sec)

Mysql>
Mysql>
Mysql> insert into authors values (1006, 'h','s. ', 'T '),
-> (1007, 'J', 'C', 'O '),
-> (1008, 'B', null, 'E '),
-> (1009, 'R', 'M', 'R '),
-> (1010, 'J', 'k', 'T '),
-> (1011, 'J', 'G. ', 'n '),
-> (1012, 'A', null, 'P '),
-> (1013, 'A', null, 'w '),
-> (1014, 'n', null, 'A ');
Query OK, 9 rows affected (0.03 sec)
Records: 9 duplicates: 0 warnings: 0

Mysql>
Mysql>
Mysql> create table authorbook (
-> Authid smallint not null,
-> Bookid smallint not null,
-> Primary key (authid, bookid ),
-> Foreign key (authid) references authors (authid ),
-> Foreign key (bookid) references books (bookid)
->)
-> Engine = innodb;
Query OK, 0 rows affected (0.06 sec)

Mysql>
Mysql>
Mysql> insert into authorbook values (1006,143 56 ),
-> (1008,157 29 ),
-> (1009,127 86 ),
-> (1010,176 95 ),
-> (1011,157 29 ),
-> (64, 1012,192 ),
-> (1012,193 54 ),
-> (1014,162 84 );
Query OK, 8 rows affected (0.05 sec)
Records: 8 duplicates: 0 warnings: 0

Mysql>
Mysql>
Mysql> select * from authors;
+ -------- +
| Authid | authfn | authmn | authln |
+ -------- +
| 1006 | h | s. | t |
| 1, 1007 | j | c | o |
| 1, 1008 | B | null | e |
| 1009 | r | m | r |
| 1010 | j | k | t |
| 1011 | j | g. | n |
| 1012 | a | null | p |
| 1013 | a | null | w |
| 1014 | n | null | an |
+ -------- +
9 rows in set (0.00 sec)

Mysql> select * from books;
+ -------- + ---------------- + ----------- +
| Bookid | booktitle | copyright |
+ -------- + ---------------- + ----------- +
| 1, 12786 | java | 1934 |
| 1, 13331 | mysql | 1919 |
| 14356 | php | 1966 |
| 15729 | perl | 1932 |
| 1, 16284 | oracle | 1996 |
| 17695 | pl/SQL | 1980 |
| 1, 19264 | javascript | 1992 |
| 19354 | 1993 |
+ -------- + ---------------- + ----------- +
8 rows in set (0.00 sec)

Mysql> select * from authorbook;
+ -------- +
| Authid | bookid |
+ -------- +
| 1009/12786 |
| 1006/14356 |
| 1008/15729 |
| 1011/15729 |
| 1014/16284 |
| 1010/17695 |
| 1012/19264 |
| 1012/19354 |
+ -------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql> select booktitle, authid from books inner join authorbook;
+ ---------------- + -------- +
| Booktitle | authid |
+ ---------------- + -------- +
| Java | 1, 1006 |
| Mysql | 1006 |
| Php | 1006 |
| Perl | 1006 |
| Oracle | 1006 |
| Pl/SQL | 1, 1006 |
| Javascript | 1006 |
| 1006 |
| Java | 1, 1008 |
| Mysql | 1008 |
| Php | 1008 |
| Perl | 1008 |
| Oracle | 1008 |
| Pl/SQL | 1, 1008 |
| Javascript | 1008 |
| 1008 |
| Java | 1, 1009 |
| Mysql | 1009 |
| Php | 1009 |
| Perl | 1009 |
| Oracle | 1009 |
| Pl/SQL | 1, 1009 |
| Javascript | 1009 |
| 1009 |
| Java | 1, 1010 |
| Mysql | 1010 |
| Php | 1010 |
| Perl | 1010 |
| Oracle | 1010 |
| Pl/SQL | 1, 1010 |
| Javascript | 1010 |
| 1010 |
| Java | 1, 1011 |
| Mysql | 1011 |
| Php | 1011 |
| Perl | 1011 |
| Oracle | 1011 |
| Pl/SQL | 1, 1011 |
| Javascript | 1011 |
| 1011 |
| Java | 1, 1012 |
| Mysql | 1012 |
| Php | 1012 |
| Perl | 1012 |
| Oracle | 1012 |
| Pl/SQL | 1, 1012 |
| Javascript | 1012 |
| 1012 |
| Java | 1, 1012 |
| Mysql | 1012 |
| Php | 1012 |
| Perl | 1012 |
| Oracle | 1012 |
| Pl/SQL | 1, 1012 |
| Javascript | 1012 |
| 1012 |
| Java | 1, 1014 |
| Mysql | 1014 |
| Php | 1014 |
| Perl | 1014 |
| Oracle | 1014 |
| Pl/SQL | 1, 1014 |
| Javascript | 1014 |
| 1014 |
+ ---------------- + -------- +
64 rows in set (0.00 sec)

Mysql>
Mysql> drop table authorbook;
Query OK, 0 rows affected (0.02 sec)

Mysql> drop table books;
Query OK, 0 rows affected (0.06 sec)

Mysql> drop table authors;
Query OK, 0 rows affected (0.03 sec)

Join two tables

Mysql> select employee. first_name, job. title, duty. task
-> From employee, job, duty
-> Where (employee. id = job. id and employee. id = duty. id );
+ ------------ + ----------- +
| First_name | title | task |
+ ------------ + ----------- +
| Jason | tester | test |
| Alison | accountant | calculate |
| James | developer | program |
| Celia | coder | test |
| Robert | director | manage |
| Linda | mediator | talk |
| David | proffessor | speak |
| James | programmer | shout |
+ ------------ + ----------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> drop table duty;
Query OK, 0 rows affected (0.00 sec)

Mysql> drop table job;
Query OK, 0 rows affected (0.01 sec)

Mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)

Summary

Use inner join to join two data tables:
Select * from table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number

Use inner join to join three data tables:
Select * from (Table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number) inner join table 3 on table 1. Field number = TABLE 3. Field number

Use inner join to join four data tables:
Select * from (Table 1 inner join table 2 on table 1. field number = table 2. field number) inner join table 3 on table 1. field number = table 3. field number) inner join table 4 on member. field number = table 4. field number

Use inner join to join Five data tables:
Select * from (Table 1 inner join table 2 on table 1. field number = table 2. field number) inner join table 3 on table 1. field number = table 3. field number) inner join table 4 on member. field number = table 4. field number) inner join table 5 on member. field number = table 5. field number