Joins are used for joining two more tables. using joins we can fetch data from the two table in a database.
Following are the different types of joins used in the mysql.
1.Inner join
2.Left join
3.Right join
To test the joins in mysql please follow the following steps
Create a table called jobtable. You can use the following code generating table in phpmyadmin
CREATE TABLE IF NOT EXISTS jobtable (
ID int(10) NOT NULL AUTO_INCREMENT,
job varchar(250) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
—
— Dumping data for table jobtable
—
INSERT INTO jobtable (ID, job) VALUES
(1, ‘Software engineer’),
(2, ‘webdesigner’),
(3, ‘Civil Engineer’),
(4, ‘Driver’),
(5, ‘Teacher’),
(6, ‘Accountant’),
(7, ‘Police’),
(8, ‘Marketting Executive’);
table data will be look like as below
———————————
ID job
———————————
1 Software engineer
2 webdesigner
3 Civil Engineer
4 Driver
5 Teacher
6 Accountant
———————————
Create a table employee with data amd the corresponding jobtable id.You can use the following code generating table in phpmyadmin
CREATE TABLE IF NOT EXISTS employee (
ID int(11) NOT NULL AUTO_INCREMENT,
Name varchar(250) NOT NULL,
jobid int(10) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
—
— Dumping data for table employee
—
INSERT INTO employee (ID, Name, jobid) VALUES
(1, ‘Vasanthan’, 1),
(2, ‘Shyam’, 1),
(3, ‘ragunath’, 3),
(4, ‘sanal’, 4),
(5, ‘Nazar’, NULL),
(6, ‘Sreejith’, 6),
(7, ‘Arun’, 2),
(8, ‘Dixit’, 5),
(9, ‘Raina’, NULL);
table data will be look like as below
————————————-
ID Name jobid
————————————-
1 Vasanthan 1
2 Shyam 1
3 ragunath 3
4 sanal 4
5 Nazar NULL
6 Sreejith 6
7 Arun 2
8 Dixit 5
9 Raina NULL
————————————–
INNERJOIN- inner join produce the matching records from both the table Employee and jobtable
Select employee.Name,jobtable.job From employee INNER JOIN jobtable on employee.jobid=jobtable.ID
Output:
—————————————
Name job
—————————————-
Vasanthan Software engineer
Shyam Software engineer
Arun webdesigner
ragunath Civil Engineer
sanal Driver
Dixit Teacher
Sreejith Accountant
—————————————–
LEFT JOIN: left join will produce all the matching records and some unmatching records from left table.
Select employee.Name,jobtable.job From employee LEFT JOIN jobtable on employee.jobid=jobtable.ID
Output:
—————————————-
Name job
—————————————-
Vasanthan Software engineer
Shyam Software engineer
ragunath Civil Engineer
sanal Driver
Nazar NULL
Sreejith Accountant
Arun webdesigner
Dixit Teacher
Raina NULL
——————————————
RIGHT JOIN: right join will produce all the matching records and some unmatching records from right table.
Select employee.Name,jobtable.job From employee RIGHT JOIN jobtable on
employee.jobid=jobtable.ID
OUTPUT:
——————————————-
Name job
——————————————-
Vasanthan Software engineer
Sreejith Accountant
Shyam Software engineer
sanal Driver
ragunath Civil Engineer
Dixit Teacher
Arun webdesigner
NULL Marketting Executive
NULL Police
———————————————