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 :
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.
SELECT user.name, course.name FROM `user` INNER JOIN `course` on user.course = course.id;
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;