JOINS in MySQL

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.