SQL Learning--select (i) TOP, derived table, join, predicate

Top keywords

1 Select 4 with TIES t.title,sum (s.qty) as from sales s2 left JOIN titles T on S.title_ id=t.title_id3GROUP by T.title4 ORDER by TotalSales

View Code

The top 4 with TIES here is to fetch the first 4 data and require duplicate values, but note that this duplicate value is the row that will affect the returned data
For example, duplicate values appear in line 4th so that it is possible to return 5 rows of data (2 duplicates of values)
Returns only 4 data if duplicate values appear in the second row or on the third row
Also need to note the top N with TIES ... Need to be used in conjunction with order by or an error will occur.

Disadvantages of TOP N

　　Cannot return the first few of the result sets that are grouped in the GROUPBY clause of the query,
This indicates that top n points to the result set of the entire query, not to the rows in the source table or groups that have been categorized.
TOP N is the order of operations behind the entire SQL keyword. Instance:

1 SelectT.state,t.stor_name,sum (S.qty) asTotalSales2  fromSales S Join stores asT on s.stor_id=t.stor_id3 GROUP BY T.state,t.stor_name4 ORDER BY totalsales Desc5 6 SelectTop1T.state,t.stor_name,sum (S.qty) asTotalSales7  fromSales S Join stores asT on s.stor_id=t.stor_id8 GROUP BY T.state,t.stor_name9ORDER BY totalsales Desc

View Code

Derived tables

Select can also use derived tables (subqueries), also known as logical tables, in addition to directly referencing tables or attempts. It can be queried and linked like a table or a view

Select  from (Select from as a

This derived table is created by the SELECT * from authors syntax, where you can insert any valid query,

However, it is important to note that aliases are used here and must be used. Because T-SQL supports a non-list SELECT statement.

1 Select* from (2     Select 'Blotchet-halls'  asWeightclass,0  asLowbound, the  asHighbound3 Union All4     Select 'defrance'  asWeightclass, the  asLowbound,118  asHighbound5 Union All6     Select 'Green'  asWeightclass,127  asLowbound,135  asHighbound7) asW8ORDER BY W.lowbound

View Code

In the example, the table does not exist, but a logical table is formed through the Union All link, and the logical table can be connected to the table or attempt

Connection

　　In an inner JOIN, the clause order s does not affect the result set. If a equals B, then B is equal to a.

In an outer join, the order in the table directly affects which rows and values contained in the result set

1 Select sum(D.unitprice*d.quantity) asTotalorders fromOrders o2  Left Join [Order Details]D onO.orderid+Ten=D.orderid3  Left JoinProducts P onD.productid=P.productid4 5 Select sum(D.unitprice*d.quantity) asTotalorders from [Order Details]D6  Left JoinProducts P onD.productid=P.productid7  Left Join [Orders]O onO.orderid+Ten=D.orderid8 --the order of the connecting parts changed.

View Code

In the example of deliberately orderid+10 caused by the mismatch, observed two times the results of the query operation is not the same.

Because the table orders and order details that are caused by the first query do not match before the column unitprice*quantity is summarized,

The second query does not match when it occurs after a rollup. In the case of the second query, you get the sum of all the items in the details,

Regardless of whether he matches orders, this is not the case in the first query.

Take a look at what data does not match in 2 queries

1 SelectO.orderdate,d.unitprice,d.quantity fromOrders o2  Left Join [Order Details]D onO.orderid+Ten=D.orderid3  Left JoinProducts P onD.productid=P.productid4 whereO.orderdate is NULL orD.unitprice is NULL

View Code

After executing the statement, I will find the 10 data that I passed orderid+10.

So be careful when using external links to make it possible to have mismatched links.

predicate between

His role is to determine whether a given value falls within the two values of the internal

1 Select  from  2wherebetween's'and'  ZZ'3order by au_lname

View Code

Statements with subsets, variables, and expressions

1 Declare @au_idID2 Select @au_id=(Select Max(au_id) fromtitleauthor)3 4 SelectAu_lname,au_fname fromauthors5 whereau_idbetween(Select min(au_id) fromtitleauthor) and(ISNULL(@au_id,'zzzzzzzzzzzzz'))6 Order  byau_lname

View Code

Despite the between ... and is convenient, but sometimes it is difficult to define the range of multiple intervals. It is better to use the reverse thinking to eliminate the law, the deduction must occur, you can get what will not happen

Like

　Detecting a value-to-string pattern match

%: Indicates a match for any character

_: Indicates that only one character is matched

[AB]: Indicates matching a, B, AB

EXISTS

The handle query is the judgment function returned as a separate parameter. In the front of the exists, plus not means negation.

EXISTS specifies a subquery to detect the presence of a row. Iterate over the outer surface and see if the records in the appearance are the same as the data in the table. Match the result into the result set

Returns False if True does not form. If True is returned, the row result is retained and if False is returned, the row is deleted and the resulting result is returned.

　　Handling of NULL values in exists

1 SelectTitle fromtitles T2 where EXISTS(--is true at this time3     Select *  from( 4         Select * fromSales5         Union  All6         Select NULL,NULL,NULL, -,NULL,NULL7) s--a data of Null,qty 90 is inserted through UNION ALL8 wheret.title_id=s.title_id andS.qty> the)

View Code

The result of this query is finally empty. Why is it? The last inserted null is the data that satisfies where qty>75 why is it not returned?

The answer is that even though the connection condition is Titleid=titleid, and the inserted data titleid=null,null how could it be equal to null? Null is not equal to, nor equal to himself

exists and in

There are some peculiarities in replacing the exists with in.

1 Select Count(title) fromtitles T2 wheret.title_idinch(Selecttitle_id fromSales--16 Article3 4 Select Count(title) fromtitles T5 wheret.title_id not inch(Selecttitle_id fromSales--2 Article6 7 Select Count(title) fromtitles T8 wheret.title_id not inch(Selecttitle_id fromSalesUnion  All Select NULL)--0 Article

View Code

In the expression that compares a value with NULL is always returned NULL, so it does not conform to detection because other rows are the same as null in the same list, so return null. This is the difference between in and exists.

In addition, if the query statement uses not-in so that the appearance of the full table is scanned, no index is used, and not extsts sub-query can still use the index on the table. So no matter the table is large, using not exists is faster than not.

If the subquery results in fewer result set records, the table in the main query should be in if it is large and indexed, whereas if the outer main query records are small, the tables in the subquery are large and the indexes are indexed with exists.

It is recommended to use a connection, regardless of which seed query is used more slowly than the table connection.

Result set is empty

Another use of exists is to detect multiple rows of the result set.

if exists (SELECT * from sales) is definitely much faster than if (select count (*) from sales) >0, and provides a quick way to not check system objects to determine if a table is empty

Where and having a exists

Exists can also do a lot of other work, not just the rows returned by the query. Derived tables can also be used in case expressions and from clauses

Select Case-EXISTS (SELECT * from titleauthor where au_id=a.au_id) and then ' true ' else ' false ' end from authors a

Inch

1 Select *  fromTitleswheretitle_idinch2 (3     Selecttitle_id from (4         Select Top 99999title_id,Count(*) asNumberorder fromSalesGroup  bytitle_idOrder  byNumberorderdesc5 ) s6)

View Code

　　

SQL Learning--select (i) TOP, derived table, join, predicate