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
5 SQL

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

  • re-number the associated entries in the user.course column if the course.id 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 :


SELECT user.name, course.name

FROM `user`

INNER JOIN `course` on user.course = course.id;

Output :

 

user.name course.name
Pradeep Java
amit Java
rahul C#
Dec SQL

 

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).


SELECT user.name, course.name

FROM `user`

LEFT JOIN `course` on user.course = course.id;

Right Join :


SELECT user.name, course.name

FROM `user`

RIGHT JOIN `course` on user.course = course.id;

 

Advertisements