Explanation:
Answer option C is correct.
The outer join returns all rows from one table and only the matching rows from the joined table. The plus symbol within parentheses ((+)) denotes an outer join in the query.
Answer option B is incorrect. In a non-equijoin, a query relates two tables using an operator other than the equality operator (=).
Answer option A is incorrect. EQUI JOIN relates two tables using an equality operator (=). It is also known as inner join or equality join. EQUI JOIN produces only matched rows from both the tables.
For example:
SELECT Dept_Name, Emp_Name
FROM Departments d1, Employees e1
WHERE d1.Dept_No = e1.Dept_No
ORDER BY Dept_Name, Emp_Name;
Here, Dept_No columns in both tables are related using EQUI JOIN.
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 |
0 |
| 2 |
2034 |
2 |
| 3 |
3046 |
3 |
To get all the columns of EMPLOYEE table and ORDER table, run the following command:
SELECT EMPLOYEE.LastName, ORDER.Order,
FROM EMPLOYEE, ORDER
WHERE EMPLOYEE.P_ID=ORDER.O_ID;
The output of the command will appear as:
| P_ID |
LastName |
Order |
| 2 |
Steve |
2034 |
| 3 |
Peter |
3046 |
Answer option D is incorrect. SELF JOIN joins a table to itself. Each row of the table is joined with itself and with every other row of the table.
For example:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
Consider the following example, EMPLOYEE table and ORDER table:
| P_ID |
LastName |
FirstName |
Address |
| 1 |
Harry |
David |
Storgata |
| 2 |
Steve |
Joseph |
Storgata |
| 3 |
Peter |
Kari |
Toronto |
To join this table using SELF JOIN, run the following command:
SELECT a.LastName, a.Address, b.Order,
FROM EMPLOYEE a EMPLOYEE b
WHERE a.Address= b.Address;
The output of the command will appear as:
| LastName |
LastName |
Address |
| Harry |
Steve |
Storgata |
| Steve |
Harry |
Storgata |