SQLite Join Types: INNER, LEFT, and CROSS Quiz

Explore essential SQL join types in SQLite with this quiz focusing on INNER JOIN, LEFT JOIN, and CROSS JOIN concepts. Deepen your understanding of join operations, their syntax, and practical differences in relational database queries.

1. Identifying the INNER JOIN Result

   Given two tables, Employees and Departments, an INNER JOIN on DepartmentID returns which of the following?

   1. Rows with DepartmentID from Departments only
   2. Rows with matching DepartmentID in both tables
   3. All rows from Employees even if there's no match
   4. All possible combinations of Employees and Departments

   Explanation: An INNER JOIN only returns rows where the join condition is met in both tables, so only employees with a department are included. Selecting all rows from Employees regardless of match describes a LEFT JOIN. Returning all possible combinations matches a CROSS JOIN. Only showing records from Departments ignores Employees and is incorrect.

2. LEFT JOIN Row Inclusion

   What is the key feature of a LEFT JOIN between tables A and B?

   1. Returns all rows from table B, including unmatched ones
   2. Returns all rows from table A with matching rows from B or null where there is no match
   3. Returns no rows if there is no match
   4. Returns only rows with matching keys in both A and B

   Explanation: A LEFT JOIN returns every row from the left table (A), adding data from the right table (B) where available, and null for unmatched cases. Only showing matches describes an INNER JOIN. Including all rows from table B would be a RIGHT JOIN, which SQLite does not natively support. Returning no rows on no match omits LEFT JOIN’s primary purpose.

3. Purpose of CROSS JOIN

   Which SQLite JOIN is used to produce the Cartesian product of two tables, such as Products and Categories?

   1. INNER JOIN
   2. CROSS JOIN
   3. LEFT JOIN
   4. RIGHT JOIN

   Explanation: A CROSS JOIN generates all possible combinations of rows between two tables, creating a Cartesian product. LEFT JOIN and INNER JOIN return rows based on matching conditions, not all combinations. RIGHT JOIN is not a standard SQLite join.

4. Syntax of INNER JOIN

   Which is the correct SQL keyword to perform an inner join in SQLite syntax?

   1. OUTER JOIN
   2. INSIDE JOIN
   3. INNER JOIN
   4. BOTH JOIN

   Explanation: The syntax uses 'INNER JOIN' to combine rows based on a matching condition. 'INSIDE JOIN', 'OUTER JOIN', and 'BOTH JOIN' are not valid SQL terms for this purpose. The explicit 'INNER JOIN' clearly signals an inner join operation in queries.

5. Using LEFT JOIN with Missing Matches

   If a LEFT JOIN is used on Customers and Orders but some customers have never placed an order, what will the Orders fields show for those customers?

   1. Empty strings
   2. NULL values
   3. Zero values
   4. Random existing order data

   Explanation: For customers without matching orders, the result of the LEFT JOIN shows NULL in the Orders columns. Zero values or empty strings are not assigned by default; these would need to be set explicitly in queries. Random data does not appear in standard join operations.

6. Join Order and Result Size

   What is the result size of a CROSS JOIN between a table with 4 rows and another with 3 rows?

   1. 7 rows
   2. 3 rows
   3. 12 rows
   4. 4 rows

   Explanation: CROSS JOIN combines every row from both tables, so 4 rows times 3 rows results in 12 rows. Seven rows, four rows, or three rows would only occur under different join types or with limited data.

7. Join Condition Requirement

   Which SQL JOIN requires an explicit condition to match rows from both tables?

   1. CROSS JOIN
   2. FULL JOIN
   3. MULTI JOIN
   4. INNER JOIN

   Explanation: An INNER JOIN requires a condition, such as ON columns, to match rows. CROSS JOIN does not use a condition and instead produces all combinations. FULL JOIN and MULTI JOIN are not native to SQLite, and neither is correct here.

8. Selecting Unmatched Rows

   Which JOIN type should you use to identify records in the left table that have no corresponding match in the right table?

   1. INNER JOIN
   2. SELF JOIN
   3. LEFT JOIN
   4. CROSS JOIN

   Explanation: A LEFT JOIN, when combined with a condition checking for NULL on the right table, lets you find rows in the left table without matches. INNER JOIN only returns matches, SELF JOIN is not used for this purpose, and CROSS JOIN does not match based on conditions.

9. Keyword for Joining Without Matching Condition

   What SQLite JOIN keyword can you use when you want every row from table M to be paired with every row from table N, regardless of any matching values?

   1. INNER JOIN
   2. LEFT JOIN
   3. CROSS JOIN
   4. OUTER JOIN

   Explanation: CROSS JOIN pairs each row from one table with every row in another, ignoring column values. INNER JOIN and LEFT JOIN rely on conditions, while OUTER JOIN is not a recognized keyword in standard SQLite.

10. Default Join Behavior

    If you write 'SELECT * FROM Sellers, Products' in SQLite, which JOIN is being performed by default?

    1. CROSS JOIN
    2. LEFT JOIN
    3. INNER JOIN
    4. RIGHT JOIN

    Explanation: Listing multiple tables without a join condition or using commas creates a CROSS JOIN, resulting in the Cartesian product. LEFT JOIN and RIGHT JOIN require explicit keywords. INNER JOIN needs a condition and is not the default here.