Embedded Files
Join in MySQL is technique to combine records from two tables for query processing.
MySQL official page recommend use join statement when want collect data from two tables which have relationship with each other.
Join on field that have index or foreign key will make query run faster.
MySQL support several kind of join: INNER JOIN, CROSS JOIN, LEFT JOIN, RIGHT JOIN
INNER JOIN
INNER JOIN
The most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course.
CROSS JOIN (OUTER JOIN)
CROSS JOIN (OUTER JOIN)
Returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.
Example:
LEFT JOIN
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).
LEFT JOIN means get all record in left table and matched records in reference table , if there is no record in reference table matched, the value is null.
RIGHT JOIN
RIGHT JOIN
Perhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user).
RIGHT JOIN returns all records from the right table (reference), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
Report abuse