sqlintermediate
SQL Join Queries
INNER/LEFT/RIGHT/FULL JOIN
6 questions
By EZ4Code Team
1. What is the result of INNER JOIN?
Returns only rows from both tables that satisfy the join condition
Returns all rows from the left table
Returns all rows from the right table
Returns all rows from both tables
Explanation: INNER JOIN returns only rows from the left and right tables that match the join condition; non-matching rows are filtered out.
2. What is the characteristic of LEFT JOIN (LEFT OUTER JOIN)?
Returns all rows from the left table, filling NULL for non-matching right table rows
Returns all rows from the right table
Returns only matching rows
Returns all rows from both tables
Explanation: LEFT JOIN preserves all rows from the left table; when the right table has no match, the corresponding fields are NULL.
3. What is the result of FULL OUTER JOIN?
Returns all rows from both tables, filling NULL where there is no match
Returns only matching rows
Returns only left table rows
Returns only right table rows
Explanation: FULL OUTER JOIN is equivalent to LEFT JOIN ∪ RIGHT JOIN, preserving all rows from both tables, with NULL where there is no match.
4. What is the result of CROSS JOIN?
The Cartesian product of the two tables
Returns only matching rows
Returns all rows from the left table
Returns the intersection
Explanation: CROSS JOIN returns the Cartesian product of the two tables (M×N rows), with no ON condition needed.
5. What is a self join?
A table joined with itself, requiring different aliases for the table
Joining two identical physical tables
Cannot use aliases
Can only use INNER JOIN
Explanation: A self join is a table joined with itself, by giving the same table different aliases, commonly used for hierarchical relationships (such as employee-manager).
6. What is the difference between ON and WHERE in the following SQL (for LEFT JOIN)?
ON is the join condition; WHERE filters after the join and may filter out left table rows where the right table is NULL
They are completely identical
WHERE is used for the join condition
ON executes after WHERE
Explanation: ON is the join condition; LEFT JOIN preserves left table rows; WHERE filters on the join result, and if it checks right table columns for non-NULL, it will also filter out non-matching left table rows.
More sql Quizzes
SQL Basics Quiz
beginnerSELECT, WHERE, ORDER BY, GROUP BY, JOINs, and fundamental SQL queries.
SQL Advanced Quiz
advancedSubqueries, window functions, CTEs, indexes, and advanced SQL techniques.
SQL Window Functions
advancedROW_NUMBER, RANK, LAG, etc.
SQL Performance Optimization
advancedIndexes, execution plans and query optimization