1-20: Basic Join Types
-- 1. Inner Join (explicit syntax)
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 2. Inner Join (using old Oracle syntax)
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.department_id = d.dept_id;
-- 3. Left Outer Join (explicit syntax)
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id;
-- 4. Right Outer Join
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id;
-- 5. Full Outer Join
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;
-- 6. Cross Join (Cartesian product)
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- 7. Self Join to find employees with same manager
SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.emp_id;
-- 8. Natural Join (joins on all columns with the same name)
SELECT * FROM employees NATURAL JOIN departments;
-- 9. Using JOIN with USING clause
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d USING (department_id);
-- 10. Left Join with filter on right table column
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id AND d.location = 'New York';
-- 11. Right Join with filter on left table column
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id AND e.status = 'ACTIVE';
-- 12. Full Outer Join with WHERE clause filtering NULLs
SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL;
-- 13. Inner Join with multiple join conditions
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id AND e.status = d.status;
-- 14. Join with inequality condition
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id <> d.dept_id;
-- 15. Inner join using subquery
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN (SELECT dept_id, dept_name FROM departments WHERE location = 'NY') d
ON e.department_id = d.dept_id;
-- 16. Left join with subquery in ON condition
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id AND d.budget > 1000000;
-- 17. Cross join with WHERE filter to simulate inner join
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.department_id = d.dept_id;
-- 18. Self join to find employees in the same department
SELECT e1.emp_name, e2.emp_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id AND e1.emp_id <> e2.emp_id;
-- 19. Join with aliasing columns
SELECT e.emp_name AS employee, d.dept_name AS department
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 20. Left join with NULL check on right table column
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_name IS NULL;
________________________________________
21-40: Joins with Aggregate and Grouping
-- 21. Inner join with GROUP BY and aggregate
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 22. Left join with aggregate showing departments with zero employees
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id
GROUP BY d.dept_name;
-- 23. Join with HAVING clause
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 10;
-- 24. Join with AVG salary per department
SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 25. Join with MAX salary per department
SELECT d.dept_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 26. Join with SUM salary per department
SELECT d.dept_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 27. Join with COUNT DISTINCT
SELECT d.dept_name, COUNT(DISTINCT e.job_id) AS unique_jobs
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 28. Join with multiple aggregates
SELECT d.dept_name,
COUNT(e.emp_id) AS emp_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 29. Left join with aggregate and filter on aggregate result
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) = 0;
-- 30. Join and group by multiple columns
SELECT d.dept_name, e.job_id, COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name, e.job_id;
-- 31. Join with ROLLUP grouping
SELECT d.dept_name, e.job_id, COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY ROLLUP (d.dept_name, e.job_id);
-- 32. Join with CUBE grouping
SELECT d.dept_name, e.job_id, COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY CUBE (d.dept_name, e.job_id);
-- 33. Join with GROUPING function to differentiate NULLS
SELECT d.dept_name, e.job_id, COUNT(*) AS emp_count,
GROUPING(d.dept_name) AS grp_dept, GROUPING(e.job_id) AS grp_job
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY ROLLUP (d.dept_name, e.job_id);
-- 34. Join with HAVING multiple conditions
SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > 50000 AND COUNT(e.emp_id) > 5;
-- 35. Join with DISTINCT inside aggregate
SELECT d.dept_name, COUNT(DISTINCT e.manager_id) AS unique_managers
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
-- 36. Join with aggregate and ordering
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name
ORDER BY emp_count DESC;
-- 37. Join with LEFT JOIN and COALESCE for NULL handling
SELECT d.dept_name, COALESCE(COUNT(e.emp_id), 0) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id
GROUP BY d.dept_name;
-- 38. Join with nested subquery aggregate
SELECT d.dept_name,
(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.dept_id) AS emp_count
FROM departments d;
-- 39. Join with aggregate and filter on join table column
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.department_id AND e.status = 'ACTIVE'
GROUP BY d.dept_name;
-- 40. Join with CASE inside aggregate
SELECT d.dept_name,
COUNT(CASE WHEN e.salary > 70000 THEN 1 END) AS high_salary_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.dept_name;
________________________________________
41-60: Complex and Multiple Joins
-- 41. Join three tables
SELECT e.emp_name, d.dept_name, l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id;
-- 42. Left join three tables
SELECT e.emp_name, d.dept_name, l.location_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
LEFT JOIN locations l ON d.location_id = l.location_id;
-- 43. Inner join with three tables and filter
SELECT e.emp_name, d.dept_name, l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.region = 'North America';
-- 44. Join with four tables
SELECT e.emp_name, d.dept_name, j.job_title, l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN jobs j ON e.job_id = j.job_id
JOIN locations l ON d.location_id = l.location_id;
-- 45. Join with left join and inner join mixed
SELECT e.emp_name, d.dept_name, l.location_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id;
-- 46. Join with subquery returning table alias
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN (SELECT dept_id, dept_name FROM departments WHERE location_id = 100) d
ON e.department_id = d.dept_id;
-- 47. Join with inline view (subquery in FROM)
SELECT e.emp_name, d.dept_name
FROM employees e,
(SELECT dept_id, dept_name FROM departments WHERE location_id = 100) d
WHERE e.department_id = d.dept_id;
-- 48. Join with correlated subquery in SELECT
SELECT e.emp_name,
(SELECT d.dept_name FROM departments d WHERE d.dept_id = e.department_id) AS dept_name
FROM employees e;
-- 49. Join with EXISTS
SELECT e.emp_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.department_id);
-- 50. Join with NOT EXISTS to find employees without department
SELECT e.emp_name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.department_id);
-- 51. Join with UNION of two tables
SELECT e.emp_name FROM employees e
UNION
SELECT m.manager_name FROM managers m;
-- 52. Join with UNION ALL
SELECT e.emp_name FROM employees e
UNION ALL
SELECT m.manager_name FROM managers m;
-- 53. Join with MINUS (difference)
SELECT e.emp_name FROM employees e
MINUS
SELECT m.manager_name FROM managers m;
-- 54. Join with full outer join and complex ON condition
SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.dept_id AND e.status = d.status;
-- 55. Join with multiple OR conditions in ON clause
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id OR e.location_id = d.location_id;
-- 56. Join with lateral inline view (Oracle 12c+)
SELECT e.emp_name, d.dept_name
FROM employees e,
LATERAL (SELECT dept_name FROM departments d WHERE d.dept_id = e.department_id) d;
-- 57. Join with multiple join types in one query
SELECT e.emp_name, d.dept_name, j.job_title
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
INNER JOIN jobs j ON e.job_id = j.job_id;
-- 58. Join with USING clause and table aliases
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d USING(department_id);
-- 59. Join with LEFT JOIN and COALESCE in SELECT
SELECT e.emp_name, COALESCE(d.dept_name, 'No Dept') AS dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id;
-- 60. Join using NATURAL JOIN on multiple tables
SELECT * FROM employees NATURAL JOIN departments NATURAL JOIN locations;
________________________________________
61-80: Joins with Oracle Hints and Performance
-- 61. Inner join with USE_NL hint (nested loops)
SELECT /*+ USE_NL(d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 62. Inner join with USE_HASH hint (hash join)
SELECT /*+ USE_HASH(d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 63. Inner join with LEADING hint to specify join order
SELECT /*+ LEADING(e d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 64. Inner join with INDEX hint to use specific index
SELECT /*+ INDEX(e idx_emp_dept) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 65. Inner join with PARALLEL hint
SELECT /*+ PARALLEL(e 4) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 66. Join forcing full scan on one table
SELECT /*+ FULL(d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 67. Join forcing index scan on one table
SELECT /*+ INDEX(e idx_emp_name) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 68. Join with NO_MERGE hint on subquery
SELECT /*+ NO_MERGE(d) */ e.emp_name, d.dept_name
FROM employees e
JOIN (SELECT * FROM departments) d ON e.department_id = d.dept_id;
-- 69. Join using HASH_AJ hint (hash anti join)
SELECT /*+ HASH_AJ(e) */ e.emp_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_id IS NULL;
-- 70. Join with FULL OUTER JOIN and hint
SELECT /*+ USE_MERGE(e d) */ e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;
-- 71. Join with multiple hints in query
SELECT /*+ USE_NL(e d) LEADING(e d) INDEX(e idx_emp_name) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 72. Join with parallel and partition pruning
SELECT /*+ PARALLEL(e 8) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
WHERE d.location_id = 1700;
-- 73. Join using INLINE hint on subquery
SELECT /*+ INLINE(d) */ e.emp_name, d.dept_name
FROM employees e
JOIN (SELECT dept_id, dept_name FROM departments) d ON e.department_id = d.dept_id;
-- 74. Join with NO_EXPAND hint on views
SELECT /*+ NO_EXPAND(v) */ e.emp_name, v.dept_name
FROM employees e
JOIN my_dept_view v ON e.department_id = v.dept_id;
-- 75. Join forcing SORT_MERGE_JOIN
SELECT /*+ USE_MERGE(e d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 76. Join forcing NL_JOIN on multiple tables
SELECT /*+ USE_NL(e d l) LEADING(e d l) */ e.emp_name, d.dept_name, l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id;
-- 77. Join with hint to avoid Cartesian product
SELECT /*+ NO_CARTESIAN */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d;
-- 78. Join with hint to gather cardinality statistics
SELECT /*+ GATHER_PLAN_STATISTICS */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 79. Join using INDEX_DESC hint for descending index scan
SELECT /*+ INDEX_DESC(e idx_emp_name) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 80. Join with hint to force star
transformation on star schema
SELECT /*+ STAR_TRANSFORMATION(e d f) */ e.emp_name, d.dept_name, f.fact_value
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN fact_table f ON e.emp_id = f.emp_id;
---
### 81-100: Advanced Joins, Analytical Queries, and Misc
```sql
-- 81. Join with analytical function over partition
SELECT e.emp_name, d.dept_name,
ROW_NUMBER() OVER (PARTITION BY d.dept_name ORDER BY e.salary DESC) AS rank
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 82. Join with lateral and cross apply (Oracle 12c+)
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS APPLY (SELECT dept_name FROM departments d WHERE d.dept_id = e.department_id) d;
-- 83. Join with hierarchical query
SELECT e.emp_name, e.manager_id, LEVEL
FROM employees e
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;
-- 84. Join using MODEL clause
SELECT emp_id, salary, dept_id
FROM employees
MODEL
PARTITION BY (dept_id)
DIMENSION BY (emp_id)
MEASURES (salary)
RULES UPSERT (salary[ANY] = salary[CV(emp_id)] * 1.1);
-- 85. Join with PIVOT clause
SELECT * FROM
(SELECT e.emp_name, d.dept_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.dept_id)
PIVOT
(SUM(salary) FOR dept_name IN ('HR' AS HR, 'IT' AS IT, 'SALES' AS SALES));
-- 86. Join with UNPIVOT clause
SELECT * FROM
(SELECT e.emp_name, d.dept_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.dept_id)
UNPIVOT
(salary FOR dept_name IN (HR, IT, SALES));
-- 87. Join with flashback query (to get past data)
SELECT e.emp_name, d.dept_name
FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') e
JOIN departments d ON e.department_id = d.dept_id;
-- 88. Join with FETCH FIRST N ROWS
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
FETCH FIRST 10 ROWS ONLY;
-- 89. Join with SAMPLE clause for random sampling
SELECT e.emp_name, d.dept_name
FROM employees e SAMPLE(10)
JOIN departments d ON e.department_id = d.dept_id;
-- 90. Join with CONNECT BY and join filtering
SELECT e.emp_name, e.manager_id, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.emp_id = e.manager_id;
-- 91. Join with MATERIALIZE hint on subquery
SELECT /*+ MATERIALIZE */ e.emp_name, d.dept_name
FROM employees e
JOIN (SELECT dept_id, dept_name FROM departments) d ON e.department_id = d.dept_id;
-- 92. Join with inline PL/SQL function call
SELECT e.emp_name, d.dept_name, my_pkg.get_bonus(e.emp_id) AS bonus
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 93. Join with JSON_TABLE
SELECT e.emp_name, jt.*
FROM employees e,
JSON_TABLE(e.json_data, '$.departments[*]'
COLUMNS (dept_name VARCHAR2(50) PATH '$.name')) jt;
-- 94. Join with XMLTABLE
SELECT e.emp_name, xt.*
FROM employees e,
XMLTABLE('/departments/department'
PASSING e.xml_data
COLUMNS dept_name VARCHAR2(50) PATH 'name') xt;
-- 95. Join with multi-level nested joins
SELECT e.emp_name, d.dept_name, j.job_title, m.manager_name
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN jobs j ON e.job_id = j.job_id
LEFT JOIN managers m ON e.manager_id = m.manager_id;
-- 96. Join with DISTINCT ON (Oracle doesn't support directly, but emulate)
SELECT *
FROM (
SELECT e.emp_name, d.dept_name, ROW_NUMBER() OVER (PARTITION BY e.emp_name ORDER BY d.dept_name) rn
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
) WHERE rn = 1;
-- 97. Join with lateral join and recursive subquery factoring (WITH clause)
WITH dept_hierarchy(dept_id, parent_dept_id) AS (
SELECT dept_id, parent_dept_id FROM departments WHERE parent_dept_id IS NULL
UNION ALL
SELECT d.dept_id, d.parent_dept_id FROM departments d JOIN dept_hierarchy dh ON d.parent_dept_id = dh.dept_id
)
SELECT e.emp_name, dh.dept_id
FROM employees e
JOIN dept_hierarchy dh ON e.department_id = dh.dept_id;
-- 98. Join with multiset operations and collection usage
SELECT e.emp_name, CAST(COLLECT(d.dept_name) AS SYS.odcivarchar2list) AS depts
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY e.emp_name;
-- 99. Join with analytic aggregate partitioned by multiple columns
SELECT e.emp_name, d.dept_name,
COUNT(*) OVER (PARTITION BY d.dept_name, e.job_id) AS emp_count
FROM employees e
JOIN departments d ON e.department_id = d.dept_id;
-- 100. Join with lateral join and JSON data extraction (Oracle 19c+)
SELECT e.emp_name, jt.dept_name
FROM employees e
CROSS APPLY JSON_TABLE(e.json_data, '$.departments[*]'
COLUMNS (dept_name VARCHAR2(50) PATH '$.name')) jt;
No comments:
Post a Comment