Given the data:
Expected output:
Which query produces the expected output?
A.
SELECT colors2.name, colors1.name
FROM colors2
OPTIONAL JOIN colors1
ON colors2.name, colors1.name
B.
SELECT colors2.name, colors1.name
FROM colors2
NATURAL JOIN colors1
ON colors2.name=colors1.name
C.
SELECT colors2.name, colors1.name
FROM colors2
STRAIGHT JOIN colors1
ON colors2.name, =colors1.name
D.
SELECT colors2.name,colors1.name
FROM colors2
LEFT JOIN colors1
ON colors2.name=colors1.name
E.
SELECT colors2.name,colors1.name
FROM colors2
RIGHT JOIN colors1
ON colors2.name=colors1.name
correct is E
E
From MySQL 5.0 Certification Study Guide by Dubois, Hinz, and Pedersen:
“The right table is the reference table, so a RIGHT JOIN produces a result for each row in the right table, whether or not it has any match in the left table.”
hence the NULLs in the left table.
E
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
http://www.w3schools.com/sql/sql_join_right.asp
E
E