How to use collection operators in a database
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!
