MySQL JOIN

MySQL Join is used to retrieve data from self or more tables from a single statement based on the values of common columns between tables.

Types of JOIN

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

INNER JOIN

INNER JOIN is the most common type of join. It is used to return rows from multiple tables where the given condition satisfies. Tables join by FROM clause and ON clause is used to match up records.

Suppose we have two tables "students" and "books". Both contain the following data.

'id' column of students table and 'student_id' column of books table are same matching values.

The following statement returns all rows from both tables.

mysql->SELECT s.first_ name, s.last_name, s.class, b.book_id, b.issue_date
		FROM students s INNER JOIN books b ON s.id = b.student_id;

LEFT JOIN

LEFT JOIN returns all rows from left table and only those from other table where the join condition is fulfilled.

Suppose we have two tables "students" and "books". Both contain the following data.

In the books table, only some students matching records are found. When you execute the following statement, it returns all rows of students table and only the matching records from books table.

mysql->SELECT s.first_ name, s.last_name, s.class, b.issue_date
		FROM students s LEFT JOIN books b ON s.id = b.student_id;

RIGHT JOIN

RIGHT JOIN returns all rows from right table and only those from other table where the join condition is fulfilled.

When you execute the following statement, it returns all rows of books table and only the matching records from students table.

mysql->SELECT s.first_ name, s.last_name, s.class, b.issue_date
		FROM students s RIGHT JOIN books b ON s.id = b.student_id;