![]() Besides mailing lists, other applications for unions include when combining archived data with active data or comparing data in different tables that share a similar structure. This results in a single list of all staff and customers, with a shared data structure. Instead, we simply combine the result sets, knowing that duplicates may exist. The inner query below uses UNION to combine store sales with online sales, so it vertically appends the two types of sales. Notice that we use UNION ALL instead of UNION to prevent MySQL from eliminating duplicate rows, which can be computationally expensive when we have a large result set. SELECT first_name, last_name, email_address FROM customers UNION ALL SELECT first_name, last_name, email_address FROM staff You want to combine the results of both tables into a single result set, to produce a mailing list. The Student table contains three column studentid, studentname, and city and the table TechStack also contains three columns studentname, city and technology. JOIN in SQL Suppose we are given two tables Student and TechStack. UNIONs are best used when we need to combine two queries that would otherwise be difficult to create in one query.įor example, let's say you have two tables, customers and staff, both with similar column names. Example of JOIN and UNION in SQL In this section, we will go through examples of the JOIN clause and UNION clause in SQL. Looking at the above query, you'll see that this could be achieved with a simple SELECT statement. This union query collects the names of people from the Customers table and combines. MySQL Tryit Editor v1.0 SQL Statement: x SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City Edit the SQL Statement, and click 'Run SQL' to see the result. Also, bear in mind that the number of columns in all SELECT statements must be the same, with the same data types, or else you'll get an error. Here is an example that you can recreate in the Northwind sample database. Notice how I used the UNION keyword to combine the two results. This will output the result set of 1 and 2, as if they were in a single column. Specifically, it takes the values from one table or query, and instead of putting them side-by-side with another table or another query, it puts them together over-under. A UNION query is used to combine the results of two or more SELECT statements into a single result set.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |