SQL JOIN and different types of JOINs?

Question

What is a SQL `JOIN` and what are different types?

Answer 1

Hi ,

Here are the different types of Join in Oracle database.

The JOIN operations are:

1. INNER JOIN operation
2. Specifies a join between two tables with an explicit join clause.
3. LEFT OUTER JOIN operation
4. Specifies a join between two tables with an explicit join clause, preserving unmatched rows from the first table.
5. RIGHT OUTER JOIN operation
6. Specifies a join between two tables with an explicit join clause, preserving unmatched rows from the second table.
7. CROSS JOIN operation
8. Specifies a join that produces the Cartesian product of two tables. It has no explicit join clause.
9. NATURAL JOIN operation
10. Specifies an inner or outer join between two tables. It has no explicit join clause. Instead, one is created implicitly using the common columns from the two tables.

In all cases, you can specify additional restrictions on one or both of the tables being joined in outer join clauses or in the WHERE clause.

Answer 2

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Return all records when there is a match in either left or right table