■■ Advanced SQL Relational Operators – Notes
Introduction
Relational operators in Advanced SQL extend basic set operations and joins. They allow
combining, comparing, and filtering datasets in complex queries, useful for data analysis,
optimization, and reporting.
Set Operators
1. UNION – Combines results of two queries, removes duplicates.
2. UNION ALL – Combines results, keeps duplicates (faster).
3. INTERSECT – Returns only rows common to both queries.
4. EXCEPT / MINUS – Returns rows in first query but not in second.
Join Operators
1. INNER JOIN – Returns only matching rows.
2. LEFT JOIN – All rows from left + matches from right.
3. RIGHT JOIN – All rows from right + matches from left.
4. FULL JOIN – All rows from both tables.
5. CROSS JOIN – Cartesian product.
6. SELF JOIN – Table joined with itself.
7. NATURAL JOIN – Joins automatically on common attributes.
Advanced Relational Operators
• Division (÷ in Relational Algebra): Implemented in SQL using NOT EXISTS or GROUP BY
HAVING.
• Subquery Comparisons:
– IN / NOT IN: membership.
– ANY / SOME: compare with any value.
– ALL: compare with all values.
• Common Table Expressions (CTEs): Temporary result sets, support recursion.
Examples
• UNION: SELECT course_id FROM students UNION SELECT course_id FROM courses;
• INNER JOIN: SELECT s.name, c.course_name FROM students s INNER JOIN courses c ON
s.course_id = c.course_id;
• EXCEPT: SELECT course_id FROM courses EXCEPT SELECT course_id FROM students;
Applications
• Business Intelligence: combine datasets.
• Data Warehousing: merge fact tables and dimensions.
• Reporting Systems: find common, unique, or missing data.
• Data Analysis: complex filtering with ANY, ALL, EXISTS.
Quick Recap
✔ Set Operators: UNION, UNION ALL, INTERSECT, EXCEPT/MINUS.
✔ Joins: INNER, OUTER (LEFT, RIGHT, FULL), CROSS, SELF, NATURAL.
✔ Advanced: Division, Subquery operators (IN, ANY, ALL).
✔ SQL enhances relational algebra with flexibility for large-scale queries.
