MariaDB Crash Course - Ben Forta [49]
• Pay careful attention to the type of join being used. More often than not, you’ll want an inner join, but there are often valid uses for outer joins, too.
• Make sure you use the correct join condition, or you’ll return incorrect data.
• Make sure you always provide a join condition, or you’ll end up with the Cartesian product.
• You may include multiple tables in a join and even have different join types for each. Although this is legal and often useful, make sure you test each join separately before testing them together. This makes troubleshooting far simpler.
Summary
This chapter was a continuation of the previous chapter on joins. This chapter started by teaching you how and why to use aliases, and then continued with a discussion on different join types and various forms of syntax used with each. You also learned how to use aggregate functions with joins, and some important do’s and don’ts to keep in mind when working with joins.
17. Combining Queries
In this chapter you learn how to use the UNION operator to combine multiple SELECT statements into one result set.
Understanding Combined Queries
Most SQL queries contain a single SELECT statement that returns data from one or more tables. MariaDB also enables you to perform multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are usually known as unions or compound queries.
There are basically two scenarios in which you’d use combined queries:
• To return similarly structured data from different tables in a single query
• To perform multiple queries against a single table returning the data as one query
* * *
Tip: Combining Queries and Multiple WHERE Conditions
For the most part, combining two queries to the same table accomplishes the same thing as a single query with multiple WHERE clause conditions. In other words, any SELECT statement with multiple WHERE clauses can also be specified as a combined query, as you see in the section that follows. However, the performance of each of the two techniques can vary based on the queries used. As such, it is always good to experiment to determine which is preferable for specific queries.
* * *
Creating Combined Queries
SQL queries are combined using the UNION operator. Using UNION, multiple SELECT statements can be specified, and their results can be combined into a single result set.
Using UNION
Using UNION is simple enough. All you do is specify each SELECT statement and place the keyword UNION between each.
Let’s look at an example. You need a list of all products costing 5 or less. You also want to include all products made by vendors 1001 and 1002, regardless of price. Of course, you can create a WHERE clause that does this, but this time we use a UNION instead.
As just explained, creating a UNION involves writing multiple SELECT statements. First look at the individual statements:
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;
Output
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
Output
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
Analysis
The first SELECT retrieves all products with a price of no more than 5. The second SELECT uses IN to find all products made by vendors 1001 and 1002.
To combine these two statements, do the following:
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products