Lesson Overview
In this lesson, students learn how to retrieve data from multiple related tables, match rows using different join techniques, understand outer joins, and summarize grouped data using CUBE.
INNER JOIN
USING
NATURAL JOIN
CROSS JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
SELF JOIN
GROUP BY
CUBE
Trainer
Mohamed Alswaify
Mobile: 0564842804
Join Syntax Overview
FROM table1 [INNER] JOIN table2 ON bool_expr
FROM table1 [INNER] JOIN table2 USING (column1, ...)
FROM table1 NATURAL JOIN table2
FROM table1 CROSS JOIN table2 WHERE bool_expr
FROM table1, table2 WHERE bool_expr
FROM table1 LEFT | RIGHT | FULL [OUTER] JOIN table2 ON bool_expr
Examples
Example 1 - GROUP BY CUBE
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE (department_id, job_id);
Example 2 - Department Salary Totals
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;
SELECT department_id, SUM(salary)
FROM employees
GROUP BY CUBE (department_id);
SELECT SUM(salary)
FROM employees;
Example 3 - Department and Job Grouping
SELECT department_id, job_id, SUM(salary), COUNT(*)
FROM employees
GROUP BY department_id, job_id;
Example 4 - Employees and Departments Join
SELECT first_name, department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
SELECT e.first_name, d.department_name, d.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
Example 5 - Employees and Jobs Join
SELECT e.first_name, j.job_title, j.job_id
FROM employees e JOIN jobs j
ON e.job_id = j.job_id;
Example 6 - Multiple Table Join
SELECT e.first_name, d.department_name, d.department_id, job_title
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id;
Example 7 - Join with Locations
SELECT e.first_name, d.department_name, city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id;
Example 8 - Department Addresses
Produce the addresses of all departments using LOCATIONS and COUNTRIES.
SELECT department_name,
l.location_id,
street_address,
city,
state_province,
country_name
FROM departments d JOIN locations l
ON d.location_id = l.location_id
JOIN countries c
ON l.country_id = c.country_id;
Example 9 - Department Manager Name
SELECT department_name, first_name
FROM departments d JOIN employees e
ON d.manager_id = e.employee_id;
Example 10 - Self Join Employees and Managers
SELECT e.first_name AS employee_name, m.first_name AS manager_name
FROM employees e JOIN employees m
ON e.manager_id = m.employee_id;
Example 11 - USING Keyword
SELECT e.first_name, d.department_name, d.department_id
FROM employees e JOIN departments d
USING (department_id);
SELECT e.first_name, j.job_title, job_id
FROM employees e JOIN jobs j
USING (job_id);
Example 12 - NATURAL JOIN
SELECT e.first_name, j.job_title, job_id
FROM employees e NATURAL JOIN jobs j;
Important: NATURAL JOIN may cause logic errors if tables share more than one column with the same name.
Example 13 - Cross Join Logic
SELECT e.first_name, d.department_name, d.department_id
FROM employees e CROSS JOIN departments d
WHERE e.department_id = d.department_id;
Example 14 - Old Join Syntax
SELECT e.first_name, d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND salary > 15000;
Example 15 - Multiple Old-Style Join
SELECT e.first_name, d.department_name, d.department_id, job_title, city
FROM employees e, departments d, jobs j, locations l
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND d.location_id = l.location_id;
Example 16 - Left Join
SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
Example 17 - Left / Right / Full Outer Join
SELECT department_name, first_name
FROM departments d LEFT OUTER JOIN employees e
ON d.manager_id = e.employee_id;
SELECT department_name, first_name
FROM departments d RIGHT JOIN employees e
ON d.manager_id = e.employee_id;
SELECT department_name, first_name
FROM departments d FULL JOIN employees e
ON d.manager_id = e.employee_id;
Example 18 - Oracle (+) Syntax
SELECT department_name, first_name
FROM departments d, employees e
WHERE d.manager_id = e.employee_id(+);
SELECT department_name, first_name
FROM departments d, employees e
WHERE d.manager_id(+) = e.employee_id;
Example 19 - Unmatched Rows
SELECT e.first_name, d.department_name
FROM employees e FULL JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
Questions
Write a query for the HR department to produce the addresses of all departments. Use the LOCATIONS tables. Show the location ID, street address, city, state or province, and country in the output.
Write the same department addresses query again, but use NATURAL JOIN.
The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto, using the USING keyword.
Task
Build a complete multi-table HR report that combines employees, departments, jobs, locations, and countries, then compare the result using JOIN ... ON, USING, and NATURAL JOIN.
Hint: Start with Employees + Departments, then add Jobs, then Locations, then Countries.