JOINS in MariaDB

by

MariaDB JOINS
To retrieve data from multiple tables, the MariaDB Join Query is used.

Types of Joins:

There are mainly three types of Joins that the MariaDB database supports. These are:

  • Inner or Simple Join
  • Left Outer Join or Left Join
  • Right Outer Join or Right Join

 

INNER JOIN
The INNER Join is the most common one. It returns all the rows from multiple tables where the join condition is met.

Syntax: 

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
INNER JOIN table_2  
ON join_predicate;

Example:
Players Table:

ID	NAME	        SPORTS
1	Sachin	        Cricket
2	Dhoni	        Cricket
3	Sunil	        Football
4	Srikanth	Badminton
5	Mary	        Boxing

Trainers Table:

TRAINER_ID	TRAINER_NAME	TRAINER_SPORTS
101	        Bond	        Football
102	        Smith	        Badminton
103	        Brand	        Boxing

Query: 

SELECT players.id, players.name, trainers.trainer_id  
FROM players  
INNER JOIN trainers  
ON players.sports = trainers.trainer_sports;

Output: 

ID	NAME	        TRAINER_ID
3	Sunil	        101
4	Srikanth	102
5	Mary	        103

Explanation:
The PLAYERS and the TRAINERS are the already existing tables. The selected fields of the rows satisfying the conditions will be added to the result set, on joining.