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
andtable2
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, andtable2
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, andtable1
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 andtable2
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.