Test Your MySQL UNION and UNION ALL Knowledge

Progress

True / False

In MySQL, the UNION operator combines the results of two or more SELECT statements and removes duplicate rows.

True False

The UNION operator in MySQL combines results from multiple SELECT statements and automatically removes duplicate rows.

True / False

UNION ALL in MySQL includes duplicates in the result set.

True False

The UNION ALL operator includes all rows from each SELECT statement, including duplicates.

True / False

The number of columns in each SELECT statement used with UNION must be different.

True False

The number of columns in each SELECT statement must be the same when using UNION or UNION ALL, though the column names can be different.

True / False

The ORDER BY clause can be used with UNION or UNION ALL to sort the final result set.

True False

You can use the ORDER BY clause with UNION or UNION ALL, but it must be placed at the end of the entire query.

True / False

MySQL requires that all SELECT statements in a UNION query have the same column names.

True False

MySQL does not require the same column names, but the number and data types of columns must match.

True / False

Using UNION ALL is generally faster than using UNION because UNION ALL does not perform a duplicate check.

True False

UNION ALL can be faster than UNION because it does not need to check for and remove duplicate rows.

True / False

You can use UNION or UNION ALL with subqueries in MySQL.

True False

MySQL allows UNION or UNION ALL to combine results from subqueries, provided each subquery has the same number of columns with compatible data types.

True / False

The UNION operator can only be used with SELECT statements that have columns of exactly the same data types in MySQL.

True False

For UNION to work, the corresponding columns in each SELECT statement must have compatible data types.

True / False

You can use LIMIT in conjunction with UNION ALL to limit the number of rows returned in MySQL.

True False

You can use LIMIT with UNION ALL to limit the rows returned from the entire combined result set.

Q10

True / False

When using UNION in MySQL, the columns selected must be in the same order for each SELECT statement involved.

True False

In MySQL, when using UNION, the columns must be in the same order in each SELECT statement for the combined results to be meaningful.

Q11

Single Choice

Which MySQL keyword is used to combine the result set of two or more SELECT statements and automatically remove duplicates?

JOIN UNION UNION ALLINTERESECT

The UNION keyword combines the results of two or more SELECT statements and removes duplicate rows by default

Q12

Single Choice

Given the following SQL query, which of the following is correct about the result set?

SQL Code

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

The result will include duplicate cities. The result will exclude duplicate cities. The result will include only cities from the customers tableThe result will include only cities from the suppliers table

The UNION operator automatically removes duplicate rows from the combined result set

Q13

Single Choice

What is the key difference between UNION and UNION ALL in MySQL?

UNION ALL removes duplicates, UNION does not. UNION allows combining result sets, UNION ALL does not. UNION ALL retains duplicates, UNION removes them.UNION requires same number of columns, UNION ALL does not

The UNION operator removes duplicates, while UNION ALL keeps all duplicate rows in the result set.

Q14

Single Choice

Given two tables employees and managers, how would you write a query to list all unique employee and manager names using MySQL?

SELECT name FROM employees UNION ALL SELECT name FROM managers; SELECT name FROM employees UNION SELECT name FROM managers; SELECT DISTINCT name FROM employees UNION ALL SELECT DISTINCT name FROM managers;SELECT name FROM employees JOIN name FROM managers;

Using UNION ensures that the combined result set has no duplicate names.

Q15

Single Choice

If you want to combine the results of two queries and ensure that all duplicates are included in the final result, which MySQL keyword should you use?

UNION UNION ALL INTERSECTJOIN

UNION ALL combines results without removing duplicates, unlike UNION, which removes duplicates.

Q16

Single Choice

Which statement is true about their results?

SQL Code

Consider the following two SQL queries
SELECT product_id FROM products_a
UNION
SELECT product_id FROM products_b;
SELECT product_id FROM products_a
UNION ALL
SELECT product_id FROM products_b;

Both queries will return the same number of rows. The first query might return fewer rows than the second. The first query will always return more rows than the secondThe second query might return fewer rows than the first. Answer: B) The first query might return fewer rows than the second

The first query uses UNION, which removes duplicates, while the second query uses UNION ALL, which includes duplicates, potentially leading to more rows.

Q17

Single Choice

Which of the following queries will return all rows from both the sales and purchases tables, including duplicates, while sorting the result set by transaction_date in descending order?

SELECT transaction_date, amount FROM sales UNION SELECT transaction_date, amount FROM purchases ORDER BY transaction_date DESC; SELECT transaction_date, amount FROM sales UNION ALL SELECT transaction_date, amount FROM purchases ORDER BY transaction_date DESC; SELECT transaction_date, amount FROM sales UNION SELECT transaction_date, amount FROM purchases ORDER BY 2 DESC;SELECT transaction_date, amount FROM sales UNION ALL SELECT transaction_date, amount FROM purchases ORDER BY 2 DESC;

UNION ALL is used to keep duplicates, and ORDER BY is used to sort the results by transaction_date in descending order.

Q18

Single Choice

You have two queries that select the same columns from different tables but with different conditions. You want to combine the results and remove duplicates, then sort by one of the columns. Which MySQL keyword would you use and where would you place the ORDER BY clause?

Use UNION; place ORDER BY after the last SELECT. Use UNION ALL; place ORDER BY after the last SELECT. Use UNION; place ORDER BY after the UNION.Use UNION ALL; place ORDER BY after the UNION ALL.

When using UNION, you place the ORDER BY clause after the entire UNION operation to sort the final combined result

Q19

Single Choice

Consider two tables orders_2023 and orders_2024, each having columns order_id and order_date. Write a query using UNION ALL that lists all orders from both tables without removing duplicates and sorts the results by order_date in ascending order. Which query would you use?

SELECT order_id, order_date FROM orders_2023 UNION SELECT order_id, order_date FROM orders_2024 ORDER BY order_date ASC; SELECT order_id, order_date FROM orders_2023 UNION ALL SELECT order_id, order_date FROM orders_2024 ORDER BY order_date ASC; SELECT order_id, order_date FROM orders_2023 UNION ALL SELECT order_id, order_date FROM orders_2024 ORDER BY 2 ASC;SELECT order_id, order_date FROM orders_2023 UNION SELECT order_id, order_date FROM orders_2024 ORDER BY 2 ASC;

UNION ALL ensures all records are included, and ORDER BY sorts the combined result set by order_date.

Q20

Single Choice

Given two complex queries that retrieve data from several joined tables, you want to combine their results, remove duplicates, and then sort by a calculated column (e.g., total_sales). How would you structure your query in MySQL?

(SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2023 GROUP BY customer_id) UNION (SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2024 GROUP BY customer_id) ORDER BY total_sales DESC; SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2023 UNION SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2024 GROUP BY customer_id ORDER BY total_sales DESC; (SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2023 GROUP BY customer_id) UNION ALL (SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2024 GROUP BY customer_id) ORDER BY total_sales DESC;(SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2023 GROUP BY customer_id) UNION (SELECT customer_id, SUM(sales_amount) AS total_sales FROM sales_2024 GROUP BY customer_id) ORDER BY total_sales;

Subqueries: Each subquery calculates total_sales for each customer_id from different tables (e.g., sales_2023 and sales_2024). UNION: Combines the results of the subqueries and removes any duplicate rows. ORDER BY: Sorts the final combined result by total_sales in descending order (DESC).

Q21

Multiple Choice

Which SQL query uses UNION to combine the results from the 'employees' and 'managers' tables, returning unique records?

SQL Code

SELECT employee_id, name FROM employees
UNION
SELECT manager_id, name FROM managers;

SELECT employee_id, name FROM employees UNION SELECT manager_id, name FROM managers; SELECT employee_id, name FROM employees UNION ALL SELECT manager_id, name FROM managers; SELECT employee_id, name FROM employees JOIN managers ON employees.manager_id = managers.manager_id;SELECT employee_id, name FROM employees LEFT JOIN managers ON employees.manager_id = managers.manager_id;

The UNION operator is used to combine the results from two SELECT statements, returning unique records by default.

Q22

Multiple Choice

Identify the correct SQL query that uses UNION ALL to combine the results from the 'orders' and 'shipments' tables, including all duplicate records.

SQL Code

SELECT order_id, order_date FROM orders
UNION ALL
SELECT shipment_id, shipment_date FROM shipments;

SELECT order_id, order_date FROM orders UNION SELECT shipment_id, shipment_date FROM shipments; SELECT order_id, order_date FROM orders UNION ALL SELECT shipment_id, shipment_date FROM shipments; SELECT order_id, order_date FROM orders JOIN shipments ON orders.order_id = shipments.shipment_id;SELECT order_id, order_date FROM orders LEFT JOIN shipments ON orders.order_id = shipments.shipment_id;

The UNION ALL operator combines the results from two SELECT statements, including all duplicate records.

Q23

Multiple Choice

Which SQL query uses UNION to retrieve all customer names from the 'domestic_customers' and 'international_customers' tables without duplicates?

SQL Code

SELECT customer_name FROM domestic_customers
UNION
SELECT customer_name FROM international_customers;

SELECT customer_name FROM domestic_customers UNION SELECT customer_name FROM international_customers; SELECT customer_name FROM domestic_customers UNION ALL SELECT customer_name FROM international_customers; SELECT customer_name FROM domestic_customers JOIN international_customers ON domestic_customers.customer_id = international_customers.customer_id;SELECT customer_name FROM domestic_customers LEFT JOIN international_customers ON domestic_customers.customer_id = international_customers.customer_id;

The UNION operator removes duplicate records from the combined results of the two SELECT statements.

Q24

Multiple Choice

Determine the correct SQL query that uses UNION ALL to combine the 'products' and 'services' tables, showing all product and service names, including duplicates.

SQL Code

SELECT product_name FROM products
UNION ALL
SELECT service_name FROM services;

SELECT product_name FROM products UNION ALL SELECT service_name FROM services; SELECT product_name FROM products UNION SELECT service_name FROM services; SELECT product_name FROM products JOIN services ON products.product_id = services.service_id;SELECT product_name FROM products LEFT JOIN services ON products.product_id = services.service_id;

The UNION ALL operator combines the results from the two SELECT statements without removing duplicates.

Q25

Multiple Choice

Which SQL query uses UNION to combine the results from the 'current_employees' and 'former_employees' tables, ensuring no duplicates are returned?

SQL Code

SELECT employee_id, name FROM current_employees
UNION
SELECT employee_id, name FROM former_employees;

SELECT employee_id, name FROM current_employees UNION SELECT employee_id, name FROM former_employees; SELECT employee_id, name FROM current_employees UNION ALL SELECT employee_id, name FROM former_employees; SELECT employee_id, name FROM current_employees JOIN former_employees ON current_employees.employee_id = former_employees.employee_id;SELECT employee_id, name FROM current_employees LEFT JOIN former_employees ON current_employees.employee_id = former_employees.employee_id;

The UNION operator combines the results from the two SELECT statements, removing duplicates from the final result set.

Q26

Multiple Choice

Which SQL query uses UNION ALL to combine the results from the 'vendors' and 'suppliers' tables, showing all vendor and supplier names, including duplicates?

SQL Code

SELECT vendor_name FROM vendors
UNION ALL
SELECT supplier_name FROM suppliers;

SELECT vendor_name FROM vendors UNION ALL SELECT supplier_name FROM suppliers; SELECT vendor_name FROM vendors UNION SELECT supplier_name FROM suppliers; SELECT vendor_name FROM vendors JOIN suppliers ON vendors.vendor_id = suppliers.supplier_id;SELECT vendor_name FROM vendors LEFT JOIN suppliers ON vendors.vendor_id = suppliers.supplier_id;

The UNION ALL operator is used here to combine the results of the two SELECT statements, including all duplicates.

Q27

Multiple Choice

Identify the correct SQL query that uses UNION to retrieve all distinct product names from the 'electronics' and 'furniture' tables.

SQL Code

SELECT product_name FROM electronics
UNION
SELECT product_name FROM furniture;

SELECT product_name FROM electronics UNION SELECT product_name FROM furniture; SELECT product_name FROM electronics UNION ALL SELECT product_name FROM furniture; SELECT product_name FROM electronics JOIN furniture ON electronics.product_id = furniture.product_id;SELECT product_name FROM electronics LEFT JOIN furniture ON electronics.product_id = furniture.product_id;

The UNION operator is used to combine the results from both tables while ensuring that only distinct product names are returned.

Q28

Multiple Choice

Which SQL query uses UNION ALL to combine the results from the 'north_america_sales' and 'europe_sales' tables, showing all sales records including duplicates?

SQL Code

SELECT sale_id, amount FROM north_america_sales
UNION ALL
SELECT sale_id, amount FROM europe_sales;

SELECT sale_id, amount FROM north_america_sales UNION ALL SELECT sale_id, amount FROM europe_sales; SELECT sale_id, amount FROM north_america_sales UNION SELECT sale_id, amount FROM europe_sales; SELECT sale_id, amount FROM north_america_sales JOIN europe_sales ON north_america_sales.sale_id = europe_sales.sale_id;SELECT sale_id, amount FROM north_america_sales LEFT JOIN europe_sales ON north_america_sales.sale_id = europe_sales.sale_id;

The UNION ALL operator is used to include all sales records from both tables, including duplicates.

Q29

Multiple Choice

Determine the correct SQL query that uses UNION to retrieve all unique student names from the 'undergraduates' and 'postgraduates' tables.

SQL Code

SELECT student_name FROM undergraduates
UNION
SELECT student_name FROM postgraduates;

SELECT student_name FROM undergraduates UNION SELECT student_name FROM postgraduates; SELECT student_name FROM undergraduates UNION ALL SELECT student_name FROM postgraduates; SELECT student_name FROM undergraduates JOIN postgraduates ON undergraduates.student_id = postgraduates.student_id;SELECT student_name FROM undergraduates LEFT JOIN postgraduates ON undergraduates.student_id = postgraduates.student_id;

The UNION operator is used to combine results from both tables while removing any duplicate student names.

Q30

Multiple Choice

Which SQL query uses UNION ALL to combine the results from the 'domestic_sales' and 'international_sales' tables, showing all sales records including duplicates?

SQL Code

SELECT sale_id, total_amount FROM domestic_sales
UNION ALL
SELECT sale_id, total_amount FROM international_sales;

SELECT sale_id, total_amount FROM domestic_sales UNION ALL SELECT sale_id, total_amount FROM international_sales; SELECT sale_id, total_amount FROM domestic_sales UNION SELECT sale_id, total_amount FROM international_sales; SELECT sale_id, total_amount FROM domestic_sales JOIN international_sales ON domestic_sales.sale_id = international_sales.sale_id;SELECT sale_id, total_amount FROM domestic_sales LEFT JOIN international_sales ON domestic_sales.sale_id = international_sales.sale_id;

The UNION ALL operator is used to include all sales records from both tables, including duplicates.

MySQL Database Quiz Questions