MariaDB Combined Queries

MariaDB Combined Queries

(jlive)[crashcourse]>SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|    1001 | ANV01   |       5.99 |

|    1001 | ANV02   |       9.99 |

|    1001 | ANV03   |      14.99 |

|    1002 | OL1     |       8.99 |

|    1002 | FU1     |       3.42 |

|    1003 | SLING   |       4.49 |

|    1003 | TNT1    |       2.50 |

|    1003 | FC      |       2.50 |

+---------+---------+------------+

8 rows in set (0.00 sec)

(jlive)[crashcourse]>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);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|    1002 | FU1     |       3.42 |

|    1003 | SLING   |       4.49 |

|    1003 | TNT1    |       2.50 |

|    1003 | FC      |       2.50 |

|    1001 | ANV01   |       5.99 |

|    1001 | ANV02   |       9.99 |

|    1001 | ANV03   |      14.99 |

|    1002 | OL1     |       8.99 |

+---------+---------+------------+

8 rows in set (0.00 sec)

UNION只是将完整查询语句查询到的结果整合到了一起并会自动去除重复行，故上面的语句，不使用UNION也可以通过OR来实现，但如果是完完全全地UNION, OR就无法做到，如：

(jlive)[crashcourse]>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);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|    1002 | FU1     |       3.42 |

|    1003 | SLING   |       4.49 |

|    1003 | TNT1    |       2.50 |

|    1003 | FC      |       2.50 |

|    1001 | ANV01   |       5.99 |

|    1001 | ANV02   |       9.99 |

|    1001 | ANV03   |      14.99 |

|    1002 | OL1     |       8.99 |

|    1002 | FU1     |       3.42 |

+---------+---------+------------+

 

9 rows in set (0.00 sec)

还可进行排序

(jlive)[crashcourse]>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) ORDER BY vend_id, prod_price DESC;

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|    1001 | ANV03   |      14.99 |

|    1001 | ANV02   |       9.99 |

|    1001 | ANV01   |       5.99 |

|    1002 | OL1     |       8.99 |

|    1002 | FU1     |       3.42 |

|    1003 | SLING   |       4.49 |

|    1003 | TNT1    |       2.50 |

|    1003 | FC      |       2.50 |

+---------+---------+------------+

 

8 rows in set (0.15 sec)