Page 172 - SQL

Selection with nulls take a different syntax. Don't use =, use IS NULL or IS NOT NULL instead.

Selecting with CASE

When results need to have some logic applied 'on the fly' one can use CASE statement to
implement it.

SELECT CASE WHEN Col1 < 50 THEN 'under' ELSE 'over' END threshold
FROM TableName

also can be chained

SELECT
CASE WHEN Col1 < 50 THEN 'under'
WHEN Col1 > 50 AND Col1 <100 THEN 'between'
ELSE 'over'
END threshold
FROM TableName

one also can have CASE inside another CASE statement

SELECT
CASE WHEN Col1 < 50 THEN 'under'
ELSE
CASE WHEN Col1 > 50 AND Col1 <100 THEN Col1
ELSE 'over' END
END threshold
FROM TableName

Selecting without Locking the table

Sometimes when tables are used mostly (or only) for reads, indexing does not help anymore and
every little bit counts, one might use selects without LOCK to improve performance.

SQL Server

SELECT * FROM TableName WITH (nolock)

MySQL

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TableName;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Oracle

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TableName;

https://riptutorial.com/ 154