JOIN

CREATE DATABASE COLL;
USE COLL;

CREATE TABLE STUDENT(
ID INT PRIMARY KEY,
NAME VARCHAR(80)
);

INSERT INTO STUDENT (ID, NAME) VALUES 
(101, 'ADAM'),
(102, 'BOB'),
(103, 'CASEY');

CREATE TABLE COURSE(
STUD_ID INT,
SUB_NAME VARCHAR(60)
);

INSERT INTO COURSE
(STUD_ID, SUB_NAME) VALUES
(105, 'HINDI'),
(102, 'MATHEMATICS'),
(106, 'ENGLISH'),
(101, 'BIOLOGY');

SELECT * FROM STUDENT;
SELECT * FROM COURSE;

SELECT *  
FROM STUDENT AS A
RIGHT JOIN COURSE AS B
ON A.ID = B.STUD_ID; /*RIGHT JOIN*/

SELECT *
FROM STUDENT AS A
LEFT JOIN COURSE AS B
ON A.ID = B.STUD_ID
WHERE B.STUD_ID IS NULL;/*LEFT JOIN*/

SELECT *  
FROM STUDENT AS A
RIGHT JOIN COURSE AS B
ON A.ID = B.STUD_ID
WHERE A.ID IS NULL;/*right exclusive join*/

SELECT *
FROM STUDENT AS A
LEFT JOIN COURSE AS B
ON A.ID = B.STUD_ID
WHERE B.STUD_ID IS NULL; /* left exclusive join*/

SELECT *  
FROM STUDENT AS A
RIGHT JOIN COURSE AS B
ON A.ID = B.STUD_ID
WHERE A.ID IS NULL
UNION
SELECT *
FROM STUDENT AS A
LEFT JOIN COURSE AS B
ON A.ID = B.STUD_ID
WHERE B.STUD_ID IS NULL; /*full exclusive join*/