ASSIGNMENT
ASSIGNMENT
ASSIGNMENT
Q.2 Write a note on Joins.
Answer :-Â
In Oracle databases, join queries are used to retrieve data from multiple tables by combining rows based on a related column between them. Joins are essential for querying relational databases, as they allow for a comprehensive view of data spread across different tables.
Types of Joins in Oracle
1. Inner Join
Returns only the rows that have matching values in both tables.
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
2. Outer Join:
Includes rows from one or both tables that do not have matching rows in the other table.
Types:
Left Outer Join:
Returns all rows from the left table and matching rows from the right table.
SELECT columns
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;
Right Outer Join:
Returns all rows from the right table and matching rows from the left table.
SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;
Full Outer Join:
Returns all rows when there is a match in either table.
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
3. Cross Join :Â
Produces a Cartesian product of the two tables, combining each row from the first table with every row from the second table.
Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;
Example:
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
4. Self Join
A table is joined with itself to compare rows within the same table.
Syntax:
SELECT A.column, B.column
FROM table A, table B
WHERE A.common_column = B.common_column;
Example:
SELECT A.employee_id, B.manager_id
FROM employees A
INNER JOIN employees B
ON A.manager_id = B.employee_id;
5. Natural Join :
Automatically joins tables based on columns with the same name and datatype.
Syntax:
SELECT columns
FROM table1
NATURAL JOIN table2;
Considerations for Join Queries
Performance: Proper indexing can significantly improve the performance of join queries.
Aliasing: Using table aliases improves readability and prevents ambiguity in queries.
Filtering: Adding WHERE clauses can reduce the result set and improve efficiency.