What is Joining Tables?
Joining Tables is the fundamental SQL operation that combines rows from two or more tables based on a related column between them. In PL/SQL, this is primarily achieved through SQL statements embedded within procedural code blocks.
The core purpose is to retrieve related data distributed across normalized tables, solving the problem of data fragmentation by creating cohesive result sets from separated relational data structures.
INNER JOIN
Returns only the rows that have matching values in both tables. Records without matches in either table are excluded from the result set.
DECLARE
CURSOR emp_dept_cur IS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
v_emp_id employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
v_dname departments.department_name%TYPE;
BEGIN
OPEN emp_dept_cur;
LOOP
FETCH emp_dept_cur INTO v_emp_id, v_lname, v_dname;
EXIT WHEN emp_dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Emp: ' || v_emp_id || ', ' || v_lname || ', Dept: ' || v_dname);
END LOOP;
CLOSE emp_dept_cur;
END;
/
LEFT OUTER JOIN
Returns all rows from the left table and matched rows from the right table. Unmatched right table rows contain NULL values.
Use LEFT OUTER JOIN when you want all records from the primary table, even if there are no matches in the secondary table.
DECLARE
CURSOR emp_left_join_cur IS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
v_emp_id employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
v_dname departments.department_name%TYPE;
BEGIN
FOR rec IN emp_left_join_cur LOOP
-- Employees without departments will show NULL department_name
DBMS_OUTPUT.PUT_LINE('Emp: ' || rec.employee_id || ', Name: ' || rec.last_name ||
', Dept: ' || NVL(rec.department_name, 'No Department'));
END LOOP;
END;
/
RIGHT OUTER JOIN
Returns all rows from the right table and matched rows from the left table. Unmatched left table rows contain NULL values.
DECLARE
CURSOR dept_right_join_cur IS
SELECT d.department_id, d.department_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
v_dept_id departments.department_id%TYPE;
v_dname departments.department_name%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
FOR rec IN dept_right_join_cur LOOP
-- Departments without employees will show NULL last_name
IF rec.last_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Dept: ' || rec.department_name || ' (No employees)');
ELSE
DBMS_OUTPUT.PUT_LINE('Dept: ' || rec.department_name || ', Emp: ' || rec.last_name);
END IF;
END LOOP;
END;
/
FULL OUTER JOIN
Returns all rows when there’s a match in either table. Combines LEFT and RIGHT OUTER JOIN results.
DECLARE
CURSOR full_join_cur IS
SELECT
NVL(e.employee_id, 0) as emp_id,
NVL(e.last_name, 'No Employee') as emp_name,
NVL(d.department_name, 'No Department') as dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
BEGIN
FOR rec IN full_join_cur LOOP
DBMS_OUTPUT.PUT_LINE('Emp ID: ' || rec.emp_id ||
', Name: ' || rec.emp_name ||
', Dept: ' || rec.dept_name);
END LOOP;
END;
/
SELF JOIN
Joins a table to itself, typically to represent hierarchical relationships or compare rows within the same table.
SELF JOINs are perfect for representing organizational hierarchies, like employee-manager relationships.
DECLARE
CURSOR manager_cur IS
SELECT e.employee_id, e.last_name as emp_name,
m.last_name as manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
BEGIN
FOR rec IN manager_cur LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.emp_name ||
', Manager: ' || NVL(rec.manager_name, 'Top Level'));
END LOOP;
END;
/
CROSS JOIN
Produces Cartesian product - every row from first table joined with every row from second table.
Use CROSS JOIN cautiously due to potential large result sets. Always limit results when appropriate.
DECLARE
CURSOR cross_join_cur IS
SELECT e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d
WHERE ROWNUM <= 10; -- Limit results for demonstration
BEGIN
FOR rec IN cross_join_cur LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.last_name ||
', Possible Dept: ' || rec.department_name);
END LOOP;
END;
/
JOIN ON Clause
Explicitly specifies the join condition using column comparisons, allowing complex conditions and multiple predicates.
DECLARE
CURSOR complex_join_cur IS
SELECT e.employee_id, e.last_name, j.job_title, d.department_name
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
AND j.min_salary > 5000 -- Additional condition in ON clause
JOIN departments d ON e.department_id = d.department_id
AND d.location_id = 1700; -- Filter departments by location
BEGIN
FOR rec IN complex_join_cur LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || rec.employee_id || ', ' || rec.last_name ||
', ' || rec.job_title || ', ' || rec.department_name);
END LOOP;
END;
/
JOIN USING Clause
Simplified syntax when joining on columns with identical names in both tables.
USING clause requires that the column name is identical in both tables being joined.
DECLARE
CURSOR using_join_cur IS
SELECT employee_id, last_name, department_name
FROM employees
JOIN departments USING (department_id); -- Column name must be identical in both tables
BEGIN
FOR rec IN using_join_cur LOOP
DBMS_OUTPUT.PUT_LINE('Employee ' || rec.last_name ||
' works in ' || rec.department_name);
END LOOP;
END;
/
Why is Joining Tables Important?
Schema Normalization
Normalization Principle: Enables efficient database design by allowing data separation into normalized tables while maintaining query capability.
Scalability Pattern: Proper join usage reduces data redundancy and improves query performance through indexed foreign key relationships.
Data Integrity
ACID Compliance: Maintains referential integrity by ensuring relationships between tables are explicitly defined and enforced through join conditions.
Advanced Nuances
Partitioned Outer Joins
Oracle’s extension allowing outer joins to respect analytic functions’ partitioning:
SELECT e.last_name, d.department_name,
COUNT(*) OVER (PARTITION BY d.department_id) as dept_count
FROM employees e
PARTITION BY (e.department_id)
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
Multi-Table Join Order Optimization
Senior developers understand that join order affects performance. Use hints when necessary.
-- Oracle's cost-based optimizer may rewrite this, but explicit ordering helps
SELECT /*+ ORDERED */ e.last_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;
Anti-Joins and Semi-Joins
Specialized join patterns using NOT EXISTS or NOT IN for exclusion logic:
-- Find employees without departments (anti-join pattern)
SELECT employee_id, last_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM departments d
WHERE d.department_id = e.department_id
);
How This Fits the Roadmap
Joining Tables serves as the foundational prerequisite within the “Advanced SQL Features” section.
Essential For Understanding:
- Hierarchical Queries - Self-joins are building blocks for recursive WITH clauses and CONNECT BY
- Analytic Functions - Join results provide the dataset for advanced windowing and analytical processing
- Materialized Views - Understanding joins is critical for creating efficient materialized views
- Advanced Partitioning - Join strategies directly impact partitioned table access patterns
Unlocks:
- Flashback Query joins
- Cross-schema joins
- Database link joins across distributed systems