Advanced SQL

Joins

Tutorial Join

Best practices / tips:

1. ALWAYS use explicit join syntax, no join conditions in the WHERE clause

2. Try to only use inner or left joins if possible, reorder joins if necessary to achieve this

3. User proper indentation in your selects to maintain readability and add comments

4. Start with the most limiting table in terms of “relevant for final result” (often the optimizer takes care of this)

5. Know your tables, check for usable indexes and be aware of their ORDER

6. 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

7. 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

8. Use table aliases for all tables with long names

9. 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

Union / Intersect / Except Tutorial

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 Tutorial

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 (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

Window Functions Tutorial

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 / Outer Apply (Lateral Joins in SQL Standard) Tutorial Table Expressions

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;
END

Then 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 update