CC SQL 17-combining queries

Card Set Information

Author:
dau108
ID:
146358
Filename:
CC SQL 17-combining queries
Updated:
2012-04-08 10:07:29
Tags:
CC SQL 17 combining queries
Folders:

Description:
CC SQL 17-combining queries
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. creating combined queries
    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 will do this, but this time you'll use aUNION instead.

    As just explained, creating a UNION involves writing multiple SELECT statements. First look at the individual statements:

    • • InputSELECT 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 secondSELECT 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
    • WHERE vend_id IN (1001,1002);

    • Output

    • | vend_id | prod_id | prod_price |
    • +---------+---------+------------+
    • | 1003 | FC | 2.50 |
    • | 1002 | FU1 | 3.42 |
    • | 1003 | SLING | 4.49 |
    • | 1003 | TNT1 | 2.50 |
    • | 1001 | ANV01 | 5.99 |
    • | 1001 | ANV02 | 9.99 |
    • | 1001 | ANV03 | 14.99 |
    • | 1002 | OL1 | 8.99 |
    • +---------+---------+------------+
  2. union rules
    As you can see, unions are very easy to use. But a few rules govern exactly which can be combined:

    1. A UNION must be comprised of two or more SELECT statements, each separated by the keyword UNION (so, if combining four SELECT statements, three UNIONkeywords would be used).

    2. Each query in a UNION must contain the same columns, expressions, or aggregate functions (although columns need not be listed in the same order).

    3. Column datatypes must be compatible: They need not be the exact same type, but they must be of a type that MySQL can implicitly convert (for example, different numeric types or different date types).
  3. UNION ALL
    The UNION automatically removes any duplicate rows from the query result set (in other words, it behaves just as multiple WHERE clause conditions in a single SELECTwould). Because vendor 1002 creates a product that costs less than 5, that row was returned by both SELECT statements. When the UNION was used, the duplicate row was eliminated.

    This is the default behavior of UNION, but you can change this if you so desire. If you do, in fact, want all occurrences of all matches returned, you can use UNION ALLinstead of UNION.

    Look at the following example:

    • Input

    • SELECT vend_id, prod_id, prod_price
    • FROM products
    • WHERE prod_price <= 5
    • UNION ALL
    • SELECT vend_id, prod_id, prod_price
    • FROM products
    • WHERE vend_id IN (1001,1002);

    • Output

    • | vend_id | prod_id | prod_price |
    • +---------+---------+------------+
    • | 1003 | FC | 2.50 |
    • | 1002 | FU1 | 3.42 |
    • | 1003 | SLING | 4.49 |
    • | 1003 | TNT1 | 2.50 |
    • | 1001 | ANV01 | 5.99 |
    • | 1001 | ANV02 | 9.99 |
    • | 1001 | ANV03 | 14.99 |
    • | 1002 | FU1 | 3.42 |
    • | 1002 | OL1 | 8.99 |

    • • Analysis
    • Using UNION ALL, MySQL does not eliminate duplicates. Therefore, the preceding example returns nine rows, one of them occurring twice.

What would you like to do?

Home > Flashcards > Print Preview