Subqueries in MySQL allow you to nest one SQL query within another. They can be used in a variety of contexts, including the SELECT, FROM, and WHERE clauses, as well as in conjunction with joins. In this guide, we will explain the basics of subqueries in MySQL with several practical examples.
Understanding UNION and UNION ALL
Both UNION and UNION ALL are used to combine the results of two or more SELECT statements. However, there's a key difference:
- UNION: Combines results from multiple SELECT statements and removes duplicate rows.
- UNION ALL: Combines results from multiple SELECT statements and includes all rows, including duplicates.
Basic Syntax
The basic structure for using UNION or UNION ALL is:
SELECT column_name(s) FROM table1
UNION [ALL]
SELECT column_name(s) FROM table2;Using UNION
Example: Combining Different Tables
Imagine you have two tables, ProductsA and ProductsB, both with similar structures. To combine data from both:
SELECT Name, Price FROM ProductsA
UNION
SELECT Name, Price FROM ProductsB;This query combines products from both tables into a single list, removing any duplicate products.
Using UNION ALL
Example: Including Duplicate Rows
If you want to include all products, even if they are duplicates:
SELECT Name, Price FROM ProductsA
UNION ALL
SELECT Name, Price FROM ProductsB;This query lists all products from both tables, including duplicates.
Sorting Results in UNION Queries
You can sort the combined result of a UNION or UNION ALL query using an ORDER BY clause.
Example: Sorted Combined Results
To sort the combined list of products by price:
SELECT Name, Price FROM ProductsA
UNION
SELECT Name, Price FROM ProductsB
ORDER BY Price;Combining Results from Grouped Data
UNION and UNION ALL can also combine results from queries that include grouping and aggregation.
Example: Aggregate Data from Different Tables
To get the count of products in each category from two tables:
SELECT Category, COUNT(*) AS Count FROM ProductsA GROUP BY Category
UNION
SELECT Category, COUNT(*) AS Count FROM ProductsB GROUP BY Category;Using UNION with Joins
UNION can be used to combine results from queries that involve joins.
Example: Combining Results from Joined Tables
If you have a Users table and an Orders table, and you want to list names of users who have placed orders and those who have not:
SELECT Users.Name FROM Users
JOIN Orders ON Users.UserID = Orders.UserID
UNION
SELECT Users.Name FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID
WHERE Orders.OrderID IS NULL;Column Number and Type Matching
When using UNION or UNION ALL, ensure that each SELECT statement has the same number of columns, in the same order, and with similar data types.