What does a FULL OUTER JOIN return?

Only matched rows from both tables

All rows from the left table and matched rows from the right

All rows from both tables, with NULLs where there is no match

medium #joins #sql #data-analysis #filtering #sql-clauses #subqueries

QUESTION 01 OF 10

SQL Joins: Combining Tables Like a Pro

Test your understanding of SQL JOIN types and how they combine rows from multiple tables. This quiz covers INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with practical scenarios.

Taken by 14 developers

Answered 0/10

Pass 70%

~8 min

00:00

Note: Sign in to save your quiz progress and results.

Which JOIN type returns only the rows where there is a match in both tables?

About this Quiz

This interactive assessment is designed to test your understanding of SQL Joins: Combining Tables Like a Pro. Whether you are a beginner looking to grasp the basics or an experienced professional aiming to refine your skills, quizzes are an excellent way to validate your knowledge and identify areas for improvement.

Instructions

Read each question carefully before answering.
Some questions may have multiple correct answers.
There is no time limit, so take your time to think.
You will receive immediate feedback after submission.

Learning Outcomes

Self-assess your current skill level.
Reinforce key concepts through active recall.
Identify knowledge gaps to focus your study.
Gain confidence in your technical expertise.
Reinforce memory and recall.

Topic Overview

Test your understanding of SQL JOIN types and how they combine rows from multiple tables. This quiz covers INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with practical scenarios.

Questions in this quiz:

1. Which JOIN type returns only the rows where there is a match in both tables?

LEFT JOIN
INNER JOIN
FULL OUTER JOIN
CROSS JOIN

Hint: INNER JOIN returns only rows where the join condition is satisfied in both tables. Rows without a match in either table are excluded from the result set.

2. Given the query below, which rows will appear in the result set?

Only customers who have placed at least one order
All orders, including those with no matching customer
All customers, with NULL for order_id if they have no orders
All customers and all orders, regardless of match

Hint: A LEFT JOIN returns all rows from the left table (<code>customers</code>) and matching rows from the right table (<code>orders</code>). Where no match exists, columns from the right table are filled with <code>NULL</code>.

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

4 rows
7 rows
12 rows
It depends on the ON condition

Hint: A CROSS JOIN produces the Cartesian product of two tables — every row from the first table is combined with every row from the second. 4 × 3 = 12 rows. CROSS JOIN has no ON condition.

4. A RIGHT JOIN is simply a LEFT JOIN with the tables swapped.

True
False

Hint: True. <code>A RIGHT JOIN B</code> is logically equivalent to <code>B LEFT JOIN A</code>. Most developers prefer LEFT JOIN for consistency, and some databases (e.g., SQLite) don't support RIGHT JOIN at all.

5. Which query correctly finds all customers who have never placed an order?

Hint: A LEFT JOIN keeps all customers. Filtering with <code>WHERE o.customer_id IS NULL</code> isolates customers whose join produced no match — i.e., those with no orders. This is the classic anti-join pattern.

6. What does a FULL OUTER JOIN return?

Only matched rows from both tables
All rows from the left table and matched rows from the right
All rows from both tables, with NULLs where there is no match
The Cartesian product of both tables

Hint: FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. Every row from both tables appears; unmatched rows on either side get <code>NULL</code> values for the other table's columns.

7. How many rows does the following query return, given: <code>employees</code> has 5 rows, <code>departments</code> has 3 rows, and every employee belongs to one of the departments?

3 rows
5 rows
8 rows
15 rows

Hint: Since every employee has a matching department, the INNER JOIN returns one row per employee — 5 rows. No rows are lost because there are no unmatched employees.

8. Which clause is used to specify the condition when joining two tables?

WHERE
HAVING
ON
USING only

Hint: The ON clause specifies the join condition (e.g., <code>ON a.id = b.a_id</code>). <code>USING</code> is a shorthand when the column names are identical in both tables. <code>WHERE</code> filters rows after the join.

9. You can join a table to itself using a self-join.

True
False

Hint: True. A self-join joins a table with itself using aliases. It is commonly used for hierarchical data — for example, finding an employee's manager when both exist in the same <code>employees</code> table.

10. What is wrong with the following query?

You cannot SELECT from two tables at once
The JOIN is missing an ON (or USING) condition, producing a Cartesian product
The column names must be prefixed with table aliases
Nothing is wrong; this is valid SQL

Hint: Without an ON or USING clause, a plain <code>JOIN</code> behaves like a <code>CROSS JOIN</code>, returning the Cartesian product of both tables — usually unintended. Always specify a join condition to link related rows correctly.