MySQL JOINS

MySQL JOINS

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;

MySQL JOINS

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 JOINS



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;

MySQL JOINS

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 JOINS

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;

MySQL JOINS

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

MySQL JOINS
Post a Comment