SQL skills (MS SQL Server)

發表於 分類於
AdSense

Foreword

The following notes are pain points that I encountered when I was doing my job. Listed all of them to remember them and also hope I can help someone who also comes across this issue.

Putting anything related to a LEFT JOIN table into the WHERE clause will automatically turn it into an INNER JOIN. Because if the values of the row of tableB are all NULL, b.age(NULL) cannot compare with 30, SQL Server turn a LEFT JOIN into an INNER JOIN.

1
2
3
SELECT * FROM tableA a
LEFT JOIN tableB b ON b.staffID = a.staffID
WHERE b.age = 30

OUTER APPLY used for derived table including an outer query and table-valued functions

OUTER APPLY is used to join a table-valued function or a derived table that refers to the outer query for each row. To add more conditions in the derived table, I use OUTER APPLY instead of LEFT (OUTER) JOIN.

1
2
3
4
5
6
7
8
-- Select top n data in tableB and order according to data in tableB
SELECT * 
FROM tableA a
OUTER APPLY (SELECT TOP 2 * 
             FROM tableB b 
             WHERE b.staffID = a.staffID 
             ORDER BY b.timestamp DESC) tableB_2
WHERE a.year = 2021

The statement of CASE Statement after THEN should not be conditions

The CASE Statement looks like this:

1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

The result should not be conditions.

How can I do if I want to make a comparision under certain conditions?

This is wrong, but I just use this to express ideas.

1
2
3
4
5
6
7
8
-- isRight is bit type
SELECT * 
FROM tableA a
LEFT JOIN tableB b ON
    (CASE
        WHEN b.isRight = 1 THEN b.staffID = a.staffID
        ELSE b.orderID = a.orderID
    END)
  1. CASE = CASE
1
2
3
4
5
6
7
8
9
10
11
SELECT * 
FROM tableA a
LEFT JOIN tableB b ON
    (CASE
        WHEN b.isRight = 1 THEN b.staffID
        ELSE b.orderID
    END) =
    (CASE
        WHEN b.isRight = 1 THEN a.staffID
        ELSE a.orderID
    END)
  1. (CASE WHEN TRUE THEN 1 ) = 1
1
2
3
4
5
6
7
8
9
SELECT * 
FROM tableA a
LEFT JOIN tableB b ON
    (CASE
        WHEN b.isRight = 1 AND b.staffID = a.staffID THEN 1
        WHEN b.isRight = 0 AND b.orderID = a.orderID THEN 1
        ELSE -1        -- b.isRight = 1 AND b.staffID != a.staffID
                       -- b.isRight = 0 AND b.orderID != a.orderID
    END) = 1

CONCAT() and SUBSTRING()

1
2
SELECT CONCAT(staffFirstName, ' - ',  staffSurname) FROM tableA
WHERE staffSurname = 'Golden'
1
2
SELECT SUBSTRING('Hi, I am Jenifer', 2, 12)
-- Output: i, I am Jeni

GROUP BY = DISTINCT

1
2
SELECT zipcode FROM tableStores
GROUP BY zipcode
1
SELECT DISTINCT zipcode FROM tableStores

However, if I’d like to use aggregate function, I can’t use DISTINCT.

1
2
3
SELECT zipcode, COUNT(*) AS count
FROM tableStores
GROUP BY zipcode

GROUP BY and Aggregate Function

storeID branchName revenue year
10 AAA 1000 2020
11 BBB 50 2020
12 BBB 25 2021 
1
2
3
SELECT branchName, SUM(revenue)
FROM tableStores
GROUP BY branchName

The result:

branchName revenue
AAA 1000
BBB 75

HAVING and GROUP BY and Aggregate Function

HAVING is WHERE for GROUP BY and Aggregate Function.

1
2
3
4
SELECT branchName, SUM(revenue) AS sum
FROM tableStores
GROUP BY branchName
HAVING SUM(revenue) > 100

The result:

branchName sum
AAA 1000

In the HAVING clause, I must use original name and aggregate function to calculate again. I cannot use HAVING sum > 100. The following statement is incorrect.

1
2
3
4
SELECT branchName, SUM(revenue) AS sum
FROM tableStores
GROUP BY branchName
HAVING sum > 100

N’XXXXXString’

N’XXXXXString’ - this N next to the string enclosed in single quotes mean that the string is NVARCHAR type if N wouldn’t be there it would be VARCHAR.

Check if the object already exists or not

1
2
3
4
5
6
7
8
9
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmpEmployee]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [tmpEmployee] 
END

IF OBJECT_ID (N'GetTop2EmpForDep', N'IF') IS NOT NULL
BEGIN
    DROP FUNCTION GetTop2EmpForDep
END

Get Top 10 Rows from a Table Randomly

The NEWID() function in SQL Server returns a unique ID or a random value.
To return the rows in random order, adding the NEWID() function with the ORDER BY clause.

1
2
SELECT TOP 10 * FROM tablA 
ORDER BY NEWID()

How to get TIME part?

Use data_type(length) or style of CONVERT().
How to get current TIME in Sql Server

1
2
3
4
5
SELECT CONVERT(varchar(10), GETDATE(), 108)
SELECT CONVERT(time, GETDATE())

-- Output: 12:43:38
-- Output: 12:43:38.6370000

How to compare time in SQL Server?

  1. Convert data type (Slower):
1
2
IF CONVERT(varchar(8), SYSDATETIME(), 108) BETWEEN '08:00:00' AND '17:30:00'
    PRINT 'Work Time.'
  1. Compare as a floating point number (Efficient way):
    A date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time. Conversions between datetime and float are pretty fast, because they’re basically stored in the same way.

How can I compare time in SQL Server?

EXEC sp_executesql @sql = EXEC (@sql)

1
2
3
4
5
6
7
8
9
DECLARE @sql	nvarchar(max)

SET @sql = 'SELECT name 
            FROM tableStores 
            WHERE name = ''Zara'''

-- The followings have the same results
EXEC sp_executesql @sql
EXEC (@sql)