**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Working with Multiple Tables: Joins and Relationships **Topic:** Combining data from multiple tables with UNION and UNION ALL. Combining data from multiple tables is a common task in database management. In previous topics, we covered JOIN operations that allow you to combine data from multiple tables based on a common column. However, there are scenarios where you want to combine data from multiple tables without a common column. This is where UNION and UNION ALL operators come in. **What is UNION in SQLite?** The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The resulting table will have the same structure as the SELECT statements, with columns from the first SELECT statement. The UNION operator eliminates duplicate rows between the various SELECT statements. **Syntax for UNION:** ```sql SELECT column_name1, column_name2 FROM table_name1 UNION SELECT column_name1, column_name2 FROM table_name2; ``` **What is UNION ALL in SQLite?** The UNION ALL operator is similar to the UNION operator, but it combines the result sets of two or more SELECT statements into a single result set, including duplicate rows. **Syntax for UNION ALL:** ```sql SELECT column_name1, column_name2 FROM table_name1 UNION ALL SELECT column_name1, column_name2 FROM table_name2; ``` **Key differences between UNION and UNION ALL:** 1. **Duplicate rows**: UNION eliminates duplicate rows between the various SELECT statements, while UNION ALL includes all rows, including duplicates. 2. **Performance**: UNION ALL is generally faster than UNION because it doesn't require the removal of duplicate rows. 3. **Result set**: The resulting table structure is the same for both UNION and UNION ALL. **Example of UNION in SQLite:** Suppose we have two tables, `orders_2020` and `orders_2021`, with columns `id`, `customer_name`, and `order_total`. We want to get a list of all customers who have placed an order in either 2020 or 2021. ```sql CREATE TABLE orders_2020 ( id INTEGER PRIMARY KEY, customer_name TEXT, order_total REAL ); CREATE TABLE orders_2021 ( id INTEGER PRIMARY KEY, customer_name TEXT, order_total REAL ); INSERT INTO orders_2020 (id, customer_name, order_total) VALUES (1, 'John Doe', 100.00), (2, 'Jane Doe', 200.00); INSERT INTO orders_2021 (id, customer_name, order_total) VALUES (1, 'John Doe', 150.00), (3, 'Bob Smith', 300.00); SELECT customer_name FROM orders_2020 UNION SELECT customer_name FROM orders_2021; ``` The result will be: | customer_name | | --- | | John Doe | | Jane Doe | | Bob Smith | **Example of UNION ALL in SQLite:** Using the same tables and data as the previous example, we can use UNION ALL to get a list of all customers, including duplicates. ```sql SELECT customer_name FROM orders_2020 UNION ALL SELECT customer_name FROM orders_2021; ``` The result will be: | customer_name | | --- | | John Doe | | Jane Doe | | John Doe | | Bob Smith | **Best practices for using UNION and UNION ALL:** 1. **Use UNION when duplicate rows are not desired**. 2. **Use UNION ALL when performance is critical**, and duplicate rows are not a concern. 3. **Ensure that the SELECT statements have the same number of columns**, and the corresponding columns have compatible data types. **Conclusion:** In this topic, we covered the UNION and UNION ALL operators in SQLite, which allow you to combine data from multiple tables without a common column. We explored the syntax, key differences, and best practices for using these operators. With this knowledge, you can effectively combine data from multiple tables and achieve your desired results. **External resources:** * SQLite documentation on UNION: <https://sqlite.org/lang_select.html#compound> * Stack Overflow: Differences between UNION and UNION ALL: <https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all> Do you have any questions about UNION and UNION ALL in SQLite? Please leave a comment below, and we'll be happy to help. In the next topic, we'll cover 'Choosing the right type of join for different use cases.'