/*===============================================================
SQL JOINS
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 03A β SQL JOINS
Topics Covered
--------------
β’ Join fundamentals
β’ INNER JOIN
β’ ANSI joins
β’ Oracle join syntax
β’ Multi-table joins
β’ HR schema join examples
===============================================================*/
/*---------------------------------------------------------------
JOIN INTRODUCTION
---------------------------------------------------------------*/
/*
Joins combine rows from two or more tables.
Types of joins
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
CROSS JOIN
*/
/*---------------------------------------------------------------
QUESTION 1
Display employee names with department names
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 2
Display employee and department information
---------------------------------------------------------------*/
SELECT
e.first_name,
e.last_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 3
Employees with job titles
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
j.job_title
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id;
/*---------------------------------------------------------------
QUESTION 4
Employees with department and job
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
d.department_name,
j.job_title
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.jobs j
ON e.job_id = j.job_id;
/*---------------------------------------------------------------
QUESTION 5
Employees with department location
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
l.city
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id;
/*---------------------------------------------------------------
QUESTION 6
Employees with department and country
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
c.country_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id;
/*---------------------------------------------------------------
QUESTION 7
Employees with region information
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
r.region_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id
JOIN hr.regions r
ON c.region_id = r.region_id;
/*---------------------------------------------------------------
ORACLE PROPRIETARY JOIN SYNTAX
---------------------------------------------------------------*/
/*
Older Oracle syntax uses WHERE clause.
*/
/*---------------------------------------------------------------
QUESTION 8
Join using WHERE clause
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e,
hr.departments d
WHERE e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 9
Employee job title using WHERE join
---------------------------------------------------------------*/
SELECT
e.first_name,
j.job_title
FROM hr.employees e,
hr.jobs j
WHERE e.job_id = j.job_id;
/*---------------------------------------------------------------
MULTI-TABLE JOINS
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 10
Employees with department and job
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
j.job_title
FROM hr.employees e,
hr.departments d,
hr.jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id;
/*---------------------------------------------------------------
QUESTION 11
Employees with department city
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
l.city
FROM hr.employees e,
hr.departments d,
hr.locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
/*---------------------------------------------------------------
QUESTION 12
Employees with department country
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
c.country_name
FROM hr.employees e,
hr.departments d,
hr.locations l,
hr.countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;
/*---------------------------------------------------------------
QUESTION 13
Employees with full geographic info
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
l.city,
c.country_name,
r.region_name
FROM hr.employees e,
hr.departments d,
hr.locations l,
hr.countries c,
hr.regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id;
/*---------------------------------------------------------------
JOIN FILTERING
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 14
Employees working in IT department
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'IT';
/*---------------------------------------------------------------
QUESTION 15
Employees earning above 10000 with department names
---------------------------------------------------------------*/
SELECT
e.first_name,
e.salary,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
WHERE e.salary > 10000;
/*---------------------------------------------------------------
QUESTION 16
Employees hired after 2005 with department
---------------------------------------------------------------*/
SELECT
e.first_name,
e.hire_date,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
WHERE e.hire_date > DATE '2005-01-01';
/*---------------------------------------------------------------
QUESTION 17
Employees with department sorted by department
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
ORDER BY d.department_name;
/*---------------------------------------------------------------
QUESTION 18
Employees with job titles sorted by salary
---------------------------------------------------------------*/
SELECT
e.first_name,
j.job_title,
e.salary
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
ORDER BY e.salary DESC;
/*---------------------------------------------------------------
JOIN PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Join Best Practices
1 Join on indexed columns
2 Avoid Cartesian products
3 Use ANSI JOIN syntax
4 Filter rows early
5 Use EXPLAIN PLAN to verify joins
*/
/*---------------------------------------------------------------
END OF PART 03A
---------------------------------------------------------------*/
/*===============================================================
PART 03B β ADVANCED SQL JOINS
Continuation of PART 03A
Topics Covered
--------------
β’ LEFT OUTER JOIN
β’ RIGHT OUTER JOIN
β’ FULL OUTER JOIN
β’ Oracle (+) joins
β’ SELF JOIN
β’ CROSS JOIN
β’ Advanced join examples
===============================================================*/
/*---------------------------------------------------------------
LEFT OUTER JOIN
---------------------------------------------------------------*/
/*
LEFT JOIN returns all rows from left table
and matching rows from right table.
If no match exists β NULL values returned.
*/
/*---------------------------------------------------------------
QUESTION 19
Display all employees and their departments
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name,
d.department_name
FROM hr.employees e
LEFT JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 20
Employees and department city
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name,
l.city
FROM hr.employees e
LEFT JOIN hr.departments d
ON e.department_id = d.department_id
LEFT JOIN hr.locations l
ON d.location_id = l.location_id;
/*---------------------------------------------------------------
QUESTION 21
Departments even without employees
---------------------------------------------------------------*/
SELECT
d.department_name,
e.first_name
FROM hr.departments d
LEFT JOIN hr.employees e
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
RIGHT OUTER JOIN
---------------------------------------------------------------*/
/*
RIGHT JOIN returns all rows from right table.
*/
/*---------------------------------------------------------------
QUESTION 22
Departments and employees (right join)
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e
RIGHT JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 23
Department city even if no employees
---------------------------------------------------------------*/
SELECT
e.first_name,
l.city
FROM hr.employees e
RIGHT JOIN hr.departments d
ON e.department_id = d.department_id
RIGHT JOIN hr.locations l
ON d.location_id = l.location_id;
/*---------------------------------------------------------------
FULL OUTER JOIN
---------------------------------------------------------------*/
/*
Returns rows from both tables.
*/
/*---------------------------------------------------------------
QUESTION 24
Employees and departments (full join)
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e
FULL OUTER JOIN hr.departments d
ON e.department_id = d.department_id;
/*---------------------------------------------------------------
QUESTION 25
Departments and employees including unmatched rows
---------------------------------------------------------------*/
SELECT
d.department_name,
e.first_name
FROM hr.departments d
FULL OUTER JOIN hr.employees e
ON d.department_id = e.department_id;
/*---------------------------------------------------------------
ORACLE OUTER JOIN (+) SYNTAX
---------------------------------------------------------------*/
/*
Legacy Oracle syntax.
Modern code should prefer ANSI joins.
*/
/*---------------------------------------------------------------
QUESTION 26
Left outer join using (+)
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e,
hr.departments d
WHERE e.department_id = d.department_id(+);
/*---------------------------------------------------------------
QUESTION 27
Right outer join using (+)
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e,
hr.departments d
WHERE e.department_id(+) = d.department_id;
/*---------------------------------------------------------------
SELF JOIN
---------------------------------------------------------------*/
/*
A self join joins a table to itself.
Common use β employee manager hierarchy.
*/
/*---------------------------------------------------------------
QUESTION 28
Employees and their managers
---------------------------------------------------------------*/
SELECT
e.first_name employee,
m.first_name manager
FROM hr.employees e
LEFT JOIN hr.employees m
ON e.manager_id = m.employee_id;
/*---------------------------------------------------------------
QUESTION 29
Employee manager relationship
---------------------------------------------------------------*/
SELECT
e.employee_id,
e.first_name employee,
m.employee_id manager_id,
m.first_name manager
FROM hr.employees e
LEFT JOIN hr.employees m
ON e.manager_id = m.employee_id;
/*---------------------------------------------------------------
QUESTION 30
Employees reporting to same manager
---------------------------------------------------------------*/
SELECT
e1.first_name employee1,
e2.first_name employee2,
e1.manager_id
FROM hr.employees e1
JOIN hr.employees e2
ON e1.manager_id = e2.manager_id
AND e1.employee_id <> e2.employee_id;
/*---------------------------------------------------------------
CROSS JOIN
---------------------------------------------------------------*/
/*
CROSS JOIN produces Cartesian product.
*/
/*---------------------------------------------------------------
QUESTION 31
Cartesian product of employees and departments
---------------------------------------------------------------*/
SELECT
e.first_name,
d.department_name
FROM hr.employees e
CROSS JOIN hr.departments d;
/*---------------------------------------------------------------
QUESTION 32
Cartesian product of jobs and departments
---------------------------------------------------------------*/
SELECT
j.job_title,
d.department_name
FROM hr.jobs j
CROSS JOIN hr.departments d;
/*---------------------------------------------------------------
ADVANCED JOIN FILTERING
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 33
Employees working in Europe region
---------------------------------------------------------------*/
SELECT
e.first_name,
r.region_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id
JOIN hr.regions r
ON c.region_id = r.region_id
WHERE r.region_name = 'Europe';
/*---------------------------------------------------------------
QUESTION 34
Employees in Seattle city
---------------------------------------------------------------*/
SELECT
e.first_name,
l.city
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
WHERE l.city = 'Seattle';
/*---------------------------------------------------------------
QUESTION 35
Employees in United States
---------------------------------------------------------------*/
SELECT
e.first_name,
c.country_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id
WHERE c.country_name = 'United States';
/*---------------------------------------------------------------
QUESTION 36
Employees working in Asia region
---------------------------------------------------------------*/
SELECT
e.first_name,
r.region_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id
JOIN hr.regions r
ON c.region_id = r.region_id
WHERE r.region_name = 'Asia';
/*---------------------------------------------------------------
QUESTION 37
Employees and salary sorted by region
---------------------------------------------------------------*/
SELECT
e.first_name,
e.salary,
r.region_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id
JOIN hr.countries c
ON l.country_id = c.country_id
JOIN hr.regions r
ON c.region_id = r.region_id
ORDER BY r.region_name;
/*---------------------------------------------------------------
QUESTION 38
Employees and job salary range
---------------------------------------------------------------*/
SELECT
e.first_name,
j.job_title,
j.min_salary,
j.max_salary
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id;
/*---------------------------------------------------------------
QUESTION 39
Employees earning above job minimum salary
---------------------------------------------------------------*/
SELECT
e.first_name,
e.salary,
j.min_salary
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
WHERE e.salary > j.min_salary;
/*---------------------------------------------------------------
QUESTION 40
Employees earning above job maximum salary
---------------------------------------------------------------*/
SELECT
e.first_name,
e.salary,
j.max_salary
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
WHERE e.salary > j.max_salary;
/*---------------------------------------------------------------
JOIN PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Join Optimization
1 Join using primary keys and foreign keys
2 Avoid unnecessary joins
3 Use indexes on join columns
4 Use EXPLAIN PLAN to analyze joins
5 Filter rows before joins when possible
*/
/*---------------------------------------------------------------
END OF PART 03
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/