Joins Quiz - Languages SQL

Free practice quiz: Joins (Languages SQL). 50 multiple-choice questions with answers and explanations. Play online, no sign-up needed.

This Languages SQL practice quiz has 50 multiple-choice questions on Joins, and 50 of them come with an explanation. Try the 5 sample questions below (tap an option to check yourself), then play the full quiz online. No sign-up needed.

Sample questions

1. Which type of JOIN returns all rows from the left table and the matched rows from the right table? If there is no match, the result is NULL from the right side.

   1. INNER JOIN
   2. LEFT JOIN
   3. RIGHT JOIN
   4. FULL OUTER JOIN
   Show answer

   Answer: B. LEFT JOIN

   A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (the table specified before the JOIN keyword) and the matched rows from the right table. If a row in the left table does not have a corresponding match in the right table, the columns from the right table will contain NULL values.

2. When using a RIGHT JOIN, the join condition is evaluated against which table first?

   1. The left table
   2. The right table
   3. Both tables simultaneously
   4. The table with fewer rows
   Show answer

   Answer: B. The right table

   In a RIGHT JOIN, all rows from the right table are included. The join condition is used to find matching rows in the left table. If there's no match in the left table, NULLs are returned for the left table's columns.

3. Which JOIN would you use to find all customers and the count of orders they have placed, including customers who have placed zero orders?

   1. INNER JOIN
   2. LEFT JOIN
   3. RIGHT JOIN
   4. FULL OUTER JOIN
   Show answer

   Answer: B. LEFT JOIN

   You would use a `LEFT JOIN` from `Customers` to `Orders`. Then, you would `GROUP BY` customer and use `COUNT(Orders.OrderID)` (or a similar aggregate function that counts non-NULL values) to get the number of orders. Customers with no orders will have a count of 0.

4. What is the purpose of the `USING` clause in SQL JOINs?

   1. To specify multiple join conditions.
   2. To join tables without a common column name.
   3. To join tables on columns that have the same name in both tables, simplifying the ON clause.
   4. To define the order of columns in the result set.
   Show answer

   Answer: C. To join tables on columns that have the same name in both tables, simplifying the ON clause.

   The `USING` clause is a shorthand for the `ON` clause when the join columns in both tables have the exact same name. For example, `JOIN table2 USING (common_column)` is equivalent to `JOIN table2 ON table1.common_column = table2.common_column`.

5. Consider the following two tables: `Students` (StudentID, StudentName) `Enrollments` (EnrollmentID, StudentID, Course) Which query would display all students and the courses they are enrolled in, including students who are not enrolled in any course?

   1. `SELECT * FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;`
   2. `SELECT * FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;`
   3. `SELECT * FROM Enrollments LEFT JOIN Students ON Enrollments.StudentID = Students.StudentID;`
   4. `SELECT * FROM Students RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;`
   Show answer

   Answer: B. `SELECT * FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;`

   A `LEFT JOIN` with `Students` as the left table ensures that all students are included in the result. If a student is not enrolled in any course, the `Enrollments` columns (like `Course`) will be NULL for that student.

More questions in this quiz

Answers and explanations are in the full quiz.

1. What is the primary difference between an INNER JOIN and a FULL OUTER JOIN?
2. Consider two tables: `Employees` (EmployeeID, Name, DepartmentID) and `Departments` (DepartmentID, DepartmentName). Which JOIN would you use to get all employees and their department names, including employees who are not assigned to any department?
3. Which type of JOIN is equivalent to specifying WHERE table1.column = table2.column in the WHERE clause?
4. If you want to retrieve all records from the `Customers` table and their corresponding orders from the `Orders` table, but also include customers who have not placed any orders, which JOIN should you use?
5. Which JOIN clause is used to retrieve records that do not have a match in either table?
6. When joining a table to itself, what is this type of join commonly referred to as?
7. Consider tables `A` (ID, ValueA) and `B` (ID, ValueB). The following query returns:
8. What is the result of a CROSS JOIN between two tables with N rows in the first table and M rows in the second table?
9. Which of the following keywords is often used in conjunction with JOINs to filter results based on conditions in both tables being joined?
10. Which JOIN type is guaranteed to return at least one row for every row in BOTH tables, filling in NULLs where there's no match?
11. What is the most appropriate JOIN to use when you need to find employees who have not been assigned to any department?
12. Which JOIN is used to retrieve all rows from the right table and matched rows from the left table? If there is no match, the result is NULL from the left side.
13. Consider the following query: `SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;` Which of the following is a correct interpretation of this query's output?
14. Which JOIN clause is suitable for finding records that exist in one table but not in another, based on a common key?
15. What is the primary purpose of a Self-Join?
16. Consider two tables: `Products` (ProductID, ProductName, CategoryID) and `Categories` (CategoryID, CategoryName). If you want to list all products and their category names, but also include products that have not been assigned a category (CategoryID is NULL), which JOIN is most appropriate?
17. Which of the following JOIN types is often used when you need to display data from two tables but want to ensure that no data is lost from either table, regardless of whether a match exists?
18. What happens if you perform an INNER JOIN between table `A` and table `B` where there are no rows that satisfy the join condition?
19. Which of the following scenarios would best utilize a `FULL OUTER JOIN`?
20. Consider the following tables: `TableA` (ID, Name) `TableB` (ID, Value) What does `SELECT * FROM TableA, TableB` produce?
21. Which of these JOIN types is guaranteed to return only rows where the join condition is met in both tables?
22. What is the primary concern when performing a CROSS JOIN on very large tables?
23. Consider the following query: `SELECT * FROM Orders o LEFT JOIN Customers c ON o.CustomerID = c.CustomerID WHERE c.CustomerID IS NULL;` What does this query retrieve?
24. Which JOIN type is used to find records that exist in the left table but not in the right table, based on a common key?
25. When using `UNION` versus `UNION ALL` in SQL, what is the key difference regarding duplicate rows?
26. What is the effect of `dbms_output.put_line('Hello');` within a PL/SQL block?
27. Which JOIN clause is ideal for finding records present in *either* table A or table B, but not in both?
28. Consider an `Employees` table and a `Salaries` table, both with an `EmployeeID` column. Which query would list all employees and their salaries, and also list employees who have no salary record (assuming a LEFT JOIN)?
29. Which of the following is NOT a valid type of SQL JOIN?
30. When you have tables `A` and `B`, and you want to find rows that exist in `A` but do NOT exist in `B` based on a common `ID` column, how would you typically construct the query?
31. What is the main difference between `ON` and `USING` for specifying join conditions?
32. If you need to retrieve all products from the `Products` table and their corresponding sales from the `Sales` table, but you also want to include products that have never been sold, which JOIN should be used?
33. What is the result of `SELECT * FROM TableA CROSS JOIN TableB` when `TableA` has 5 rows and `TableB` has 10 rows?
34. Which SQL JOIN type is used to find rows that exist in the right table but not in the left table, based on a common key?
35. When writing a query to find employees who do not have a manager assigned (assuming a `manager_id` column in the `Employees` table which is NULL if no manager), what type of join is most directly relevant conceptually, even if the final filter might use WHERE?
36. What is the function of the `NATURAL JOIN` clause in SQL?
37. Which JOIN is most suitable for finding records that have a match in the left table, but not in the right table?
38. What is the typical use case for a `CROSS JOIN`?
39. When performing a `FULL OUTER JOIN`, if a row in the left table has no match in the right table, what will the columns from the right table contain?
40. Which SQL clause is specifically designed to combine the results of two or more SELECT statements and automatically removes duplicate rows?
41. What is the correct syntax for an INNER JOIN in SQL?
42. Which type of join is generally preferred for performance when you only need matching records?
43. Consider a scenario where you have `TableA` with columns `(ID, ValueA)` and `TableB` with columns `(ID, ValueB)`. If `TableA` has IDs {1, 2, 3} and `TableB` has IDs {2, 3, 4}, what is the result of `SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.ID = TableB.ID`?
44. Which of the following is the most correct statement about SQL JOINs?
45. If you want to retrieve all employees and their respective departments, but also include departments that have no employees assigned to them, which JOIN type would you use?

More quizzes

• Stored Procedures▶ Play
• DDL▶ Play
• DML▶ Play
• Indexes▶ Play

Browse all SQL quizzes

More to play