Oracle INNER JOIN Characteristics

➔ INNER JOIN in Oracle is used to join rows from two or more tables based on a related column between them and it returns only those records where there is a matching value in all participating tables.

➔ The INNER keyword is optional and using just JOIN is considered an inner join by default.

➔ This returns only those rows where there are matches in both tables. If a row in the first table does not have a corresponding value in the second table, it is excluded from the result set.

➔ If multiple rows from one table are matched to a single row from another table, the result set will include multiple combined rows for that single row.

➔ An inner join requires explicit join conditions (using the ON or USING clause) to determine how records will be matched and returned.

➔ Although inner joins are most often used with the equality (=) operator, inequality operators (such as >, <, or !=) can also be used in inner joins to filter matching.

Basic INNER JOIN Syntax (ON)

SELECT columns
FROM table1
[INNER] JOIN table2 
  ON table1.column_name = table2.column_name;

ON clause properties:

➔ INNER Keyword: is optional for JOIN JOIN.

➔ ON Clause: is used when joining on columns with different names.

Example: This code sample joins the employees and departments tables to retrieve the names of employees and their respective departments using ON clause.

SELECT e.first_name, e.last_name, d.department_name
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id;

Example: This SQL query retrieves a list of department names and the cities in which they are located using ON clause.

SELECT d.department_name, l.city
FROM hr.departments d
JOIN hr.locations l ON d.location_id = l.location_id;

Basic INNER JOIN Syntax (USING)

SELECT column_list
FROM table1
[INNER] JOIN table2
USING (column_name [, column_name2, ...]);

USING clause properties:

➔ The USING Clause is used if both tables share the exact same column name.

➔ In a SELECT or JOIN clause, the join column cannot be qualified with a table name or alias (department_id can be used, but employees.department_id cannot be used).

➔ The USING clause and the ON clause cannot be used together in the same join.

Example: This code sample joins the employees and departments tables to retrieve the names of employees and their respective departments by USING clause.

SELECT first_name, department_name, department_id
FROM hr.employees
JOIN hr.departments USING (department_id);

Example: This SQL query retrieves a list of department names and the cities in which they are located by USING clause.

SELECT department_name, city
FROM hr.departments
JOIN hr.locations USING (location_id);

We can use multiple join clauses to join several tables together and collect data from them.

Example: This query retrieves a list of employees, including their first names, their department names, and the city they are located in. It combines information from three different tables, such as employees, departments, and locations.

SELECT e.first_name, d.department_name, l.city
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
JOIN hr.locations l ON d.location_id = l.location_id;

We can add a WHERE clause to the JOIN clause to further filter the results.

Example: This query finds the first name, last name, and department name of only those employees whose salary is greater than 6000.

SELECT e.first_name, e.last_name, e.salary, d.department_name 
FROM hr.employees e
INNER JOIN hr.departments d ON e.department_id = d.department_id
WHERE e.salary > 6000;