W3. SQL Union

Question 1

Q: What does the SQL UNION operator do?

Answer 1

A: It combines the result sets of two or more SELECT statements into a single result set.

Question 2

Q: What requirements must be met for UNION to work?

Answer 2

A: Each SELECT statement must have the same number of columns, with similar data types, and the columns must be in the same order.

Question 3

Q: Write the syntax for a basic UNION.

Answer 3

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Question 4

Q: What does UNION ALL do differently than UNION?

Answer 4

A: UNION ALL includes duplicate values, whereas UNION only returns distinct values.

Question 5

Q: Write the syntax for UNION ALL.

Answer 5

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Question 6

Q: Write a query to return distinct cities from both “Customers” and “Suppliers”.

Answer 6

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Question 7

Q: How can you modify a UNION to include duplicate values?

Answer 7

A: Use UNION ALL instead of UNION.

Question 8

Q: Write a query to return German cities (distinct) from both “Customers” and “Suppliers”.

Answer 8

SELECT City, Country FROM Customers
WHERE Country = ‘Germany’
UNION
SELECT City, Country FROM Suppliers
WHERE Country = ‘Germany’
ORDER BY City;

Question 9

Q: Write a query to list all customers and suppliers, labeling each as “Customer” or “Supplier”.

Answer 9

SELECT ‘Customer’ AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT ‘Supplier’, ContactName, City, Country
FROM Suppliers;

Question 10

Q: What is the purpose of using AS Type in the last example?

Answer 10

A: It creates an alias, Type, to indicate whether each record is a “Customer” or a “Supplier”.