MariaDB INTERSECT
The MariaDB INTERSECT operator is used to get the common or intersecting records from the corresponding columns of the selected tables. Each SELECT statement however must have the same number of expressions, and each corresponding expression should be of the same data type.
Syntax:
SELECT expr_1, expr_2, ... expr_n FROM table1 WHERE conditions INTERSECT SELECT expr_1, expr_2, ... expr_n FROM table2 WHERE conditions;
Example 1: Fetching single field from two tables.
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 5 Bond Football 6 Smith Badminton 7 Brand Boxing
Query:
SELECT sports
FROM players
UNION
SELECT trainer_sports
FROM trainers; |
Output:
SPORTS Football Badminton Boxing
Explanation:
The PLAYERS and the TRAINERS are the already existing tables. An intersection of SPORTS and TRAINER_SPORTS would appear in the result set.
Example 2: Fetching multiple fields from two tables.
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 5 Sunil Football 6 Smith Badminton 7 Brand Boxing
Query:
SELECT name, sports
FROM players
INTERSECT
SELECT trainer_name, trainer_sports
FROM trainers; |
Output:
NAME SPORTS Sunil Football
Explanation:
The PLAYERS and the TRAINERS are the already existing tables. An intersection of SPORTS and TRAINER_SPORTS along with the intersection of NAME and TRAINER_NAME would appear in the result set.