MySQL or Sql JOINS are used to retrieve data combining rows from two or more tables based on the relation between them. MySQL JOIN or Sql JOIN is executed by a SQl statement.

When we require to retrieve data from two or more tables base on some relation SQL or MySQl join is performed. Joining of tables involves the tricks to retrieve data from multiple table using a SQL statement.

There are different type of joins available in MySQl:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

We will discuss about

inner join

in this post:

INNER JOIN : INNER JOIN returns all the matching rows from both the table.

See the below image for visual example,

MySQL inner join

Lets explain with the example
Create two table admission and student

Admission table SQL

CREATE TABLE `admission` (
  `id` int(11) NOT NULL,
  `stdID` int(11) NOT NULL,
  `admission_date` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `admission` (`id`, `stdID`, `admission_date`) VALUES
(1, 1, '14-12-2016'),
(2, 3, '11-12-2016');

The admission table look like below

id stdID admission_date
1 1 14-12-2016
1 3 11-12-2016

Student table SQL

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `sname` varchar(100) NOT NULL,
  `fname` varchar(100) NOT NULL,
  `address` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `student` (`id`, `sname`, `fname`, `address`) VALUES
(1, 'Joy', 'Dev', 'Siliguri'),
(2, 'Rahul\r\n', 'Samrat', 'Chanmasari'),
(3, 'Rakesh', 'Dam', 'Jalpaiguri'),
(4, 'Chandan', 'Sridhar', 'Darjeeling\r\n');

The Student table look like below

id sname fname address
1 Joy Dev Siliguri
2 Rahul Samrat Champasari
3 Rakesh Dam Jalpaiguri
4 Chandan Sridhar Darjeeling

Now we want to select all the student who has taken admision.
Write the following query to fatch all the student who has taken admission

SELECT admission.stdID, student.sname, student.fname, admission.admission_date
FROM admission
INNER JOIN student ON admission.stdID=student.id;

The out put of inner join

stdID sname fname admission_date
1 Joy Dev 14-12-2016
2 Rakesh Dam 11-12-2016




Leave comment

Your email address will not be published. Required fields are marked with *.