MySQL JOINS
In MySQL, the Join Query is used to fetch records from multiple tables.
Types of Joins:
There are three types of Joins that the MySQL database supports. These are:
- Inner or Simple Join
- Left Outer Join
- Right Outer Join
INNER JOIN
The INNER Join returns all the rows from multiple tables.
Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table_1 INNER JOIN table_2 ON join_predicate;
Example: Items Table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 100 |
| 4 | Grocery | 90 |
| 5 | Toys | 50 |
Shops Table:
| ID | SHOP |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
SELECT items.name, shops.shop FROM items INNER JOIN shops ON items.id = shops.id ORDER BY name;
Output:
| NAME | SHOP |
| Electronics | A |
| Fashion | C |
| Grocery | D |
| Sports | B |
Explanation:
The ‘items’ and the ‘shops’ are two already existing tables in a database. The result set includes the selected fields from both tables along with the records satisfying the joining condition.
Left Outer Join:
The Left Outer Join query returns all the rows from the Left table for the specified fields and only those rows from the Right table where the join condition is satisfied.
Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table_1 LEFT JOIN table_2 ON join_predicate;
Example: Items Table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 100 |
| 4 | Grocery | 90 |
| 5 | Toys | 50 |
Shops Table:
| ID | SHOP |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
SELECT items.name, shops.shop FROM items LEFT JOIN shops ON items.id = shops.id ORDER BY name;
Output:
| NAME | SHOP |
| Electronics | A |
| Fashion | C |
| Grocery | D |
| Sports | B |
| Toys | NULL |
Explanation:
The ‘items’ and the ‘shops’ are two already existing tables in a database. The result set includes the selected fields from both tables along with all the records from the ‘items’ table for the selected fields and only those records from the ‘shops’ table for the selected fields that satisfy the joining condition.
Right Outer Join:
The Right Outer Join query returns all the rows from the Right table for the specified fields and only those rows from the Left table where the join condition is satisfied.
Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table_1 RIGHT JOIN table_2 ON join_predicate;
Example: Items Table:
| ID | NAME | QUANTITY |
| 1 | Electronics | 30 |
| 2 | Sports | 45 |
| 3 | Fashion | 100 |
| 4 | Grocery | 90 |
| 5 | Toys | 50 |
Shops Table:
| ID | SHOP |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
SELECT items.name, shops.shop FROM items RIGHT JOIN shops ON items.id = shops.id ORDER BY name;
Output:
| NAME | SHOP |
| Electronics | A |
| Fashion | C |
| Grocery | D |
| Sports | B |
Explanation:
The ‘items’ and the ‘shops’ are two already existing tables in a database. The result set includes the selected fields from both tables along with all the records from the ‘shops’ table for the selected fields and only those records from the ‘items’ table for the selected fields that satisfy the joining condition.