Oracle Joining refers to the process of becoming an employee of Oracle Corporation, one of the world’s largest software companies. This typically involves submitting an application for a specific job opening, participating in interviews, and receiving a job offer from the company. Once an individual accepts the job offer and completes any necessary onboarding processes, they officially become an employee of Oracle. Oracle is known for its competitive salaries, comprehensive benefits packages, and opportunities for career growth and development.

Create sample table

To create a department table in Oracle, you can use the following SQL syntax:

CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50) NOT NULL,
    address VARCHAR2(50) NOT NULL,
    contact_no VARCHAR2(50)
);

To insert data into the department table in Oracle, you can use the following SQL syntax:

INSERT INTO departments (department_id, department_name, address, contact_no)
VALUES (1, 'HR', 'Dhanmondi', '01866453234');

INSERT INTO departments (department_id, department_name, address, contact_no)
VALUES (2, 'Marketing', 'Mirpur', '01956327867');

INSERT INTO departments (department_id, department_name, address, contact_no)
VALUES (3, 'IT', 'Motijeel', '01572651325');

INSERT INTO departments (department_id, department_name, address, contact_no)
VALUES (4, 'Sales', 'Eskaton', '01602654512');

To create a table named employees in Oracle with some common attributes typically found in an employee table, you can use the following SQL syntax:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(50) UNIQUE,
    hire_date DATE,
    department_id NUMBER REFERENCES departments(department_id)
);

To insert data into the employees table in Oracle, you can use the following SQL syntax along with INSERT statements:

Here is an example to insert sample data into the employees table:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id)
VALUES (1, 'Rebeka', 'Jahan', 'rebeka.jahan@example.com', TO_DATE('2024-01-01', 'YYYY-MM-DD'), 1);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id)
VALUES (2, 'Salma', 'Khanaom', 'salma.khanom@example.com', TO_DATE('2023-02-15', 'YYYY-MM-DD'), 1);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id)
VALUES (3, 'Azijul', 'Hoque', 'azijul.hoque@example.com', TO_DATE('2022-10-18', 'YYYY-MM-DD'), 2);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id)
VALUES (4, 'Mehrab', 'Hossain', 'mehrab.hossain@example.com', TO_DATE('2021-11-05', 'YYYY-MM-DD'), 2);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id)
VALUES (5, 'Minhaz', 'Kazi', 'minhaz.kazi@example.com', TO_DATE('2024-10-15', 'YYYY-MM-DD'), 4);

Run the following Select query to check the content of the departments table.

SELECT * FROM departments;

Run the following Select query to check the content of the employees table.

SELECT * FROM employees;

Inner Join

In Oracle, an inner join is a type of SQL join that combines records from two tables based on a specified condition. It returns only the rows that have matching values in both tables, excluding rows that do not have a match.

Syntax of Inner Join

The syntax for performing an inner join in Oracle is as follows:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

In this example:

• table1 and table2 are the names of the tables to be joined.
• column_name is the column(s) that will be used as the join condition.

Inner Join Example

For instance, if we have two tables employees and departments, and we want to retrieve records where the employee belongs to a specific department, we can use an inner join like this:

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

This query will return all the employees along with their department names based on the matching department_id between the two tables.

Inner joins are commonly used in SQL queries to retrieve related data from multiple tables.

Left Outer Join

n Oracle, a left outer join (or simply left join) is a type of SQL join that returns all rows from the left table (table1) and the matched rows from the right table (table2). If there is no match found in the right table, NULL values are returned.

Syntax of Left Outer Join

The syntax for performing a left outer join in Oracle is as follows:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

In this example:

• table1 is the left table, and table2 is the right table.
• column_name is the column(s) used as the join condition.

Left Outer Join Example

For example, if we have two tables employees and departments and we want to retrieve all employees along with their corresponding department names, including employees who do not have a department assigned, we can use a left outer join like this:

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

This query will return all employees, even those who do not have a department assigned, along with their department name if they are assigned to a department.

Left outer joins are useful when you want to retrieve all records from the left table, regardless of whether there are matching records in the right table.

Right Outer Join

In Oracle, a right outer join (or simply right join) is a type of SQL join that returns all rows from the right table (table2) and the matched rows from the left table (table1). If there is no match found in the left table, NULL values are returned.

However, Oracle does not directly support RIGHT OUTER JOIN syntax, unlike other databases such as MySQL or SQL Server. To achieve the same result as a right outer join in Oracle, you can use a combination of LEFT OUTER JOIN with the tables in reverse order.

Syntax of Right Outer Join

The syntax for simulating a right outer join in Oracle using a LEFT OUTER JOIN is as follows:

SELECT column1, column2, ...
FROM table2
RIGHT JOIN table1
ON table2.column_name = table1.column_name;

In this example:

• table2 is the right table, and table1 is the left table.
• column_name is the column(s) used as the join condition.

Right Outer Join Example

For example, if we want to retrieve all department names along with the names of employees assigned to each department, including departments with no employees, we can simulate a right outer join in Oracle like this:

SELECT departments.department_name, employees.first_name, employees.last_name
FROM departments
RIGHT JOIN employees
ON departments.department_id = employees.department_id;

By reversing the order of the tables in the query and using a LEFT OUTER JOIN, we can effectively achieve the same result as a right outer join in Oracle. This allows us to retrieve all rows from the right table (departments) and the matched rows from the left table (employees), including departments with no employees assigned.

Full Outer Join

In Oracle, a full outer join is a type of SQL join that combines the result sets of both a left outer join and a right outer join. It returns all rows from both the left table (table1) and the right table (table2), with NULL values where there is no match.

Oracle does not have a built-in FULL OUTER JOIN syntax, unlike some other databases. To achieve the same result as a full outer join in Oracle, you can use a combination of LEFT OUTER JOIN and UNION ALL with the tables in reverse order.

Syntax of Full Outer Join

The syntax for simulating a full outer join in Oracle using LEFT OUTER JOIN and UNION ALL is as follows:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
UNION ALL
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name
WHERE table1.column_name IS NULL;

In this example:

• table1 is the left table and table2 is the right table.
• column_name is the column(s) used as the join condition.

Full Outer Join Example

For example, if we have two tables employees and departments and we want to retrieve all employees and departments (including unmatched records), we can simulate a full outer join in Oracle like this:

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION ALL
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;

By using the combination of LEFT OUTER JOIN and UNION ALL with tables in reverse order, we can effectively simulate a full outer join in Oracle and retrieve all rows from both tables, including unmatched records.

Conclusion

Understanding how to use these joins in Oracle can help you query data from multiple tables and retrieve the desired result sets based on your requirements. Each type of join has its own use case, and selecting the appropriate join type is essential for obtaining the expected results when querying data from multiple tables in Oracle.

Exercises:

Create two tables named courses and subjects. Insert some data into both tables. Apply inner join, left outer join, right outer join and full outer join by using these tables.