Which of the following statement is true in reference to the left outer join

Why are LEFT/RIGHT and LEFT OUTER/RIGHT OUTER the same? Let's explain why this vocabulary. Understand that LEFT and RIGHT joins are specific cases of the OUTER join, and therefore couldn't be anything else than OUTER LEFT/OUTER RIGHT. The OUTER join is also called FULL OUTER as opposed to LEFT and RIGHT joins that are PARTIAL results of the OUTER join. Indeed:

Table A | Table B     Table A | Table B      Table A | Table B      Table A | Table B
   1    |   5            1    |   1             1    |   1             1    |   1
   2    |   1            2    |   2             2    |   2             2    |   2
   3    |   6            3    |  null           3    |  null           -    |   -
   4    |   2            4    |  null           4    |  null           -    |   -
                        null  |   5             -    |   -            null  |   5
                        null  |   6             -    |   -            null  |   6

                      OUTER JOIN (FULL)     LEFT OUTER (partial)   RIGHT OUTER (partial)

It is now clear why those operations have aliases, as well as it is clear only 3 cases exist: INNER, OUTER, CROSS. With two sub-cases for the OUTER. The vocabulary, the way teachers explain this, as well as some answers above, often make it looks like there are lots of different types of join. But it's actually very simple.

Joins MCQs : This section focuses on the "Joins" in the SQL. These Multiple Choice Questions (mcq) should be practiced to improve the SQL skills required for various interviews (campus interview, walk-in interview, company interview), placement, entrance exam and other competitive examinations.

1. A_____ is a query that retrieves rows from more than one table or view

A. Start
B. End
C. Join
D. All of the above

View Answer

Ans : C

Explanation: A Join is a query that retrieves rows from more than one table or view.

2.  How many join types in join condition:

A. 2
B. 3
C. 4
D. 5

View Answer

Ans : D

Explanation: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN.

3. Which are the join types in join condition:

A. Cross join
B. Natural join
C. Join with USING clause
D. All of the mentioned

View Answer

Ans : D

Explanation: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN are the types of joins.

4. Which join refers to join records from the write table that have no matching key in the left table are include in the result set:

A. Left outer join
B. Right outer join
C. Full outer join
D. None of the above

View Answer

Ans : B

Explanation: Right outer join refers to join records from the write table that have no matching key in the left table are include in the result set.

5. Which view that contains more than one table in the top-level FROM clause of the SELECT statement:

A. Join view
B. Datable join view
C. Updatable join view
D. All of the mentioned

View Answer

Ans : C

Explanation: Updatable join view that contains more than one table in the top-level FROM clause of the SELECT statement.

6. Relation R1 has 10 tuples and 5 attributes. Relation R2 has 0 tuples and 7 attributes. When a CROSS JOIN is achieved between R1 and R2, how many tuples would the resultant set have?

A. 28
B. 10
C. 0
D. 35

View Answer

Ans : C

Explanation: When a CROSS JOIN is achieved between R1 and R2, 0 tuples the resultant set will have.

7. Which join is to be used between two tables A and B when the resultant table needs rows from A and B that matches the condition and rows from A that does not match the condition?

A. Outer Join
B. Cross Join
C. Inner Join
D. None of the above

View Answer

Ans : A

Explanation: Outer Join is to be used between two tables A and B when the resultant table needs rows from A and B that matches the condition and rows from A that does not match the condition.

8. Which of the following conditions has to be satisfied for INNER JOIN to work?

A. Columns used for joining must have same name
B. Columns used for joining can have same or different name
C. Columns used for joining must have different names
D. Columns used for joining must have different names

View Answer

Ans : B

Explanation: Columns used for joining can have same or different name is the following conditions has to be satisfied for INNER JOIN to work

9. Which of the following statement is TRUE about FULL OUTER JOIN created on two tables Table1 and Table2?

A. Retrieves all the unmatched rows of Table1
B. Retrieves all the unmatched rows of Table2
C. Retrieves both matched and unmatched rows of Table1 and Table2
D. Retrieves only matched rows of table1 and Table2

View Answer

Ans : C

Explanation: The statement is TRUE about FULL OUTER JOIN created on two tables Table1 and Table2 is Retrieves both matched and unmatched rows of Table1 and Table2.

10. Which join is equivalent to Cartesian Product?

A. INNER JOIN
B. OUTER JOIN
C. CROSS JOIN
D. NATURAL JOIN

View Answer

Ans : C

Explanation: CROSS JOIN is equivalent to Cartesian Product.

11. Which of the following statements are true?

A. INNER JOIN only retrieves those rows from Cartesian Product that satisfy the JOIN condition
B. FULL OUTER JOIN is same as CROSS JOIN
C. SELF JOIN is a special type of OUTER JOIN
D. Both A and C

View Answer

Ans : A

Explanation: INNER JOIN only retrieves those rows from Cartesian Product that satisfy the JOIN condition is true.

12. Which of the following statements are False?

A. RIGHT OUTER JOIN is equivalent to LEFT OUTER JOIN if order of tables are reversed
B. FULL OUTER JOIN is same as CROSS JOIN
C. SELF JOIN is a special type of OUTER JOIN
D. Both B and C

View Answer

Ans : D

Explanation: Both B and C option are False statements.

13. To specify a normal join, using the keyword inner is?

A. Mandatory
B. Optional
C. Independent
D. Free

View Answer

Ans : B

Explanation: To specify a normal join, using the keyword inner is Optional.

14. Left outer join preserves tuples only in the relation named before

A. Right outer join operation
B. Right inner join operation
C. Left inner join operation
D. Left outer join operation

View Answer

Ans : D

Explanation: Left outer join preserves tuples only in the relation named before Left outer join operation.

15. The following SQL is which type of join?

A. Equi-join
B. Natural join
C. Outer join
D. Cartesian join

View Answer

Ans : A

Explanation: Equi-join joins only same data entry field. For example, one table contains department id and another table should contain department id.

16. The following SQL is which type of join?

A. Equi-join
B. Natural join
C. Outer join
D. Cartesian join

View Answer

Ans : D

Explanation: Cartesian Join is simply the joining of one or more table which returns the product of all the rows in these tables.

17. Evaluate this SQL statement: In the statement, which capabilities of a SELECT statement are performed?

A. Selection, projection, join
B. Difference, projection, join
C. Selection, intersection, join
D. Intersection, projection, join

View Answer

Ans : A

Explanation: In the statement, Selection, projection, join capabilities of a SELECT statement are performed

Which of the following is correct about left outer join?

What is true about LEFT join?

Which is true about LEFT join in SQL?

Which of the following is the syntax for a left outer join?