**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Working with Multiple Tables: Joins and Unions **Topic:** Combining datasets with UNION and UNION ALL **Introduction** In the previous topic, we covered the basics of JOIN operations in SQL. However, there are scenarios where you need to combine data from multiple tables without joining them. This is where the UNION and UNION ALL operators come into play. In this topic, we'll explore how to use these operators to combine datasets from multiple tables. **What are UNION and UNION ALL?** UNION and UNION ALL are SQL operators used to combine the result sets of two or more SELECT statements into a single result set. The primary difference between them lies in how they handle duplicate rows. * UNION removes duplicate rows from the combined result set, returning only unique rows. * UNION ALL returns all rows, including duplicates. **When to use UNION and UNION ALL** You should use UNION when you want to combine datasets and eliminate duplicates. This is particularly useful when working with data from multiple sources, and you want to avoid duplicate records. On the other hand, use UNION ALL when you want to combine datasets and preserve duplicates. This might be the case when you're working with data from multiple sources, and the duplicates are meaningful. **Syntax** The syntax for UNION and UNION ALL is as follows: ```sql SELECT column1, column2 FROM table1 UNION | UNION ALL SELECT column1, column2 FROM table2; ``` **Example 1: UNION** Suppose you have two tables, `employees_ny` and `employees_london`, containing employee data for New York and London offices, respectively. You want to combine the data and remove duplicates. ```sql CREATE TABLE employees_ny ( employee_id INT, name VARCHAR(100), department VARCHAR(50) ); CREATE TABLE employees_london ( employee_id INT, name VARCHAR(100), department VARCHAR(50) ); INSERT INTO employees_ny (employee_id, name, department) VALUES (101, 'John Smith', 'Sales'), (102, 'Mary Johnson', 'Marketing'), (103, 'David Brown', 'IT'); INSERT INTO employees_london (employee_id, name, department) VALUES (104, 'Emma Davis', 'HR'), (102, 'Mary Johnson', 'Marketing'), (105, 'Michael Wilson', 'Finance'); SELECT name, department FROM employees_ny UNION SELECT name, department FROM employees_london; ``` Result: | name | department | | --- | --- | | John Smith | Sales | | Mary Johnson | Marketing | | David Brown | IT | | Emma Davis | HR | | Michael Wilson| Finance | **Example 2: UNION ALL** Suppose you want to combine the data from `employees_ny` and `employees_london` and preserve duplicates. ```sql SELECT name, department FROM employees_ny UNION ALL SELECT name, department FROM employees_london; ``` Result: | name | department | | --- | --- | | John Smith | Sales | | Mary Johnson | Marketing | | David Brown | IT | | Emma Davis | HR | | Mary Johnson | Marketing | | Michael Wilson| Finance | **Best Practices and Key Takeaways** When using UNION and UNION ALL, keep the following best practices and key takeaways in mind: * Ensure that the number of columns and their data types match in both SELECT statements. * UNION removes duplicates, which can impact performance. Use UNION ALL when you want to preserve duplicates. * Use UNION and UNION ALL with care, as they can lead to increased query complexity and slower performance. **Conclusion** In this topic, we covered the basics of combining datasets using UNION and UNION ALL operators. By understanding the differences between UNION and UNION ALL, you can choose the right approach for your specific use case. Remember to consider performance implications and best practices when using these operators. **Additional Resources** For more information on UNION and UNION ALL, refer to the official documentation for your specific database management system. For example: * [MySQL Documentation: UNION](https://dev.mysql.com/doc/refman/8.0/en/union.html) * [PostgreSQL Documentation: UNION](https://www.postgresql.org/docs/13/queries-union.html) **What's Next?** In the next topic, we'll cover best practices for choosing the right type of join. You'll learn how to select the most efficient join type for your use case and optimize your queries for better performance. **Have Questions or Need Help?** Leave a comment below or ask for help if you have any questions about combining datasets with UNION and UNION ALL.