Differences between Oracle, Mysql, and SQL Server statements

1, the processing of NULL value --to determine whether it is empty, to take a value when empty, not empty when another value
　　

1), in SQL Server

ISNULL (Check_expression,replacement_value)

Explanation: If the check_expression value is empty, return replacement_value, or return the value of Check_expression directly

2), Mysql

Ifnull (EXPR1.EXPR2)

Explanation: If the value expr1 value is empty, return EXPR2, or return the value of EXPR1 directly

3), Oracle

NVL (EXPR1,EXPR2)                            ①nvl2 (EXPR1,EXPR2,EXPR3)                     ②nullif(EXPR1,EXPR2)                         ③

Explanation: If EXPR1 is null in ①, returns the value of EXPR1, otherwise returns the value of EXPR2
② if EXPR1 is null, returns the value of EXPR3, otherwise returns the value of EXPR2

③ in EXP1=EXPR2, returns NULL, otherwise returns the value of EXPR1

2, paging query

1), SQL Server

A simple way to do this is through top, for example

 select  top  10  *  from  testtable "where  code not  in   ( select  top  20  code from  testtable order  Span style= "color: #0000ff;" >by   ID)  order  by  ID 

This statement, in theory, the execution time of the overall statement should be longer than the execution time of the clause, but in fact, because the clause is returned after the execution of 20 records, and the entire statement only returns 10 records, so the most important factor affecting database response time is physical I/O operations. One of the most effective ways to do this now with physical I/O is to use the top keyword. The top keyword is a system-optimized word in SQL Server that extracts the first few percent data.

The above statement has a fatal disadvantage, that is, it contains not the word, and instead of EXISTS, the efficiency of the implementation of the time is not different. Of the above paging algorithm, the key factors that affect our query speed are two points: top and not in,top can improve our query speed, and not in will slow down our query speed, so to improve the speed of our entire paging algorithm, we must completely transform not in, and other methods to replace it.

We know that in almost any field, we can extract the maximum and minimum values in a field with Max (field) or min (field), so if this field is not duplicated, you can use the max or min of these non-repeating fields as a watershed to make it a reference for separating each page in the paging algorithm. Here, we can use the operator ">" or "<" to accomplish this mission. Such as:

　　

SELECT TOPPageSize*  fromTable1WHEREId>(     SELECT MAX(ID) from (          SELECT TOP((currentpage-1)*pageSize) ID fromTable1ORDER  byID) asT)ORDER  byId

How much of this method does not always have a big drop, the stamina is still very sufficient, especially when the data volume is large, the method

Execution speed is not reduced at all. Using top requires that the primary key must be unique and cannot be a federated primary key. If it is a federated primary key, the results of the query may be out of order.

Another way is to use the Row_number () function, for example:

SELECT TOP Ten *  from (     SELECTTOPten over (ORDERby as   from TABLE1)  asWHERE RowNo>currentpage  *Ten

2), Mysql

MySQL paging is relatively simple, directly using the limit to achieve, it has one or two parameters, the first parameter to the return of the first row of data location, starting from 0, the second parameter specifies the maximum number of rows returned. For example:

SELECT *  from Table WHERE... LIMITTen; #返回前10行SELECT *  from Table WHERE... LIMIT0,Ten; #返回前10行SELECT *  from Table WHERE... LIMITTen, -; #返回10-20 Rows of data

3), Oracle

The first type:

Select *  from (     select T.* over (order by from TABLE1)    where between  +  and ;

The second type:

Select *  from (    select T.*as fromwherebetween  Ten  and  -

The second method is better. Because the order BY statement is not used, it can improve the speed of retrieving data, especially the larger the amount of data, the more obvious the second method is to retrieve data quickly.

Differences between Oracle, Mysql, and SQL Server statements