-- Дані
Departments: Software Dev, Mathematics, Physics
Teachers: Sophia (Software Dev), Henry (Software Dev), Emma (Mathematics)

-- INNER JOIN
SELECT T.FirstName, D.Name
FROM Teachers AS T
INNER JOIN Departments AS D
    ON T.DepartmentId = D.Id;

-- Результат: 3 рядки
-- ❌ Physics НЕ відображається (немає викладачів)

SELECT columns
FROM table1
LEFT JOIN table2
    ON table1.column = table2.column;

-- Або повна назва
LEFT OUTER JOIN

SELECT
    D.Name AS Department,
    T.FirstName + ' ' + T.LastName AS Teacher
FROM Departments AS D  -- Ліва таблиця (всі рядки)
LEFT JOIN Teachers AS T  -- Права таблиця (тільки збіги)
    ON D.Id = T.DepartmentId
ORDER BY D.Name;

Departments (Ліва - ВСІ)     Teachers (Права - збіги)
┌────┬──────────────┐         ┌────┬─────────┬────────┐
│ Id │ Name         │         │ Id │ Name    │ DeptId │
├────┼──────────────┤         ├────┼─────────┼────────┤
│ 1  │ Software Dev │ ────┬───│ 1  │ Sophia  │   1    │
│ 2  │ Mathematics  │ ────┼───│ 2  │ Henry   │   1    │
│ 3  │ Physics      │ ✗   └───│ 3  │ Emma    │   2    │
└────┴──────────────┘         └────┴─────────┴────────┘

        ↓ LEFT JOIN ↓

Результат (ВСІ кафедри + збіги)
┌──────────────┬─────────┐
│ Department   │ Teacher │
├──────────────┼─────────┤
│ Software Dev │ Sophia  │
│ Software Dev │ Henry   │
│ Mathematics  │ Emma    │
│ Physics      │ NULL    │ ← Немає викладачів
└──────────────┴─────────┘

SELECT columns
FROM table1
RIGHT JOIN table2
    ON table1.column = table2.column;

-- Або повна назва
RIGHT OUTER JOIN

-- Студенти БЕЗ групи (якщо такі є)
INSERT INTO Students (FirstName, LastName, GroupId) VALUES
('John', 'Doe', 1),
('Jane', 'Smith', 2),
('Orphan', 'Student', NULL);  -- Студент БЕЗ групи!

-- RIGHT JOIN - показати ВСІ студентів
SELECT
    G.Name AS GroupName,
    S.FirstName + ' ' + S.LastName AS Student
FROM Groups AS G          -- Ліва (може бути NULL)
RIGHT JOIN Students AS S  -- Права (ВСІ рядки)
    ON G.Id = S.GroupId
ORDER BY G.Name, S.LastName;

SELECT columns
FROM table1
FULL OUTER JOIN table2
    ON table1.column = table2.column;

-- Або коротко
FULL JOIN

-- Дані
Departments: Software Dev (Id=1), Mathematics (Id=2), Physics (Id=3)
Teachers: Sophia (DeptId=1), Henry (DeptId=1), Emma (DeptId=2), John (DeptId=NULL)

-- FULL OUTER JOIN
SELECT
    D.Name AS Department,
    T.FirstName + ' ' + T.LastName AS Teacher
FROM Departments AS D
FULL OUTER JOIN Teachers AS T
    ON D.Id = T.DepartmentId
ORDER BY D.Name, T.FirstName;

Departments               Teachers
┌────┬──────────────┐    ┌────┬──────┬────────┐
│ Id │ Name         │    │ Id │ Name │ DeptId │
├────┼──────────────┤    ├────┼──────┼────────┤
│ 1  │ Software Dev │────│ 1  │Sophia│   1    │
│ 2  │ Mathematics  │────│ 2  │Henry │   1    │
│ 3  │ Physics      │ ✗  │ 3  │Emma  │   2    │
└────┴──────────────┘    │ 4  │John  │  NULL  │✗
                         └────┴──────┴────────┘

        ↓ FULL OUTER JOIN ↓

Результат (ВСІ з обох таблиць)
┌──────────────┬─────────┐
│ Department   │ Teacher │
├──────────────┼─────────┤
│ Software Dev │ Sophia  │
│ Software Dev │ Henry   │
│ Mathematics  │ Emma    │
│ Physics      │ NULL    │ ← Кафедра БЕЗ викладачів
│ NULL         │ John    │ ← Викладач БЕЗ кафедри
└──────────────┴─────────┘

SELECT
    D.Name AS Department,
    T.FirstName AS Teacher
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId;

-- Результат містить NULL для кафедр без викладачів

SELECT
    D.Name AS Department,
    COALESCE(T.FirstName, 'Немає викладачів') AS Teacher
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId;

SELECT
    D.Name AS Department,
    ISNULL(T.FirstName, 'N/A') AS Teacher
FROM Departments AS D
LEFT JOIN Teams AS T
    ON D.Id = T.DepartmentId;

SELECT
    D.Name AS Department,
    CASE
        WHEN T.FirstName IS NULL THEN 'Вакансія'
        ELSE T.FirstName + ' ' + T.LastName
    END AS Teacher
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId;

SELECT D.Name AS EmptyDepartment
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId
WHERE T.Id IS NULL;  -- Немає викладачів

SELECT
    S.FirstName,
    S.LastName,
    'Не зарахований до групи' AS IssueStatus
FROM Students AS S
LEFT JOIN Groups AS G
    ON S.GroupId = G.Id
WHERE G.Id IS NULL;

SELECT
    G.Name AS EmptyGroup,
    COUNT(S.Id) AS StudentsCount  -- Буде 0
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
HAVING COUNT(S.Id) = 0;

-- Знайти ВСІ невідповідності між таблицями
SELECT
    CASE
        WHEN D.Id IS NULL THEN 'Викладач без кафедри'
        WHEN T.Id IS NULL THEN 'Кафедра без викладачів'
        ELSE 'OK'
    END AS Status,
    COALESCE(D.Name, 'N/A') AS Department,
    COALESCE(T.FirstName + ' ' + T.LastName, 'N/A') AS Teacher
FROM Departments AS D
FULL OUTER JOIN Teachers AS T
    ON D.Id = T.DepartmentId
WHERE D.Id IS NULL OR T.Id IS NULL;  -- Тільки проблеми

-- Показати ВСІ предмети з викладачами (якщо є)
SELECT
    S.Name AS Subject,
    COALESCE(T.FirstName + ' ' + T.LastName, 'Наразі не призначено') AS Teacher
FROM Subjects AS S
LEFT JOIN TeachersSubjects AS TS
    ON S.Id = TS.SubjectId
LEFT JOIN Teachers AS T
    ON TS.TeacherId = T.Id
ORDER BY S.Name;

-- Кількість студентів у кожній групі (включаючи порожні)
SELECT
    G.Name AS GroupName,
    COUNT(S.Id) AS StudentsCount  -- NULL не враховуються!
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
ORDER BY StudentsCount DESC;

-- Середній бал по кафедрах (включаючи кафедри без оцінок)
SELECT
    D.Name AS Department,
    AVG(A.Grade) AS AvgGrade,
    COUNT(A.Id) AS GradesCount
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId
LEFT JOIN Assessments AS A
    ON T.Id = A.TeacherId
GROUP BY D.Name
ORDER BY AvgGrade DESC;

-- Показати ВСІ факультети → групи → студентів
SELECT
    F.Name AS Faculty,
    COALESCE(G.Name, 'Немає груп') AS GroupName,
    COALESCE(S.FirstName + ' ' + S.LastName, 'Немає студентів') AS Student
FROM Faculties AS F
LEFT JOIN Groups AS G
    ON F.Id = G.FacultyId
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
ORDER BY F.Name, G.Name, S.LastName;

-- Показати студентів з групами (обов'язково) та оцінками (опціонально)
SELECT
    S.FirstName,
    G.Name AS GroupName,        -- INNER JOIN (обов'язково)
    COALESCE(AVG(A.Grade), 0) AS AvgGrade  -- LEFT JOIN (опціонально)
FROM Students AS S
INNER JOIN Groups AS G          -- Тільки студенти З групою
    ON S.GroupId = G.Id
LEFT JOIN Assessments AS A      -- Оцінки можуть бути відсутні
    ON S.Id = A.StudentId
GROUP BY S.FirstName, G.Name;