To retrieve data from multiple tables, we need to use MySQL JOINSwhich is used with SELECT statement. MySQL JOINS is performed whenever we need to fetch records from two or more tables.
There are 3 types of MySQL joins:
- INNER JOIN (also called simple join)
- LEFT OUTER JOIN (also called LEFT JOIN)
- RIGHT OUTER JOIN (also called RIGHT JOIN)
MySQL Inner JOIN (Simple Join)
To return all rows from multiple tables where the join condition is satisfied, there we need to use MySQL INNER JOIN. INNER JOIN is most common type of join.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Consider we have two tables “officers” and “students”. The records are:
+————+—————+———-+
| office_id | officer__name | address |
+————+—————+———-+
| 1 | Rahul | Lucknow |
| 2 | VimaL | Faizabad |
| 3 | Deepika | Lucknow |
| 4 | Ajeett | Mau |
+————+—————+———-+
+————-+—————+————-+
| student_id | student_name | course_name |
+————-+—————+————-+
| 1 | Aryan | Java |
| 2 | Rohini | Hadoop |
| 3 | Lallu | MongoDb |
+————-+—————+————-+
SELECT officers.officer_name, officers.address, students.course_name
FROM officers
INNER JOIN students
ON officers.officer_id = students.student_id;
After executing the above query our output looks like
MySQL Left Outer Join
To returns all rows from the left hand table specified in ON condition & only those rows from the other table, there we need to use LEFT OUTER JOIN
Syntax:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Example:
SELECT officers.officer_name, officers.address, students.course_name
FROM officers
LEFT JOIN students
ON officers.officer_id = students.student_id;
After executing the above query our output looks like
MySQL Right Outer Join
To returns all rows from the RIGHT-hand table specified in ON condition & only those rows from the other table, there we need to use MySQL Right Outer Join.
Syntax:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Example:
SELECT officers.officer_name, officers.address, students.course_name, students.student_name
FROM officers
RIGHT JOIN students
ON officers.officer_id = students.student_id;
After executing the above query our output looks like
