Inner join vs left join
当您只想返回两边都有对的记录时,您将使用 INNER JOIN,当您需要“左”表中的所有记录时,您将使用 LEFT JOIN,无论它们是否在“右”表中有对或不。如果您需要两个表中的所有记录,无论它们是否成对,您都需要使用 CROSS JOIN(或使用 LEFT JOIN 和 UNION 模拟它)
性能
LEFT JOIN 绝对不比 INNER JOIN 快。事实上,它更慢;根据定义,外连接(LEFT JOIN 或 RIGHT JOIN)必须完成 INNER JOIN 的所有工作加上空扩展结果的额外工作。它还会返回更多的行,进一步增加总执行时间只是因为结果集的大小更大。
即使 LEFT JOIN 在特定情况下由于一些难以想象的因素汇合而更快,它在功能上也不等同于 INNER JOIN,因此您不能简单地用另一个替换一个的所有实例!
SQL server 常用函数
1、PATINDEX(%pattern%, string):返回一个模式在字符串中的位置。
Parameter |
Description |
%pattern% |
Required. The pattern to find. It MUST be surrounded by %. Other wildcards can be used in pattern, such as:
|
string |
Required. The string to be searched |
Eg:
SELECT PATINDEX('%schools%', 'W3Schools.com'); return 3
2、STUFF(string, start, length, new_string): 删除字符串的一部分,然后从指定位置开始将另一部分插入到字符串中。
Parameter |
Description |
string |
Required. The string to be modified |
start |
Required. The position in string to start to delete some characters |
length |
Required. The number of characters to delete from string |
new_string |
Required. The new string to insert into string at the start position |
Eg:
SELECT STUFF('SQL Tutorial', 1, 3, 'HTML'); return HTML Tutorial
3、LTRIM(string):删除字符串中的前导空格
eg:
SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString; return
LeftTrimmedString |
SQL Tutorial |
4、RTRIM(string):删除字符串中的后导空格
5、CONVERT(data_type(length), expression, style):将任意类型的值转换为指定的数据类型。
Value |
Description |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
data_type |
Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
(length) |
Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
expression |
Required. The value to convert to another data type |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
style |
Optional. The format used to convert between data types, such as a date or string format. Can be one of the following values: Converting datetime to character:
Converting float to real:
Converting money to character:
|
eg:
SELECT CONVERT(datetime, '2017-08-25'); return 2017-08-25 00:00:00.000