MYSQL Joins

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

1. MYSQL Inner Join
2. MYSQL Left Join
3. MYSQL Right Join
4. MYSQL Full Join
5. MYSQL Self Join
6. 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 |
+------------------+--------+-----------------+