Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

JOINS (INNER JOIN/OUTER JOINS/SELF JOIN etc)

Got it — you want a single clean BASE CODE, and then all joins must use ONLY that same base code, instead of recreating tables again and again.

Here is the proper version:

✅ BASE CODE (use this once for everything)

(THIS will be the only code block for entire JOINS lesson)

-- Create Students Table
CREATE TABLE students (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50)
);

-- Create Courses Table
CREATE TABLE courses (
    id INT IDENTITY(1,1) PRIMARY KEY,
    course VARCHAR(100)
);

-- Create Enrollments Table
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Insert sample students
INSERT INTO students (name) VALUES
('Anya'),
('Raj'),
('Sara');

-- Insert sample courses
INSERT INTO courses (course) VALUES
('Math'),
('History'),
('Physics');

-- Insert enrollments
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1),   -- Anya → Math
(1, 2),   -- Anya → History
(2, 1),   -- Raj → Math
(3, 3);   -- Sara → Physics

1️⃣ INNER JOIN

→ Only matching rows

SELECT students.name, courses.course
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

2️⃣ LEFT JOIN

→ All students + matched courses

SELECT students.name, courses.course
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.id;

3️⃣ RIGHT JOIN

→ All courses + matched students

SELECT students.name, courses.course
FROM students
RIGHT JOIN enrollments ON students.id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.id;

4️⃣ FULL OUTER JOIN

(SQL Server supports it)

SELECT students.name, courses.course
FROM students
FULL OUTER JOIN enrollments ON students.id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.id;

5️⃣ SELF JOIN

(using students table)

SELECT s1.name AS student1, s2.name AS student2
FROM students s1
JOIN students s2 ON s1.id <> s2.id;