Examine the data in the CUSTOMERS table:
CUSTNO CUSTNAME CITY
1 KING SEATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 __________________ Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose
two.)
A.
JOIN
B.
NATURAL JOIN
C.
LEFT OUTER JOIN
D.
FULL OUTER JOIN
E.
RIGHT OUTER JOIN
why the right join is fetching no rows.. it should fetch rest of the two rows,, not understanding the reason.. plese help me and hurry up,, i have my exam day after tommorow
My tests:
select c1.custname, c1.city
from customers c1 RIGHT OUTER JOIN customers c2
on (C1.CITY=C2.CITY AND c1.custnamec2.custname);
Output is:
CUSTNAME CITY
——————– ——————–
King Seattle
Kochar Seattle
select c1.custname, c1.city
from customers c1 JOIN customers c2
on (C1.CITY=C2.CITY AND c1.custnamec2.custname);
CUSTNAME CITY
——————– ——————–
Kochar Seattle
King Seattle
This is not correct. With RIGHT OUTER JOIN you will get 4 records back. 2 last records will be NULLs.
RIGHT OUTER JOIN also provide NULL values.
Why does answer C. LEFT OUTER JOIN, won’t work?
Since we are joining the two tables with the same data, and RIGHT OUTER JOIN will get the results, LEFT OUTER JOIN should get the same results.
Left outer join does not work? please explain, thanks
Here in the select clause, it is selecting custname and city columns from c1 which is left table.
So even though right outer join is applied, all the rows from right table(c2) are not displayed as they are not in select column list.
if right outer join is correct answer then should left outer join also be correct
You select data from the left table (SELECT c1.custname, c1.city) thats why E is correct. If you have SELECT c2.custname, c2.city then C is correct.
In my opinion The CORRECT answear is only A(Join), but the most acceptable answear is A(Join) and E(Right Join), because RIGHT OUTER JOIN will return 4 rows(2 of them with null values), hiding the custname(SMITH and GREEN) of the table C2 as it is not on the SELECT list.
CUSTNAME CITY
——————– ——————–
KING SEATTLE
KOCHAR SEATTLE
(null) (null)
(null) (null)
If LEFT JOIN was used it would be completely wrong, because it would return the following rows
CUSTNAME CITY
——————– ——————–
KING SEATTLE
KOCHAR SEATTLE
SMITH NEW YORK
GREEN BOSTON