Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use set operators in the database, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

I. Description of official courseware collection types

II. Precautions

Column names and expressions in SELECT lists must match quantitatively

The data type of each column in the second query must match the data type of its corresponding column in the first query.

3, can be used to change the execution order of parentheses.

4. ORDER BY clause

a) can only appear at the end of a statement

b) column names, aliases, or relative positions from the first query can be used

c) Except for UNION ALL, duplicate records are automatically deleted.

5. Column name is the result returned by the first query

6. Except for UNION ALL, the system automatically sorts the first column in the first query in ascending order.

III. Examples

--union union, the common part contains only once

--union: find emp table name contains 'A' or contains 'M'

SELECT *

FROM EMP

WHERE ENAME LIKE '%A%'

UNION

SELECT *

FROM EMP

WHERE ENAME LIKE '%M%';

--union all: the common part contains only twice

--Find an emp table name that contains 'A' or 'M'

SELECT *

FROM EMP

WHERE ENAME LIKE '%A%'

UNION ALL

SELECT *

FROM EMP

WHERE ENAME LIKE '%M%';

--intersect, only contain common parts

--find emp table name contains both 'A' and 'M'

SELECT *

FROM EMP

WHERE ENAME LIKE '%A%'

INTERSECT

SELECT *

FROM EMP

WHERE ENAME LIKE '%M%';

--minus Find the difference set, find the set A, remove the intersection of set A and set B

--find emp table sal from 700 to 1200

SELECT *

FROM EMP

WHERE SAL BETWEEN 700 AND 1300

MINUS

SELECT *

FROM EMP

WHERE SAL BETWEEN 1200 AND 1400;

Thank you for reading this article carefully. I hope that the article "How to Use Set Operators in Databases" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.