MYSQL Joins
Category : TECH Author : Ankur Rastogi Date : Sat Apr 16 2016 Views : 37

Overview
MYSQL joins are used to combine data from two or more database tables on the basis of some common value in the tables. We apply joins by passing where conditions so we can use all the operators applicable with where clause like < , > , <=, >=, !=, <>, between, like, Not, = and lot more.
Here we will be explaining 5 types of joins in next few articles
- MYSQL Inner Join
- MYSQL Left Join
- MYSQL Right Join
- MYSQL Full Join
- MYSQL Self Join
- MYSQL Cartesian Join
Example
Let us have a look at the most simple type of join i.e. Inner Join which combines all the rows matching the condition given by us.
Have a look at these two table which are mentioned below
i) hostel_list table which is having the list of hostels.
+----+-------------------+----------------+--------------+
| id | name | occupancy_type | max_capacity |
+----+-------------------+----------------+--------------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+----------------+--------------+
ii) student_details table which is having the student details along with the info in which hostel they are residing.
+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
| 1 | Ankur Rastogi | First | 4 |
| 2 | Amit Kumar | Second | 8 |
| 3 | Ram Kumar | Second | 8 |
| 4 | Shiv Saxena | First | 6 |
| 5 | Aman Aggarwal | Third | 3 |
| 6 | Nipun Gupta | Fourth | 2 |
| 7 | Arpit Gupta | Third | 1 |
| 8 | Shantanu Rastogi | Second | 3 |
| 9 | Ramesh Kumar | Third | 1 |
+-----+------------------+--------+-----------+
Now, Let us apply the join on these two tables and fetch the list of students which reside in 'KAVERI and RUDRAKSH BHAVANS'
SELECT student.name, student.year, hostel.name FROM hostel_list hostel INNER JOIN student_list student ON hostel.id = student.hostel_id where hostel.name IN ('KAVERI BHAVAN','RUDRAKSH BHAVAN');
+------------------+--------+-----------------+
| name | year | name |
+------------------+--------+-----------------+
| Ankur Rastogi | First | KAVERI BHAVAN |
| Aman Aggarwal | Third | RUDRAKSH BHAVAN |
| Shantanu Rastogi | Second | RUDRAKSH BHAVAN |
+------------------+--------+-----------------+

Overview
MYSQL joins are used to combine data from two or more database tables on the basis of some common value in the tables. We apply joins by passing where conditions so we can use all the operators applicable with where clause like < , > , <=, >=, !=, <>, between, like, Not, = and lot more.
Here we will be explaining 5 types of joins in next few articles
- MYSQL Inner Join
- MYSQL Left Join
- MYSQL Right Join
- MYSQL Full Join
- MYSQL Self Join
- MYSQL Cartesian Join
Example
Let us have a look at the most simple type of join i.e. Inner Join which combines all the rows matching the condition given by us.
Have a look at these two table which are mentioned below
i) hostel_list table which is having the list of hostels.
+----+-------------------+----------------+--------------+
| id | name | occupancy_type | max_capacity |
+----+-------------------+----------------+--------------+
| 1 | ASHOK BHAVAN | 2 | 1090 |
| 2 | KAILASH BHAVAN | 2 | 990 |
| 3 | RUDRAKSH BHAVAN | 2 | 990 |
| 4 | KAVERI BHAVAN | 1 | 690 |
| 5 | BHAGIRATHI BHAVAN | 4 | 2140 |
| 6 | NARMADA BHAVAN | 6 | 3240 |
| 7 | GANGA BHAVAN | 1 | 780 |
| 8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+----------------+--------------+
ii) student_details table which is having the student details along with the info in which hostel they are residing.
+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
| 1 | Ankur Rastogi | First | 4 |
| 2 | Amit Kumar | Second | 8 |
| 3 | Ram Kumar | Second | 8 |
| 4 | Shiv Saxena | First | 6 |
| 5 | Aman Aggarwal | Third | 3 |
| 6 | Nipun Gupta | Fourth | 2 |
| 7 | Arpit Gupta | Third | 1 |
| 8 | Shantanu Rastogi | Second | 3 |
| 9 | Ramesh Kumar | Third | 1 |
+-----+------------------+--------+-----------+
Now, Let us apply the join on these two tables and fetch the list of students which reside in 'KAVERI and RUDRAKSH BHAVANS'
SELECT student.name, student.year, hostel.name FROM hostel_list hostel INNER JOIN student_list student ON hostel.id = student.hostel_id where hostel.name IN ('KAVERI BHAVAN','RUDRAKSH BHAVAN');
+------------------+--------+-----------------+
| name | year | name |
+------------------+--------+-----------------+
| Ankur Rastogi | First | KAVERI BHAVAN |
| Aman Aggarwal | Third | RUDRAKSH BHAVAN |
| Shantanu Rastogi | Second | RUDRAKSH BHAVAN |
+------------------+--------+-----------------+
Disclaimer: The above content reflect author’s personal views and do not reflect the views of OYEWIKI. Neither OYEWIKI nor any person/organization acting on its behalf is liable to accept any legal liability/responsibility for any error/mislead in this information or any information available on the website. This website in no way accepts the responsibility for any loss, injury, damage, discomfort or inconvenience caused as a result of reliance on any information provided on this website.
If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.