Advanced SQL
Joins
Best practices / tips:
ALWAYS use explicit join syntax, no join conditions in the WHERE clause
Try to only use inner or left joins if possible, reorder joins if necessary to achieve this
User proper indentation in your selects to maintain readability and add comments
Start with the most limiting table in terms of “relevant for final result” (often the optimizer takes care of this)
Know your tables, check for usable indexes and be aware of their ORDER
When you use DBISQL, you can check out if indexes were used, by looking into the PLAN VIEWER in the extras menu (SHIFT+F5). To get real query times, use the 3rd statistics option
If you need all columns from a specific table in your multi table select, you can use Tablename.* to achieve this, that way you do not query all tables from your select
Use table aliases for all tables with long names
When referencing columns, always prefix with the table name / alias. If in the future someone adds a new column to one of the referenced tables, you might avoid a same column name conflict, which would otherwise break your statement.
Union / Intersect / Except
The following keywords allow you to combine results of multiple selects as long as they match in column types and count. If necessary you can fill up with NULLs.
UNION: Concatenates the results of both selects INTERSECT: Shows rows, which were present in both selects EXCEPT: Shows rows, which were present exclusively in one of the selects
By default the mode DISTINCT is used, which is normally a tad slower because the database has to eliminate duplicates from the final result. If you append the keyword ALL this behavior is suppressed.
SELECT
artnr,
'KD ' + string(pers_nr) AS gp,
COALESCE(preis, 0),
gueltig_ab
FROM preise p
WHERE typ = 30
AND pers_typ = 1
UNION ALL
SELECT
artnr,
'LF ' + string(pers_nr) AS gp,
COALESCE(preis, 0),
gueltig_ab
FROM preise p
WHERE typ = 30
AND pers_typ = 2;Derived Tables
Derived tables are cached results from a select, which can be used in the FROM clause. These will be executed only once, starting from inner layer executing one by one until reaching the outer select.
This technique allows you to shrink down your dataset very early, by nesting your most restricting WHERE attributes in the deepest layer of a complex multilevel select, so that it will be executed first.
SELECT
dt.kd_nr,
dt.kd_name,
ROUND(SUM(bp.zendpreis), 2) AS umsatz
FROM belpos bp
JOIN (SELECT
b.datum,
b.pers_nr AS kd_nr,
CAST(b.adr1name1 AS NVARCHAR(50)) AS kd_name,
b.typ,
b.nr,
b.status
FROM bel b
WHERE b.datum >= YEAR(today())
AND b.pers_typ = 1
AND b.typ IN (4,11)
) AS dt // End of DT
ON bp.bel_typ = dt.typ // Join attributes
AND bp.bel_nr = dt.nr
GROUP BY dt.kd_nr, dt.kd_name
ORDER BY dt.kd_nr;Common Table Expressions (CTEs)
Common table expressions allow you to execute helper selects before your main select fires. They can often be used for divide and conquer approaches to break down a complex multilevel select into an easy to read sequential structure.
CTEs are introduced by the WITH keyword and share a scope throughout the complete select. CTEs are also able to be executed recursively, to use this feature you need to use WITH RECURSIVE (…).
Since CTEs are executed prior your main select, you can also abuse them to define variables or manage your retrieval arguments, so that your select can be easily copied to DBISQL and filled with dummy data (replace static data with your retrieval arguments later):
WITH
var AS (SELECT 2 AS PersTyp, 71933 PersNr, '2017-01-01' AS dFrom, '2017-07-1' AS dUntil)
SELECT *
FROM bel b
CROSS JOIN var
WHERE b.pers_typ = var.PersTyp AND b.pers_nr = var.PersNr
AND b.datum BETWEEN var.dFrom AND var.dUntil;As another example I will transform the previous DT example into an equivalent CTE:
WITH
cte AS
(SELECT
b.datum,
b.pers_nr AS kd_nr,
CAST(b.adr1name1 AS NVARCHAR(50)) AS kd_name,
b.typ,
b.nr,
b.status
FROM bel b
WHERE b.datum >= YEAR(today())
AND b.pers_typ = 1
AND b.typ IN (4,11)
)
// Start of main select
SELECT
cte.kd_nr,
cte.kd_name,
ROUND(SUM(bp.zendpreis), 2) AS umsatz
FROM belpos bp
JOIN cte
ON bp.bel_typ = cte.typ
AND bp.bel_nr = cte.nr
GROUP BY cte.kd_nr, cte.kd_name
ORDER BY cte.kd_nr;As mentioned before, CTEs can also be executed recursively.
Sometimes it might be necessary to raise the recursion limit option (SET OPTION max_recursive_iterations = n; // default = 100) of the database to accomplish a specific task.
The following example demonstrates how you could realize the “Belegexplorer” purely in SQL:
WITH RECURSIVE // I use "p" as prefix for parent or previous and "c" for child or current
tree (p_typ, p_nr, p_pos, c_typ, c_nr, c_pos, c_level, c_path, artnr, bez, menge, uebernommen, offen)
AS (
// Rowgenerator for seed rows
SELECT
bp.aus_bel_typ,
bp.aus_bel_nr,
bp.aus_posnr,
bp.bel_typ AS c_typ,
bp.bel_nr AS c_nr,
bp.posnr AS c_pos,
1 AS c_level,
c_typ || '.' || c_nr || '-' || c_pos,
bp.art_nr,
bp.bez1,
bp.menge,
bp.uebernommen,
bp.menge - bp.uebernommen
FROM belpos bp
WHERE bp.aus_bel_nr IS NULL // only root element
AND bp.bel_typ = 22 // Belastungsanzeige
AND bp.bel_nr = 250001 // limit to one starting element, could be more
UNION ALL
SELECT
bp.aus_bel_typ,
bp.aus_bel_nr,
bp.aus_posnr,
bp.bel_typ,
bp.bel_nr,
bp.posnr,
p.c_level + 1, // level
p.c_path || ' > ' || bp.bel_typ || '.' || bp.bel_nr || '-' || bp.posnr, // path
bp.art_nr,
bp.bez1,
bp.menge,
bp.uebernommen,
bp.menge - bp.uebernommen AS offen
FROM belpos bp
JOIN tree p
ON p.c_typ = bp.aus_bel_typ
AND p.c_nr = bp.aus_bel_nr
AND p.c_pos = bp.aus_posnr
WHERE c_level < 30 // limit to cancel long iterations
)
SELECT *
FROM tree
ORDER by c_path;
As you can see in row 1 there is our seed row. Then position 10 was split into two resulting in rows 2 and 5. Rows 3 , 4, 6 and 7 are the following 1:1 takeover operations that have been applied to 2 and 5.
Window Functions
Generally you can use every aggregate functions which you know from group by also with a window function, but without the limiting factor, that all other rows have to be either aggregated or part of the group by clause.
In addition to the general aggregate functions you get functions like row_number and first_ / last value on top.
Window functions can generally be recognized by the OVER keyword, which describes over which part of the query you want to iterate per row. Their scope behaves like a “sliding Window” that moves over your rows per “partition”. The parameters for these windows can either be declared directly in each OVER clause or as a group using the WINDOW keyword, which can then be referenced in the OVER clause.
SELECT
lp.lager_nr, lp.datum, lp.art_nr, lp.buchungstyp, lp.buchungstext,
ROW_NUMBER() // Window function
OVER (PARTITION BY lp.art_nr
ORDER BY lp.datum ASC) lfdnr,
COALESCE(lp.bel_typ, lp.bab_typ) AS bel_or_bab_typ,
COALESCE(lp.bel_nr, lp.bab_nr) AS bel_or_bab_nr,
COALESCE(NULLIF(menge_ab,0)*-1, menge) AS normalized_menge,
SUM(normalized_menge) // Window function
OVER (PARTITION BY lp.art_nr
ORDER BY lp.datum ASC
ROWS BETWEEN UNBOUNDED PRECEDING // rows clause could be omitted in this case -> default
AND CURRENT ROW) AS current_menge
FROM lagerprot lp
WHERE art_nr = '110714304'
ORDER by lp.art_nr, lp.datum;
Cross / Outer Apply (Lateral Joins in SQL Standard)
CROSS APPLY and OUTER APPLY work like INNER JOIN and LEFT OUTER JOIN against derived tables with some additional features. CROSS/OUTER APPLY also works against expressions and procedures and is evaluated per row while sharing the same scope as the main select.
The resulting columns of the expression or select within the CROSS/OUTER APPLY are appended to your select and do not require an ON clause like explicit JOINs because of the shared scope.
This feature works exactly like a LATERAL join which is part of the SQL standard, while CROSS/OUTER APPLY is a Microsoft vendor extension, so it is recommended to use the LATERAL syntax instead.
Imagine we had the following helper function for date time calculations:
CREATE PROCEDURE p_dt(@dt DATETIME)
BEGIN
SELECT
DATE(@dt) Datum,
DATEFORMAT(@dt, 'hh:nn:ss') Uhrzeit,
YEAR(@dt) AS Jahr,
MONTH(@dt) AS Monat,
DATEFORMAT(@dt, 'yyyymm') Jahr_Monat,
DATEPART(CWK, @dt) AS KW,
DATEPART(CYR, @dt) AS KW_Jahr,
DATEPART(CDW, @dt) AS Tag,
DAYNAME(@dt) AS TagBez;
ENDThen we could easily append all those fields to any row that contains a DATE or DATETIME column:
SELECT
bp.bel_typ, bp.bel_nr, bp.posnr, bp.bez1, bp.menge, bp.einzelpr, bp.dispo_datum,
disp.*
FROM belpos bp
CROSS APPLY p_dt(bp.dispo_datum) disp;
OR
SELECT
bp.bel_typ, bp.bel_nr, bp.posnr, bp.bez1, bp.menge, bp.einzelpr, bp.dispo_datum,
disp.*
FROM belpos bp
,LATERAL(p_dt(bp.dispo_datum)) disp
Both selects return the same results and execute the same plan. Just be aware that even when the CROSS APPLY syntax seems to be a bit more intuitive, it is less portable.
Transactions and Isolation Levels (W.I.P)
This section is still work in progress. It just contains rudimentary information at the moment.
WITH Keyword allows to determine if a table should not be locked or ignored if locked.
SELECT a.artnr, a.bez1, a.suchwort, a.preiseinh, bp.*
FROM art a WITH (READPAST) // ignore all locked rows
,LATERAL (SELECT TOP 1
bp.bel_typ, bp.bel_nr, bp.posnr, bp.einzelpr AS letzterPreis
FROM belpos bp WITH (NOLOCK) // read only / read uncommitted
WHERE art_nr = a.artnr
ORDER BY bp.dispo_datum DESC) bp
WHERE a.artnr LIKE '300%';UPDATE (same applies for INSERT) statement with joins but not locking all referred columns:
UPDATE belpos
SET text = a.text
FROM belpos bp
JOIN art a WITH (NOLOCK) // Prevent the exclusive lock from update statement on art
ON a.artnr = bp.art_nr;Set isolation level per statement:
UPDATE belpos
SET text = a.text
FROM belpos bp
JOIN art a
ON a.artnr = bp.art_nr
OPTION (isolation_level = 3); // use serializable for this updateLast updated