JOIN clause

table_references:
    table_reference [, table_reference ...]

table_reference:
    table_factor
    | joined_table

table_factor:
    table_name [PARTITION (partition_name_list)] [[AS] table_alias_name]
    | table_subquery [AS] table_alias_name
    | (table_references)

partition_name_list:
    partition_name [, partition_name ...]

joined_table:
    table_reference
    | table_reference1, table_reference2 [WHERE join_condition]
    | table_reference1
      {INNER} JOIN table_reference2
    | table_reference1
      {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2
      {ON join_condition | WHERE join_condition}
    | table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2

join_condition:
    expression
    | column_name1 = column_name2

-- Create the countries table.
CREATE TABLE countries (
    country_id VARCHAR2(2) PRIMARY KEY,
    country_name VARCHAR2(40),
    region_id NUMBER
);

-- Create the locations table.
CREATE TABLE locations (
    location_id NUMBER PRIMARY KEY,
    street_address VARCHAR2(40),
    postal_code VARCHAR2(12),
    city VARCHAR2(30),
    state_province VARCHAR2(25),
    country_id VARCHAR2(2),
    CONSTRAINT loc_country_fk FOREIGN KEY (country_id)
    REFERENCES countries(country_id)
);

-- Create the departments table.
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(30),
    manager_id NUMBER,
    location_id NUMBER,
    CONSTRAINT dept_loc_fk FOREIGN KEY (location_id)
    REFERENCES locations(location_id)
);

-- Create the employees table.
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER,
    department_id NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
    REFERENCES departments(department_id),
    CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
    REFERENCES employees(employee_id)
);

-- Insert country data.
INSERT INTO countries VALUES ('US', 'United States', 2);
INSERT INTO countries VALUES ('CA', 'Canada', 2);

-- Insert location data.
INSERT INTO locations VALUES (
    1000,
    '1297 Via Cola di Rie',
    '00989',
    'Roma',
    'Lazio',
    'US'
);
INSERT INTO locations VALUES (
    1100,
    '93091 Sulfur Springs',
    '10925',
    'Toronto',
    'Ontario',
    'CA'
);

-- Insert department data.
INSERT INTO departments VALUES (10, 'Administration', NULL, 1000);
INSERT INTO departments VALUES (20, 'Marketing', NULL, 1100);

-- Insert employee data.
INSERT INTO employees VALUES (
    100,
    'Steven',
    'King',
    'SKING',
    '515.123.4567',
    TO_DATE('17-06-2003', 'DD-MM-YYYY'),
    'AD_PRES',
    24000,
    NULL,
    NULL,
    10
);
INSERT INTO employees VALUES (
    101,
    'Neena',
    'Kochhar',
    'NKOCHHAR',
    '515.123.4568',
    TO_DATE('21-09-2005', 'DD-MM-YYYY'),
    'AD_VP',
    17000,
    NULL,
    100,
    10
);

-- SQL query examples

-- 1. Basic inner join
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 2. Join multiple tables
SELECT e.employee_id,
       e.first_name,
       d.department_name,
       l.city,
       c.country_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
INNER JOIN countries c ON l.country_id = c.country_id;

-- 3. Left outer join
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- 4. Self join (to query employees and their managers)
SELECT
    e1.employee_id,
    e1.first_name || ' ' || e1.last_name AS employee_name,
    e2.first_name || ' ' || e2.last_name AS manager_name
FROM employees e1
LEFT OUTER JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- RIGHT JOIN (right outer join)
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- FULL OUTER JOIN (full outer join)
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- JOIN using the USING clause
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);