SQL quiz. If we want to extract result of inner join of tabl

If we want to extract result of inner join of tables T1 and T2 on T1.p_key and T2.f_key fields (NOT NULL constraint is imposed on both of them), which of the following queries can be used for that? Select all that apply.


SELECT 
    T1.p_key, T2.f_key
FROM T1
    INNER JOIN T2
    ON T1.p_key = T2.f_key


SELECT 
    T1.p_key, T2.f_key
FROM T1
    FULL OUTER JOIN T2
    ON T1.p_key = T2.f_key
        WHERE T1.p_key IS NOT NULL
        AND T2.f_key IS NOT NULL


SELECT 
    T1.p_key, T2.f_key
FROM T1
    LEFT OUTER JOIN T2
    ON T1.p_key = T2.f_key
        WHERE T2.f_key IS NOT NULL


SELECT 
    T1.p_key, T2.f_key
FROM T1
    RIGHT OUTER JOIN T2
    ON T1.p_key = T2.f_key
        WHERE T1.p_key IS NOT NULL


SELECT 
    T1.p_key, T2.f_key
FROM T1, T2
    WHERE T1.p_key = T2.f_key


SELECT
    T1.p_key AS key
FROM T1
UNION ALL
SELECT
    T2.f_key AS key
FROM T2

Explanation

Query using INNER JOIN obviously satisfies the requirements as it perfoms exactly what is needed.

Query using FULL OUTER JOIN also meets requirements as it joins all records (even those that do not have match in second table), but all unmatched records are truncated by WHERE T1.p_key IS NOT NULL AND T2.f_key IS NOT NULL condition.

Queries using LEFT OUTER JOIN and RIGHT OUTER JOIN are analagous to FULL OUTER JOIN query and, therefore, satisfy the requirement.

Query using WHERE T1.p_key = T2.f_key condition is identical to an INNER JOIN query as well. So, it also meets requirements.

UNION ALL is the only one that does not satisfy requirements as instead of joining two tables it performs their union returning one column containing data from both tables.

2 (2)

Like

Login in to like
Comment

Login in to comment
Share

Tweet

SQL
Quiz

or Read more about SQL Quizzes

Follow CodeGalaxy

Mobile Beta

Send Feedback