MySQL join

MySQL  join :

“JOIN” is an SQL keyword which used to query data from two or more related tables.

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database provides a number of tables containing related data. You can see a very simple example :

Here students and course are two table :

id name course
1 pradeep 1
2 amit 1
3 rahul 2
4 Dev 5
5 Raman (NULL)

Course table:

id name
1 Java
2 C#
3 C++
4 C

In essence, MySQL will automatically:Since we’re using InnoDB tables and know that student.course and are related, we can specify a foreign key relationship:

  • re-number the associated entries in the user.course column if the changes
  • reject any attempt to delete a course where users are enrolled.

Note: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.


Example :


FROM `user`

INNER JOIN `course` on user.course =;

Output :
Pradeep Java
amit Java
rahul C#


Left join :

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course).


FROM `user`

LEFT JOIN `course` on user.course =;

Right Join :


FROM `user`

RIGHT JOIN `course` on user.course =;