Joins & Set

This section details how to merge datasets horizontally using various JOIN configurations (including Self-Joins) and vertically using UNION structures.

---

1. Dataset Setup

CREATE DATABASE college1;
USE college1;

CREATE TABLE student(
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO student (id, name)
VALUES
(101, "adam"),
(102, "bob"),
(103, "casey");

CREATE TABLE course(
    id INT PRIMARY KEY,
    course VARCHAR(50)
);

INSERT INTO course (id, course)
VALUES
(102, "english"),
(105, "maths"),
(103, "science"),
(107, "IT");

SELECT * FROM student;
SELECT * FROM course;

2. Joining Data Tables

-- Inner Join (Standard)
SELECT * FROM student
INNER JOIN course
ON student.id = course.id;

-- Inner Join (With Table Aliases)
SELECT * FROM student as s
INNER JOIN course as c
ON s.id = c.id;

-- Left Join
SELECT * FROM student as s
LEFT JOIN course as c
ON s.id = c.id;

-- Right Join
SELECT * FROM student as s
RIGHT JOIN course as c
ON s.id = c.id;

-- Full Outer Join Emulation via UNION
SELECT * FROM student as s
LEFT JOIN course as c
ON s.id = c.id
UNION
SELECT * FROM student as s
RIGHT JOIN course as c
ON s.id = c.id;

-- Exclusive Full Join (Symmetric Difference)
SELECT * FROM student as s
LEFT JOIN course as c
ON s.id = c.id
WHERE c.id IS NULL
UNION
SELECT * FROM student as s
RIGHT JOIN course as c
ON s.id = c.id
WHERE s.id IS NULL;
3. Hierarchical Structures (Self-Joins)
SQL
CREATE TABLE employee(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT 
);

INSERT INTO employee (id, name, manager_id)
VALUES
(101, "adam", 103),
(102, "bob", 104),
(103, "casey", NULL),
(104, "donald", 103);

-- Map worker entities to management entities
SELECT a.name as manager_name, b.name
FROM employee as a
JOIN employee as b
ON a.id = b.manager_id;
4. Set Operators: UNION vs UNION ALL
SQL
-- Dedupes matching result records
SELECT name FROM employee
UNION
SELECT name FROM employee;

-- Retains all structural duplicates
SELECT name FROM employee
UNION ALL
SELECT name FROM employee;