SQL Joins MCQs

Which join type is used to fetch only the records that have matching entries in both Orders and Manager tables based on region?

SQL:

SELECT O.OrderID, O.Region, O.Sales, M.Manager

FROM Orders O

INNER JOIN Manager M ON O.Region = M.Region;

• LEFT JOIN

• RIGHT JOIN

• INNER JOIN

• FULL OUTER JOIN

Answer explanation

The INNER JOIN is used to fetch records that have matching entries in both the Orders and Manager tables based on the region. It only returns rows where there is a match in both tables.

Which join ensures that all records from the Orders table appear even if no matching Manager is found?

SQL:

SELECT O.OrderID, O.Region, O.Sales, M.Manager

FROM Orders O

LEFT JOIN Manager M ON O.Region = M.Region;

• LEFT JOIN

• INNER JOIN

• FULL OUTER JOIN

• CROSS JOIN

Answer explanation

The LEFT JOIN ensures that all records from the Orders table are included in the result, even if there are no matching records in the Manager table. This is why LEFT JOIN is the correct choice.

Which query finds orders without an assigned manager?

SQL:

SELECT O.OrderID, O.Region, O.Sales

FROM Orders O

LEFT JOIN Manager M ON O.Region = M.Region

WHERE M.Manager IS NULL;

• INNER JOIN with NULL

• LEFT JOIN with NULL filter

• RIGHT JOIN

• CROSS JOIN

Answer explanation

The query uses a LEFT JOIN to include all orders, even those without a matching manager. The WHERE clause filters for rows where M.Manager is NULL, indicating no assigned manager, making 'LEFT JOIN with NULL filter' the correct choice.

Which query fetches orders that have been returned?

SQL:

SELECT O.OrderID, O.Sales, R.Returned

FROM Orders O

INNER JOIN Returns R ON O.OrderID = R.OrderID

WHERE R.Returned = 'Yes';

• FULL OUTER JOIN

• RIGHT JOIN

• INNER JOIN

• LEFT JOIN

Answer explanation

The query uses INNER JOIN to fetch orders that have been returned. This join type ensures only orders with matching records in the Returns table (where Returned = 'Yes') are selected, making it the correct choice.

Which query lists all orders that were not returned at all?

SQL:

SELECT O.OrderID, O.Sales, R.Returned

FROM Orders O

LEFT JOIN Returns R ON O.OrderID = R.OrderID

WHERE R.Returned IS NULL;

• LEFT JOIN with NULL filter

• INNER JOIN

• RIGHT JOIN

• FULL JOIN

Answer explanation

The query uses a LEFT JOIN to include all orders, even those without corresponding returns. The filter 'R.Returned IS NULL' ensures only orders that were not returned are listed, making 'LEFT JOIN with NULL filter' the correct choice.

Which join returns all records from the Returns table even if the Order does not exist?

SQL:

SELECT R.OrderID, O.Sales

FROM Returns R

RIGHT JOIN Orders O ON O.OrderID = R.OrderID;

• LEFT JOIN

• RIGHT JOIN

• CROSS JOIN

• INNER JOIN

Answer explanation

The RIGHT JOIN returns all records from the Orders table, including those without matching records in the Returns table. Thus, it ensures all Orders are displayed, even if there are no corresponding Returns.

Which query uses FULL OUTER JOIN to combine Orders and Returns?

SQL:

SELECT O.OrderID, O.Sales, R.Returned

FROM Orders O

FULL OUTER JOIN Returns R ON O.OrderID = R.OrderID;

• INNER JOIN

• RIGHT JOIN

• FULL OUTER JOIN

• SELF JOIN

Answer explanation

The query uses FULL OUTER JOIN to combine Orders and Returns, allowing for all records from both tables. This is evident in the SQL statement where 'FULL OUTER JOIN' is explicitly stated.

How do you display all manager names with or without orders in their region?

SQL:

SELECT M.Manager, O.OrderID, O.Sales

FROM Manager M

LEFT JOIN Orders O ON O.Region = M.Region;

• RIGHT JOIN

• FULL JOIN

• LEFT JOIN

• INNER JOIN

Answer explanation

The LEFT JOIN is correct as it retrieves all managers, including those without orders in their region. It ensures that manager names are displayed regardless of whether they have corresponding orders.

Which join creates all possible combinations between Manager and Orders tables?

SQL:

SELECT *

FROM Manager, Orders;

• CROSS JOIN

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

Answer explanation

The SQL query uses a comma to join the Manager and Orders tables, which is equivalent to a CROSS JOIN. This join produces all possible combinations of rows from both tables, making CROSS JOIN the correct choice.

Which join type is demonstrated when we compare Orders to itself for same region?

SQL:

SELECT A.OrderID AS Order1, B.OrderID AS Order2, A.Region

FROM Orders A

JOIN Orders B ON A.Region = B.Region AND A.OrderID < B.OrderID;

• SELF JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL JOIN

Answer explanation

The SQL query compares the Orders table to itself, which is characteristic of a SELF JOIN. It matches rows based on the same Region while ensuring that OrderID from A is less than that from B to avoid duplicates.