Explain the difference between UNION and UNION ALL.

Explanation:

The UNION and UNION ALL SQL operations are used to combine the results of two or more SELECT queries. The main difference between them is that UNION removes duplicate records from the result set, whereas UNION ALL includes all duplicates.

Key Talking Points:

• UNION combines results and removes duplicates.
• UNION ALL combines results and includes duplicates.
• Both queries require the same number of columns and compatible data types in the SELECT statements.

NOTES:

Reference Table:

Pseudocode:

While no specific code is required for this question, you might encounter SQL syntax, such as:

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

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

Follow-Up Questions and Answers:

• Q: How does UNION handle column data types?

  A: Both queries must have the same number of columns and compatible data types. If there is a mismatch, the query will result in an error.

• Q: Can UNION or UNION ALL be used with ORDER BY?

  A: Yes, but the ORDER BY clause can only be used once after the final SELECT statement.

• Q: Which is more performance-efficient, UNION or UNION ALL?

  A: UNION ALL is more performance-efficient because it doesn't need to sort the result set to remove duplicates.