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

Today, I will talk to you about how to understand the set operation of SQL sentences, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

In the past, always pursuing new things, discovering the basics is the most important, but this year's main goal is to be proficient in SQL queries and SQL performance optimization.

Overview

It is mainly a summary of the basis of union, intersection and difference operation in set operation.

There are four kinds of set operations:

1. Union operation (two kinds)

two。 Intersection operation

3. Difference operation

The following is a mind map of set operations:

Why to use set operation

1. It is more convenient than join query and EXISTS/NOT EXISTS in set operation.

When reading the following chapters, we can first prepare the environment, the following SQL script can help you create a database, create tables, and insert data.

I. set operation

1. Set operation

(1) an operation on two or more sets of input.

(2) multiple sets: an intermediate result set that may contain duplicate records generated by a query with two inputs.

(3) T-SQL supports three kinds of set operations: UNION, INTERSECT and EXCEPT.

two。 Grammar

The basic format of set operations:

Query 1 entered

Collection operator >

Query 2 entered

[ORDER BY]

3. Request

(1) the query entered cannot contain ORDER BY words and sentences

(2) An ORDER BY sentence can be selectively added to the whole set operation result.

(3) each individual query can contain all logical query processing phases (processing ORDER BY sentences that control the order of arrangement)

(4) two queries must contain the same number of columns

(5) the corresponding column must have compatible data types. Compatible data types: lower priority data types must be implicitly converted to higher data types. For example, if query 1 is listed as int type and query 2 as float type, the lower data type int type can be implicitly converted to a higher float type. If query 1 is listed as char type and query 2 as datetime type, the conversion failed: conversion failed when converting date and / or time from a string

(6) the column name in the set operation result is determined by the query 1 entered. If you want to assign the result column to the result, you should assign the corresponding alias in the query 1 entered.

(7) in the set operation, when comparing the rows, the set operation considers that the two NULL are equal.

(8) UNION supports DISTINCT and ALL. The specified DISTINCT sentence cannot be displayed. If ALL is not specified, DISTINCT is used by default

(9) INTERSET and EXCEPT use DISTINCT by default and do not support ALL.

2. UNION (Union) set operation

1. Venn's diagram of union

Union: the union of two sets is a set that contains all the elements in sets An and B.

The shaded area in the graph represents the union of set An and set B.

2.UNION ALL set operation

(1) if Query1 returns m rows and Query2 returns n rows, then Query1 UNION ALL Query2 returns (m rows)

(2) UNION ALL does not delete duplicate rows, so its result is multiple sets, not real collections.

(3) the same row may appear multiple times in the result.

3.UNION DISTINCT set operation

(1) if Query1 returns m rows, Query2 returns n rows, and Query1 and Query2 have the same h rows, then Query1 UNION Query2 returns (m+n-h) rows

(2) UNION deletes duplicate rows, so its result is a collection

(3) the same row appears only once in the result.

(4) the specified DISTINCT sentence cannot be displayed. If ALL is not specified, DISTINCT is used by default.

(5) when Query1 and Query2 compare a row of records to see if they are equal, the column with a value of NULL is considered to be equal.

3. INTERSECT (intersection) set operation

1. Intersecting Venn's graph

Intersection: the intersection of two sets (marked as set An and set B) is a set of all elements that belong to both An and B.

The shaded area in the graph represents the intersection of set An and set B.

2.INTERSECT DISTINCT set operation

(1) if Query1 returns m rows, Query2 returns n rows, and Query1 and Query2 have the same h rows, then Query1 INTERSECT Query2 returns h rows.

(2) the INTERSECT set operation logically deletes the duplicate rows of two input multiple sets (turning the multiple sets into sets), and then returns the rows that only appear in both sets.

(3) INTERSECT deletes duplicate rows, so its result is a collection

(4) the same row appears only once in the result.

(5) the specified DISTINCT sentence cannot be displayed. If ALL is not specified, DISTINCT is used by default.

(6) when Query1 and Query2 compare a row of records to see if they are equal, the column with a value of NULL is considered to be equal.

(7) the inner join or EXISTS predicate can be used instead of INTERSECT set operation, but NULL must be processed, otherwise when the null values of the two methods are compared, the result is UNKNOWN, and such rows will be filtered out.

3.INTERSECT ALL set operation

(1) ANSI SQL supports INTERSECT set operations with the ALL option, but SQL Server2008 has not implemented this operation yet. An alternative to the T-SQL implementation will be provided later

(2) suppose Query1 returns m rows and Query2 returns n rows. If row R appears x times in Query1 and y times in Query2, then row R should appear minimum (XMague y) times after INTERSECT ALL operation.

The following provides INTERSECT ALL set operations for T-SQL implementation: common table expression + ranking function

The results are as follows:

Where UK NULL London has four duplicate lines

Using ORDER BY (SELECT constant >) in the OVER sentence of the sort function tells SQL Server that you don't care about the order of the lines.

4. EXCEPT (difference) set operation

1. Venn's diagram of difference set

Difference: a set of two sets (denoted as set An and set B) consisting of all elements belonging to set A but not to set B.

The shaded area in the graph represents the difference between set An and set B.

2.EXCEPT DISTINCT set operation

(1) if Query1 returns m rows, Query2 returns n rows, Query1 and Query2 have the same h rows, then Query1 INTERSECT Query2 returns m-h rows and Query2 INTERSECT Query1 returns n-h rows.

(2) the EXCEPT set operation logically deletes the duplicate rows of two input multiple sets (converting multiple sets into sets), and then returns that it only appears in * * sets, but not all rows in the second set.

(3) EXCEPT deletes duplicate rows, so its result is a collection

(4) the EXCEPT is asymmetric, and the result of the difference set depends on the relationship between the two queries.

(5) the same row appears only once in the result.

(6) the specified DISTINCT sentence cannot be displayed. If ALL is not specified, DISTINCT is used by default.

(7) when Query1 and Query2 compare a row of records to see if they are equal, the column with a value of NULL is considered to be equal.

(8) the left outer join or NOT EXISTS predicate can be used instead of INTERSECT set operation, but NULL must be processed, otherwise when the null values of the two methods are compared, the result is UNKNOWN, and such rows will be filtered out.

3.EXCEPT ALL set operation

(1) ANSI SQL supports EXCEPT set operations with the ALL option, but SQL Server2008 has not implemented this operation yet. An alternative to the T-SQL implementation will be provided later

(2) suppose Query1 returns m rows and Query2 returns n rows. If row R appears x times in Query1 and y times in Query2, and x > y, then row R should appear x-y times after EXCEPT ALL operation.

The following provides EXCEPT ALL set operations for T-SQL implementation: common table expression + ranking function

WITH INTERSECT_ALL AS (SELECT ROW_NUMBER () OVER (PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country Region, city FROM HR.Employees EXCEPT SELECT ROW_NUMBER () OVER (PARTITION BY country, region City ORDER BY (SELECT 0)) AS rownum, country, region City FROM Sales.Customers) SELECT country, region, city FROM INTERSECT_ALL

The results are as follows:

Fifth, the priority of set operation

1.INTERSECT > UNION=EXCEPT

two。 The INTERSECT is calculated first, and then the same priority operations are processed in the order of occurrence from left to right.

3. You can use parentheses to control the priority of set operations, which has a priority of *.

VI. Special treatment

1. Only ORDER BY can be directly applied to the results of set operations.

two。 Other stages, such as table operators, WHERE, GROUP BY, HAVING, etc., do not support the results directly applied to set operations, so you can use table expressions to avoid this limitation. For example, define a table expression based on a query that contains set operations, and then apply any required logical query processing to the table expression in an external query

3.ORDER BY sentences can not be directly applied to a single query in set operations, and this limitation can be avoided by using TOP+ORDER BY sentences + tabular expressions. For example, define a table expression based on the TOP query, and then participate in the collection operation through an external query that uses the table expression.

7. Exercises

1. Write a query that returns customers and employees who had order activity in January 2008 but not in February 2008.

Expected results:

Option 1: EXCEPT

(1) use query 1 to find out the customers and employees who have order activities in January 2008.

(2) use query 2 to query customers and employees for order activities of customers in February 2008

(3) use the difference set operator to query customers and employees who have order activity in January 2008 but not in February 2008

SELECT custid, empid FROM Sales.Orders WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

Option 2: NOT EXISTS

You must ensure that custid,empid cannot be null before you can query with NOT EXISTS. If there are null values in custid or empid, you cannot query with NOT EXISTS. Because the result of comparing null values is UNKNOWN, the rows of subqueries returned by NOT EXISTS queries for such rows will be filtered out, so the outer query of * will have more rows of null values, and * more rows of null values will be added in the query results.

SELECT custid, empid FROM Sales.Orders AS O1 WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

If I insert two rows of data into the Sales.Orders table:

Insert cutid=NULL,empid=1,orderdate='20080101'

INSERT INTO [TSQLFundamentals2008]. [Sales]. [Orders] ([custid], [empid], [orderdate], [requireddate], [shippeddate], [shipperid], [freight], [shipname], [shipaddress], [shipcity] [shipregion], [shippostalcode], [shipcountry]) VALUES (NULL, 1, '20080101', '20080101', '20080101', 1, 1,'A', '20080101' 'A','A') GO

Insert cutid=NULL,empid=1,orderdate='20080201'

INSERT INTO [TSQLFundamentals2008]. [Sales]. [Orders] ([custid], [empid], [orderdate], [requireddate], [shippeddate], [shipperid], [freight], [shipname], [shipaddress], [shipcity] [shipregion], [shippostalcode], [shipcountry]) VALUES (NULL, 1, '20080201', '20080101', '20080101', 1, 1,'A', '20080101' 'A','A') GO

If the query result of solution 1 is 50 rows, the rows of cutid=NULL,empid=1 will be filtered out.

The query result of scheme 2 is 51 rows, and the rows of cutid=NULL,empid=1 will not be filtered out.

The above problem can be solved with the following solution, which needs to deal with the case of cutid=NULL, or empid=null. Return 50 rows

SELECT custid, empid FROM Sales.Orders AS O1 WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

two。 Write a query that returns customers and employees who have order activity in January 2008 and February 2008.

Expected results:

Option 1: INTERSECT

(1) use query 1 to find out the customers and employees who have order activities in January 2008.

(2) use query 2 to query customers and employees for order activities of customers in February 2008

(3) use the intersection operator to query customers and employees who have order activities in January 2008 and February 2008

SELECT custid, empid FROM Sales.Orders WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

Option 2: EXISTS

You must ensure that custid,empid cannot be null before you can query with EXISTS. If there are null values in custid or empid, you cannot query with EXISTS, because the result of comparing null values is UNKNOWN, and the rows of subqueries returned by the EXISTS query for such rows will be filtered out, so the outer query of * * will have fewer rows with NULL value, and the query result will have fewer rows with NULL value.

SELECT custid, empid FROM Sales.Orders AS O1 WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

If I insert two rows of data into the Sales.Orders table:

Insert cutid=NULL,empid=1,orderdate='20080101'

Insert cutid=NULL,empid=1,orderdate='20080201'

The query result of solution 1 is 6 rows, and the rows of cutid=NULL,empid=1 will not be filtered out.

The query result of solution 2 is 5 rows, which will filter out the rows of cutid=NULL,empid=1.

The above problem can be solved with the following solution, which needs to deal with the case of cutid=NULL, or empid=null. Return 6 rows.

SELECT custid, empid FROM Sales.Orders AS O1 WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate

3. Write a query that returns customers and employees who had order activity in January 2008 and February 2008, but not in 2007

Expected results:

Option 1: INTERSECT + EXCEPT

SELECT custid, empid FROM Sales.Orders WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate =' 20070101 'AND orderdate

Option 2: EXISTS + NOT EXISTS

SELECT custid, empid FROM Sales.Orders AS O1 WHERE orderdate > = '20080101' AND orderdate = '20080201' AND orderdate =' 20070101 'AND orderdate after reading the above, do you have any further understanding of how to understand the set operation of SQL statements? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.