- Self join
- Outer Join
- Equi-join
In Oracle, when writing subqueries, you can use various types of joins, just like in regular SQL queries. The types of joins commonly used in subqueries include:
- INNER JOIN:
- Retrieves records that have matching values in both tables.
- OUTER JOIN:
- Retrieves all records from one table and the matched records from another table, filling in the gaps with NULL values for non-matching rows.
- In Oracle, you can further specify the type of outer join, such as LEFT OUTER JOIN or RIGHT OUTER JOIN.
- SELF JOIN:
- Joins a table with itself. This is often used when you want to compare rows within the same table.
- CROSS JOIN:
- Returns the Cartesian product of two tables, i.e., all possible combinations of rows.
- EXISTENCE JOIN:
- Utilizes the EXISTS or NOT EXISTS condition to check for the existence of rows in a subquery.
- ANY/SOME and ALL Joins:
- These involve comparing a single value to a set of values in a subquery using conditions like = ANY, = SOME, or <> ALL.
It’s important to note that the choice of join depends on the specific requirements of your query and the relationships between the tables involved. The appropriate join type ensures that the result set contains the desired information.