Correct:
FULL OUTER JOIN
Explanation: Answer option A is correct.
FULL OUTER JOIN returns all rows from the left and right tables in a join expression, even if the data values in joined columns do not match.
Consider the following example, EMPLOYEE table and ORDER table:
| P_ID |
LastName |
FirstName |
Address |
| 1 |
Harry |
David |
Storgata |
| 2 |
Steve |
Joseph |
California |
| 3 |
Peter |
Kari |
Toronto |
| O_ID |
Order |
P_ID |
| 1 |
1023 |
1 |
| 2 |
2034 |
2 |
| 3 |
3046 |
3 |
To join all the employees and their orders, and all the orders with their employees, run the following command:
SELECT EMPLOYEE.LastName, ORDER.Order
FROM EMPLOYEE
FULL JOIN ORDER
ON EMPLOYEE.P_Id=ORDER.O_Id
ORDER BY EMPLOYEE.LastName
The output of the command will appear as:
| LastName |
Order |
| Harry |
1023 |
| Steve |
2034 |
| Peter |
3046 |
Answer option B is incorrect. LEFT OUTER JOIN returns all the rows from the left table, and only those rows that meet the join condition from the right table.
Answer option C is incorrect. CROSS JOIN produces a result set of all possible combinations of rows drawn from each table involved in the join. In other words, Oracle combines each row of one table with each row of the other table. For example, a CROSS JOIN between two tables with five rows each produces a result set of twenty-five rows.
Answer option D is incorrect. RIGHT OUTER JOIN returns all the rows from the right table, and only those rows that meet the join condition from the left table.