CodesJava

Easy learning with example program codes

INTERSECT Operator in MariaDB

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.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status