Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Manipulation Language (DML) → SELECT
Firebird Home Firebird Home Zurück: Statements der Data Manipulation Language (DML)Firebird Documentation IndexNach oben: Statements der Data Manipulation Language (DML)Weiter: INSERT

SELECT

Inhaltsverzeichnis

FIRST, SKIP
Die SELECT-Spaltenliste
Die FROM-Klausel
Joins
Die WHERE-Klausel
Die GROUP BY-Klausel
Die PLAN-Klausel
UNION
ORDER BY
ROWS
FOR UPDATE [OF]
WITH LOCK
INTO
Common Table Expressions („WITH ... AS ... SELECT“)

Verwendet für: Retrieving data

Verfügbar in: DSQL, ESQL, PSQL

Globale Syntax: 

SELECT
[WITH [RECURSIVE] <cte> [, <cte> ...]]
SELECT
  [FIRST m] [SKIP n]
  [DISTINCT | ALL] <columns>
FROM
  source [[AS] alias]
  [<joins>]
[WHERE <condition>]
[GROUP BY <grouping-list>
[HAVING <aggregate-condition>]]
[PLAN <plan-expr>]
[UNION [DISTINCT | ALL] <other-select>]
[ORDER BY <ordering-list>]
[ROWS m [TO n]]
[FOR UPDATE [OF <columns>]]
[WITH LOCK]
[INTO <variables>]

<variables> ::= [:]varname [, [:]varname ...]

Beschreibung

Die Anweisung SELECT ruft Daten aus der Datenbank ab und übergibt sie an die Anwendung oder die umschließende SQL-Anweisung. Daten werden in null oder mehr Zeilen zurückgegeben, die jeweils eine oder mehrere Spalten oder Felder enthalten. Die Summe der zurückgegebenen Zeilen ist die Ergebnismenge der Anweisung.

Die einzigen obligatorischen Teile der Anweisung SELECT sind:

In der einfachsten Form ruft SELECT eine Anzahl von Spalten aus einer einzelnen Tabelle oder Sicht ab, wie folgt:

select id, name, address
  from contacts

Oder, um alle Spalten abzurufen:

select * from sales

In der Praxis werden die abgerufenen Zeilen oft durch eine Klausel WHERE begrenzt. Die Ergebnismenge kann nach einer ORDER BY-Klausel sortiert werden. FIRST, SKIP oder ROWS können die Anzahl der Ausgabezeilen eingrenzen. Die Spaltenliste kann alle Arten von Ausdrücken anstelle von nur Spaltennamen enthalten, und die Quelle muss keine Tabelle oder Sicht sein; sie kann auch eine abgeleitete Tabelle, ein allgemeiner Tabellenausdruck (CTE) oder eine auswählbare gespeicherte Prozedur (SP) sein. Mehrere Quellen können in einer JOIN kombiniert werden, und mehrere Ergebnismengen können in einer UNION kombiniert werden.

In den folgenden Abschnitten werden die verfügbaren SELECT Unterklauseln und ihre Verwendung im Detail erläutert.

FIRST, SKIP

Verwendet für:  Abrufen eines Teiles von Zeilen aus einer geordneten Menge

Verfügbar in: DSQL, PSQL

Syntax: 

SELECT
   [FIRST <m>] [SKIP <n>]
   FROM ...
   ...

<m>, <n>  ::=  integer-literal | query-parameter | (integer-expression)

Tabelle 6.1. Argumente für die FIRST- und SKIP-Klauseln

Argument Beschreibung
integer literal Ganzzahliges Literal
query parameter Abfrageparameter-Platzhalter. ? in DSQL und :paramname in PSQL
integer-expression Ausdruck, der einen Ganzzahlwert zurückgibt


FIRST und SKIP sind keine Standardsyntax

FIRST und SKIP sind Firebird-spezifische, nicht-SQL-konforme Schlüsselwörter. Es wird dringend empfohlen die ROWS-Syntax zu verwenden.

Beschreibung

FIRST begrenz die Ausgabe der Abfrage auf die ersten m Zeilen. SKIP übergeht die ersten n Zeilen, bevor mit der Ausgabe begonnen wird.

FIRST und SKIP sind optional. Bei Verwendung in „FIRST m SKIP n“ werden die obersten n Zeilen der Ausgabe verworfen und die ersten m Zeilen des Rests der Menge zurückgegeben.

Eigenschaften von FIRST und SKIP

  • Jedes Argument für FIRST und SKIP, das kein Integer-Literal oder ein SQL-Parameter ist, muss in Klammern stehen. Dies bedeutet, dass ein Unterabfrageausdruck in zwei Klammern eingeschlossen sein muss.
  • SKIP 0 ist erlaubt, jedoch vollkommen sinnlos.
  • FIRST 0 ist ebenfalls erlaubt und gibt eine leere Ergebnismenge zurück.
  • Negative Werte für SKIP und/oder FIRST resultieren in einem Fehler.
  • Wenn eine SKIP hinter dem Ende des Datensatzes landet, wird ein leerer Satz zurückgegeben.
  • Wenn die Anzahl der Zeilen im Datensatz (oder der Rest nach einem SKIP) kleiner als der Wert des m-Arguments für FIRST ist, wird die kleinere Anzahl von Zeilen zurückgegeben. Dies sind gültige Ergebnisse, keine Fehler.

Achtung

Wenn Sie FIRST in Unterabfragen verwenden, tritt ein Fehler auf. Diese Abfrage

DELETE FROM MYTABLE
  WHERE ID IN (SELECT FIRST 10 ID FROM MYTABLE)
            

löscht ALLE Datensätze aus der Tabelle. Die Unterabfrage ruft jedesmal 10 Zeilen ab, löscht sie und die Operation wird wiederholt, bis die Tabelle leer ist. Beachten Sie dies! Oder, besser, verwenden Sie die Klausel ROWS in der DELETE-Anweisung.

Beispiele

Die folgende Abfrage gibt die ersten 10 Namen aus der People-Tabelle zurück:

select first 10 id, name from People
  order by name asc

Die folgende Abfrage gibt alles zurück but the first 10 names:

select skip 10 id, name from People
  order by name asc

Und dieser gibt die letzten 10 Zeilen zurück. Beachten Sie die doppelten Klammern:

select skip ((select count(*) - 10 from People))
  id, name from People
  order by name asc

Diese Abfrage gibt die Zeilen 81 bis 100 der People-Tabelle zurück:

select first 20 skip 80 id, name from People
  order by name asc

Siehe auch:  ROWS

Die SELECT-Spaltenliste

Die Spaltenliste enthält einen oder mehrere durch Kommas getrennte Wertausdrücke. Jeder Ausdruck liefert einen Wert für eine Ausgabespalte. Alternativ kann * („select star“) verwendet werden, um für alle Spalten in einer Beziehung zu stehen (d. H. Für eine Tabelle, eine Ansicht oder eine auswählbare gespeicherte Prozedur).

Syntax: 

SELECT
   [...]
   [DISTINCT | ALL] <output-column> [, <output-column> ...]
   [...]
   FROM ...

<output-column>     ::=  [qualifier.]*
                           | <value-expression> [COLLATE collation] [[AS] alias]

<value-expression>  ::=  [qualifier.]table-column
                           | [qualifier.]view-column
                           | [qualifier.]selectable-SP-outparm
                           | constant
                           | context-variable
                           | function-call
                           | single-value-subselect
                           | CASE-construct
                           | „jeder andere Ausdruck, der einen einzelnen Wert eines Firebird-Datentyps 
                               oder NULL zurückgibt
                           qualifier           ::=  ein Beziehungsname oder ein Alias
collation           ::=  ein gültiger Collations-Name (nur für Zeichentypspalten)

Tabelle 6.2. Argumente der SELECT-Spaltenliste

Argument Beschreibung
qualifier Name der Beziehung (Sicht, gespeicherte Prozedur, abgeleitete Tabelle); oder ein Alias dafür
collation Nur für zeichenartige Spalten: Ein Collations-Name der für den Zeichensatz der Daten existiert und gültig ist
alias Spalten- oder Feldalias
table-column Name einer Tabellenspalte
view-column Name einer Ansichtsspalte
selectable-SP-outparm Deklarierter Name eines Ausgabeparameters einer auswählbaren gespeicherten Prozedur
constant Eine Konstante
context-variable Kontextvariable
function-call Skalarer oder Aggregatfunktionsaufrufausdruck
single-value-subselect Eine Unterabfrage, die einen Skalarwert zurückgibt (Singleton)
CASE-construct CASE-Konstrukt, dass Bedingungen für einen Rückgabewert definiert
other-single-value-expr Jeder andere Ausdruck, der einen einzelnen Wert eines Firebird-Datentyps zurückgibt; oder NULL


Beschreibung

Es ist immer gültig, einen Spaltennamen zu qualifizieren (oder „*“). Dies geschieht mit dem Namen oder Alias der Tabelle, Ansicht oder abfragbaren gespeicherten Prozedur, gefolgt von einem Punkt, z.B. relationname.columnname, relationname.*, alias.columnname, alias.*. Qualifizierend ist required, wenn der Spaltenname in mehr als einer Relation auftritt, die an einem Join beteiligt ist. Qualifizierendes „ * “ ist immer obligatorisch, wenn es nicht das einzige Element in der Spaltenliste ist.

Wichtig

Aliase verschleiern den ursprünglichen Beziehungsnamen: Sobald eine Tabelle, eine Sicht oder eine Prozedur mit einem Alias versehen wurde, kann nur der Alias als Qualifikationsmerkmal für die gesamte Abfrage verwendet werden. Der Beziehungsname selbst ist nicht mehr verfügbar.

Der Spaltenliste kann optional eines der Schlüsselwörter DISTINCT or ALL vorangestellt werden:

  • DISTINCT filtert alle doppelten Zeilen aus. Das heißt, wenn zwei oder mehr Zeilen die gleichen Werte in jeder entsprechenden Spalte haben, ist nur einer von ihnen in der Ergebnismenge enthalten
  • ALL ist der Standard: es gibt alle Zeilen zurück, einschließlich Duplikate. ALL wird selten verwendet; Es wird für die Einhaltung des SQL-Standards unterstützt.

Eine Klausel COLLATE ändert das Erscheinungsbild der Spalte als solche nicht. Wenn die angegebene Sortierung jedoch die Groß- / Kleinschreibung der Spalte ändert, kann dies folgende Auswirkungen haben:

  • Die Reihenfolge, wenn eine Klausel ORDER BY ebenfalls vorhanden ist und diese Spalte umfasst

  • Gruppierung, wenn die Spalte Teil einer Klausel GROUP BY ist

  • Die abgerufenen Zeilen (und damit die Gesamtzahl der Zeilen in der Ergebnismenge), wenn DISTINCT verwendet wird

Beispiele für SELECT-Abfragen mit verschiedenen Arten von Spaltenlisten

Ein einfaches SELECT, das nur Spaltennamen verwendet:

select cust_id, cust_name, phone
  from customers
  where city = 'London'

Eine Abfrage mit einem Verkettungsausdruck und einem Funktionsaufruf in der Spaltenliste:

select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
  from contacts
  where date_last_purchase(id) = current_date

Eine Abfrage mit zwei Unterabfragen

select p.fullname,
       (select name from classes c where c.id = p.class) as class,
       (select name from mentors m where m.id = p.mentor) as mentor
from pupils p

Die folgende Abfrage führt dasselbe wie das vorherige mit Joins statt Unterabfragen durch:

select p.fullname,
       c.name as class,
       m.name as mentor
from pupils p
  join classes c on c.id = p.class
  join mentors m on m.id = p.mentor

Diese Abfrage verwendet ein CASE-Konstrukt, um die korrekte Anrede zu ermitteln, z.B. für das Senden von E-Mails an eine Person:

select case upper(sex)
         when 'F' then 'Mrs.'
         when 'M' then 'Mr.'
         else ''
       end as title,
       lastname,
       address
from employees

Abfrage einer auswählbaren gespeicherten Prozedur:

select * from interesting_transactions(2010, 3, 'S')
  order by amount

Auswahl aus Spalten einer abgeleiteten Tabelle. Eine abgeleitete Tabelle ist eine eingeklammerte SELECT-Anweisung, deren Ergebnismenge in einer einschließenden Abfrage so verwendet wird, als wäre sie eine reguläre Tabelle oder Sicht. Die abgeleitete Tabelle ist hier fett dargestellt:

select fieldcount,
       count(relation) as num_tables
from   (select r.rdb$relation_name as relation,
               count(*) as fieldcount
        from   rdb$relations r
               join rdb$relation_fields rf
                 on rf.rdb$relation_name = r.rdb$relation_name
        group by relation)
group by fieldcount

Die Zeit durch eine Kontextvariable abfragen (CURRENT_TIME):

select current_time from rdb$database

Für diejenigen, die mit RDB$DATABASE nicht vertraut sind: Dies ist eine Systemtabelle, die in allen Firebird-Datenbanken vorhanden ist und nur genau eine Zeile enthält. Obwohl es für diesen Zweck nicht erstellt wurde, ist es unter Firebird-Programmierern Standard geworden, diese Tabelle abzufragen, wenn Sie „aus nichts“ abfragen möchten, d.h. wenn Sie Daten benötigen, die nicht an eine Tabelle oder Ansicht gebunden sind, diese aber über Ausdrücke in den Ausgabespalten abgeleitet werden können. Ein anderes Beispiel ist:

select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
  from rdb$database

Zum Schluss ein Beispiel, in dem Sie aussagekräftige Informationen aus RDB$DATABASE selbst ermitteln:

select rdb$character_set_name from rdb$database

Wie Sie vielleicht schon vermutet haben, erhalten Sie den Standardzeichensatz der Datenbank.

Siehe auch:  Skalarfunktionen, Aggregatfunktionen, Kontextvariablen, CASE, Unterabfragen

Die FROM-Klausel

Die Klausel FROM gibt die Quelle (n) an, aus der die Daten abgerufen werden sollen. In seiner einfachsten Form ist dies nur eine einzelne Tabelle oder Ansicht. Die Quelle kann jedoch auch eine auswählbare gespeicherte Prozedur, eine abgeleitete Tabelle oder ein allgemeiner Tabellenausdruck sein. Mehrere Quellen können mit verschiedenen Arten von Joins kombiniert werden.

Dieser Abschnitt konzentriert sich auf Single-Source-Selects. Joins werden in einem der folgenden Abschnitte behandelt.

Syntax: 

SELECT
   ...
   FROM <source>
   [<joins>]
   [...]

<source>          ::=  {table
                          | view
                          | selectable-stored-procedure [(args)]
                          | <derived-table>
                          | <common-table-expression>}
                       [[AS] alias]

<derived-table>   ::=  (select-statement) [[AS] alias]
                         [(<column-aliases>)]

<common-table-expression>
                  ::=  WITH [RECURSIVE] <cte-def> [, <cte-def> ...]
                       select-statement

<cte-def>         ::=  name [(<column-aliases>)] AS (select-statement)

<column-aliases>  ::=  column-alias [, column-alias ...]

Tabelle 6.3. Argumente der FROM-Klausel

Argument Beschreibung
table Name einer Tabelle
view Name einer Ansicht
selectable-stored-procedure Name einer auswählbaren gespeicherten Prozedur
args Auswählbare Argumente für gespeicherte Prozeduren
derived table Abgeleiteter Tabellenabfrageausdruck
cte-def Definition des gemeinsamen Tabellenausdrucks (Common Table Expression, CTE), einschließlich eines „ad hoc“-Namens
select-statement Beliebige SELECT-Anweisung
column-aliases Alias für eine Spalte in einer Relation, CTE oder abgeleitete Tabelle
name Der „ad hoc“-Name für eine CTE
alias Der Alias einer Datenquelle (Tabelle, View, Prozedur, CTE, abgeleitete Tabelle)


Abfragen einer Tabelle oder Ansicht mit FROM

Bei der Auswahl aus einer einzelnen Tabelle oder Sicht muss die FROM-Klausel nichts mehr als den Namen enthalten. Ein Alias kann nützlich oder sogar notwendig sein, wenn es Unterabfragen gibt, die auf die Haupt-Select-Anweisung verweisen (wie sie es sooft tun — Unterabfragen wie diese werden auch korrelierte Unterabfragen genannt).

Beispiele

select id, name, sex, age from actors
  where state = 'Ohio'
select * from birds
  where type = 'flightless'
  order by family, genus, species
select firstname,
       middlename,
       lastname,
       date_of_birth,
       (select name from schools s where p.school = s.id) schoolname
from pupils p
where year_started = '2012'
order by schoolname, date_of_birth

Mischen Sie niemals Spaltennamen mit Spaltenaliasnamen!

Wenn Sie einen Alias für eine Tabelle oder eine Sicht angeben, müssen Sie diesen Alias anstelle des Tabellennamens immer verwenden, wenn Sie die Spalten der Relation abfragen (und wo auch immer Sie auf Spalten verweisen, z. B. ORDER BY, GROUP BY) und WHERE-Klauseln.

Richtige Verwendung:

SELECT PEARS
FROM FRUIT

SELECT FRUIT.PEARS
FROM FRUIT

SELECT PEARS
FROM FRUIT F

SELECT F.PEARS
FROM FRUIT F
            

Falsche Verwendung:

SELECT FRUIT.PEARS
FROM FRUIT F
            

Abfragen einer gespeicherten Prozedur mit FROM

Eine auswählbare gespeicherte Prozedur ist eine Prozedur, die:

  • enthält mindestens einen Ausgabeparameter und

  • das Schlüsselwort SUSPEND verwendet, damit der Aufrufer die Ausgabezeilen nacheinander abrufen kann, genau so wie bei der Auswahl aus einer Tabelle oder Ansicht.

Die Ausgabeparameter einer auswählbaren gespeicherten Prozedur entsprechen den Spalten einer regulären Tabelle.

Die Abfrage aus einer gespeicherten Prozedur ohne Eingabeparameter entspricht der Abfrage aus einer Tabelle oder Ansicht:

select * from suspicious_transactions
  where assignee = 'John'

Alle erforderlichen Eingabeparameter müssen nach dem in Klammern angegebenen Prozedurnamen angegeben werden:

select name, az, alt from visible_stars('Brugge', current_date, '22:30')
  where alt >= 20
  order by az, alt

Werte für optionale Parameter (d.h. Parameter, für die Standardwerte definiert wurden) können weggelassen oder bereitgestellt werden. Wenn Sie diese jedoch nur teilweise angeben, müssen die Parameter, die Sie weglassen, alle am Ende stehen.

Angenommen, die Prozedur visible_stars aus dem vorherigen Beispiel hat zwei optionale Parameter: min_magn (numerisch (3,1)) und spectral_class (varchar (12 )). Die folgenden Abfragen sind alle gültig:

select name, az, alt from visible_stars('Brugge', current_date, '22:30')
select name, az, alt from visible_stars('Brugge', current_date, '22:30', 4.0)
select name, az, alt from visible_stars('Brugge', current_date, '22:30', 4.0, 'G')

Diese jedoch nicht, da es ein „Loch“ in der Parameterliste gibt:

select name, az, alt from visible_stars('Brugge', current_date, '22:30', 'G')

Ein Alias für eine auswählbare gespeicherte Prozedur wird nach der Parameterliste angegeben:

select number,
       (select name from contestants c where c.number = gw.number)
from get_winners('#34517', 'AMS') gw

Wenn Sie auf einen Ausgabeparameter („column“) verweisen, indem Sie ihn mit dem vollständigen Prozedurnamen qualifizieren, sollte die Parameterliste weggelassen werden:

select number,
       (select name from contestants c where c.number = get_winners.number)
from get_winners('#34517', 'AMS')

Siehe auch:  Gespeicherte Prozeduren, CREATE PROCEDURE

Abfragen aus einer abgeleiteten Tabelle mittels FROM

Eine abgeleitete Tabelle ist eine gültige SELECT-Anweisung, die in Klammern eingeschlossen ist, optional gefolgt von einem Tabellenalias und / oder Spaltenaliasnamen. Die Ergebnismenge der Anweisung fungiert als virtuelle Tabelle, die die umschließende Anweisung abfragen kann.

Syntax: 

(select-query)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]
          

Die von diesem „SELECT FROM(SELECT FROM ...)“-Stil der Anweisung zurückgegebene Datenmenge ist eine virtuelle Tabelle, die innerhalb der umschließenden Anweisung abgefragt werden kann, als wäre sie eine normale Tabelle oder Ansicht.

Beispiel mit einer abgeleiteten Tabelle

Die abgeleitete Tabelle in der folgenden Abfrage gibt die Liste der Tabellennamen in der Datenbank und die Anzahl der Spalten in jeder Datenbank zurück. Eine „Drill-Down“-Abfrage für die abgeleitete Tabelle gibt die Anzahl der Felder und die Anzahl der Tabellen mit jeder Feldanzahl zurück:

SELECT
    FIELDCOUNT,
    COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
           R.RDB$RELATION_NAME RELATION,
           COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
           JOIN RDB$RELATION_FIELDS RF
           ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
           GROUP BY RELATION)
GROUP BY FIELDCOUNT
          

Ein triviales Beispiel, das demonstriert, wie der Alias einer abgeleiteten Tabelle und die Liste der Spaltenaliase (beide optional) verwendet werden können:

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO
        (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)
          

Mehr über abgeleitete Tabellen

Abgeleitete Tabellen können

  • verschachtelt werden
  • Unions sein und in Unions verwendet werden
  • Aggregatfunktionen, Unterabfragen und Joins enthalten
  • in Aggregatfunktionen, Unterabfragen und Joins verwendet werden
  • Aufrufe an abfragbare gespeicherte Prozeduren oder Abfragen auf diese sein
  • WHERE-, ORDER BY- und GROUP BY-Klauseln, FIRST, SKIP- oder ROWS-Direktiven, usw enthalten.

Weiter gilt:

  • Jede Spalte in einer abgeleiteten Tabelle muss einen Namen haben. Wenn sie keinen Namen hat, z. B. wenn es sich um einen Konstanten- oder einen Laufzeitausdruck handelt, sollte ihr ein Alias zugewiesen werden, entweder auf reguläre Weise oder durch einfügen in die Liste der Spaltenaliase in der Spezifikation der abgeleiteten Tabelle.
    • Die Liste der Spaltenaliase ist optional, aber falls vorhanden, muss sie einen Alias für jede Spalte in der abgeleiteten Tabelle enthalten
  • Der Optimierer kann abgeleitete Tabellen sehr effektiv verarbeiten. Wenn eine abgeleitete Tabelle jedoch in einem Inner Join enthalten ist und eine Unterabfrage enthält, kann der Optimierer keine Join-Reihenfolge verwenden.

Ein nützlicheres Beispiel

Angenommen, wir haben eine Tabelle COEFFS, die die Koeffizienten einer Anzahl von quadratischen Gleichungen enthält, die wir lösen müssen. Diese wurde folgendermaßen definiert:

create table coeffs (
  a double precision not null,
  b double precision not null,
  c double precision not null,
  constraint chk_a_not_zero check (a <> 0)
)

Abhängig von den Werten für a, b und c kann jede Gleichung null, eine oder zwei Lösungen haben. Es ist möglich, diese Lösungen mit einer einstufigen Abfrage für die Tabelle COEFFS zu finden, aber der Code sieht ziemlich unordentlich aus und mehrere Werte (wie die Diskriminante) müssen mehrmals pro Zeile berechnet werden. Eine abgeleitete Tabelle kann dabei helfen, die Dinge sauber zu halten:

select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

Wenn wir die Koeffizienten neben den Lösungen anzeigen möchten (was keine schlechte Idee ist), können wir die Abfrage folgendermaßen ändern:

select
  a, b, c,
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select a, b, c, b*b - 4*a*c as D, 2*a as denom
     from coeffs)

Beachten Sie, dass, während die erste Abfrage eine Liste mit Spaltenaliasen für die abgeleitete Tabelle verwendet, nutzt die zweite Abfrage intern hinzugefügte Alias, wo diese benötigt werden. Beide Methoden funktionieren, solange jede Spalte einen Namen hat.

Abfragen einer CTE mittels FROM

Ein allgemeiner Tabellenausdruck (Common Table Expression) oder CTE ist eine komplexere Variante der abgeleiteten Tabelle, aber auch leistungsfähiger. Eine Präambel, beginnend mit dem Schlüsselwort WITH, definiert einen oder mehrere benannte CTE mit jeweils einer optionalen Spalten-Alias-Liste. Die Hauptabfrage, die der Präambel folgt, kann dann auf diese CTE wie normale Tabellen oder Ansichten zugreifen. Sobald die Hauptabfrage ausgeführt wurde, werden die CTEs nicht mehr betrachtet.

Für eine vollständige Beschreibung der CTEs, beachten Sie bitte den Abschnitt Common Table Expressions („WITH ... AS ... SELECT“).

Das folgende ist eine andere Variante unseres abgeleiteten Tabellenbeispiels als CTE:

with vars (b, D, denom) as (
  select b, b*b - 4*a*c, 2*a from coeffs
)
select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
from vars

Abgesehen von der Tatsache, dass die Berechnungen, die zuerst gemacht werden müssen, jetzt am Anfang stehen, ist dies keine große Verbesserung gegenüber der abgeleiteten Tabellenversion. Aber wir können jetzt auch die doppelte Berechnung von sqrt (D) für jede Zeile eliminieren:

with vars (b, D, denom) as (
  select b, b*b - 4*a*c, 2*a from coeffs
),
vars2 (b, D, denom, sqrtD) as (
  select b, D, denom, iif (D >= 0, sqrt(D), null) from vars
)
select
  iif (D >= 0, (-b - sqrtD) / denom, null) sol_1,
  iif (D >  0, (-b + sqrtD) / denom, null) sol_2
from vars2

Der Code ist jetzt etwas komplizierter, könnte aber effizienter ausgeführt werden (abhängig davon, was mehr Zeit benötigt: die Ausführung der Funktion SQRT oder die Übergabe der Werte von b, D und denom durch eine weitere CTE). Übrigens hätten wir das Gleiche mit abgeleiteten Tabellen tun können, aber das würde Verschachtelung bedeuten.

Siehe auch: Common Table Expressions („WITH ... AS ... SELECT“).

Joins

Joins kombinieren Daten aus zwei Quellen zu einem einzelnen Satz. Dies wird durch einen Zeile-für-Zeilen-Vergleich durchgeführt und beinhaltet üblicherweise eine Join-Bedingung, um festzulegen welche Zeilen zusammengeführt werden sollen und im Ergebnisdatensatz erscheinen sollen. Es gibt unterschiedliche Arten (INNER, OUTER) und Klassen (qualifiziert, natürlich, etc.), jede mit eigener Syntax und Regeln.

Da Joins verkettet werden können, können die an einem Join beteiligten Datensätze selbst verbundene Sets sein.

Syntax: 

SELECT
   ...
   FROM <source>
   [<joins>]
   [...]

<source>          ::=  {table
                          | view
                          | selectable-stored-procedure [(args)]
                          | derived-table
                          | common-table-expression}
                       [[AS] alias]

<joins>           ::=  <join> [<join> ...]

<join>            ::=  [<join-type>] JOIN <source> <join-condition>
                         | NATURAL [<join-type>] JOIN <source>
                         | {CROSS JOIN | ,} <source>

<join-type>       ::=  INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition>  ::=  ON condition | USING (column-list)

Tabelle 6.4. Argumente für JOIN-Klauseln

Argument Beschreibung
table Name einer Tabelle
view Name einer Ansicht
selectable-stored-procedure Name einer auswählbaren gespeicherten Prozedur
args Wählbare gespeicherte Prozedur-Eingangsparameter
derived-table Referenz, namentlich, auf eine abgeleitete Tabelle
common-table-expression Verweis auf einen gemeinsamen Tabellenausdruck (CTE)
alias Ein Alias für eine Datenquelle (Tabelle, View, Prozedur, CTE, abgeleitete Tabelle)
condition Join-Bedingung (Kriterium)
column-list Die Liste der Spalten, die für einen Equi-Join verwendet werden


Inner vs. Outer Joins

Ein Join kombiniert immer Datenzeilen aus zwei Mengen (normalerweise als die linke Menge und die rechte Menge bezeichnet). Standardmäßig werden nur Zeilen in die Ergebnismenge aufgenommen, die die Join-Bedingung erfüllen (d.h. wenn bei der Join-Bedingung mindestens eine Zeile in der anderen Gruppe übereinstimmt). Dieser Standardtyp von Join wird als Inner Join bezeichnet. Angenommen, wir haben die folgenden zwei Tabellen:

Tabelle A:

ID S
87 Just some text
235 Silence

Tabelle B:

CODE X
-23 56.7735
87 416.0

Wenn wir diese Tabellen wie folgt verbinden:

select *
  from A
  join B on A.id = B.code

then the result set will be:

ID S CODE X
87 Just some text 87 416.0

Die erste Zeile von A wurde mit der zweiten Zeile von B verbunden, weil sie zusammen die Bedingung „A.id = B.code“ erfüllten. Die anderen Zeilen aus den Quellentabellen haben keine Übereinstimmung in der entgegengesetzten Menge und sind daher nicht in der Verknüpfung enthalten. Denken Sie daran, dies ist ein INNER Join. Wir können diese Tatsache explizit machen, indem wir schreiben:

select *
  from A
  inner join B on A.id = B.code

Da jedoch <datenbank>INNER</datenbank> die Standardeinstellung ist, wird dies selten durchgeführt.

Es ist durchaus möglich, dass eine Zeile im linken Satz mit mehreren Zeilen vom rechten Satz übereinstimmt oder umgekehrt. In diesem Fall sind alle diese Kombinationen enthalten und wir können Ergebnisse erhalten wie:

ID S CODE X
87 Just some text 87 416.0
87 Just some text 87 -1.0
-23 Don't know -23 56.7735
-23 Still don't know -23 56.7735
-23 I give up -23 56.7735

Manchmal möchten (oder brauchen) alle die Zeilen einer oder beider Quellen in der verbundenen Menge erscheinen, unabhängig davon, ob sie mit einem Datensatz in der anderen Quelle übereinstimmen. An dieser Stelle kommen Outer Joins ins Spiel. Ein Outer Join LEFT enthält alle Datensätze aus dem linken Satz, aber nur übereinstimmende Datensätze aus dem richtigen Satz. In einem RIGHT Outer Join ist es umgekehrt. FULL Outer Joins umfassen alle Datensätze aus beiden Sets. In allen äußeren Joins sind die „Löcher“ (die Stellen, an denen ein eingeschlossener Quelldatensatz keine Übereinstimmung in der anderen Menge hat) mit NULL gefüllt.

Um einen Outer Join zu erstellen, müssen Sie LEFT, RIGHT oder FULL angeben, optional vom Schlüsselwort OUTER gefolgt.

Im Folgenden sind die Ergebnisse der verschiedenen äußeren Joins aufgeführt, wenn sie auf unsere ursprünglichen Tabellen A und B angewendet werden:

select *
  from A
  left [outer] join B on A.id = B.code

ID S CODE X
87 Just some text 87 416.0
235 Silence <null> <null>

select *
  from A
  right [outer] join B on A.id = B.code

ID S CODE X
<null> <null> -23 56.7735
87 Just some text 87 416.0

select *
  from A
  full [outer] join B on A.id = B.code

ID S CODE X
<null> <null> -23 56.7735
87 Just some text 87 416.0
235 Silence <null> <null>

Qualifizierte Joins

Qualifizierte Joins geben Bedingungen für das Kombinieren von Zeilen an. Dies geschieht entweder explizit in einer ON-Klausel oder implizit in einer USING-Klausel.

Syntax: 

<qualified-join>  ::=  [<join-type>] JOIN <source> <join-condition>

<join-type>       ::=  INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition>  ::=  ON condition | USING (column-list)
Joins mit expliziter Bedingung

Die meisten qualifizierten Joins haben eine Klausel ON mit einer expliziten Bedingung, bei der es sich um einen beliebigen gültigen booleschen Ausdruck handeln kann, der jedoch normalerweise einen Vergleich zwischen den beiden beteiligten Quellen beinhaltet.

Häufig ist die Bedingung ein Gleichheitstest (oder eine Anzahl von AND-verknüpften Gleichheitstests) unter Verwendung des Operators „=“. Joins wie diese heißen Equi-Joins . (Die Beispiele im Abschnitt über innere und äußere Verknüpfung waren Equi-Joins.)

Beispiele für Joins mit expliziter Bedingung:

/* Wählen Sie alle Detroit-Kunden aus, die 2013 einen 
                Kauf getätigt haben, zusammen mit den Kaufdetails: */
select * from customers c
  join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013
/* Dasselbe wie oben, aber auch nicht kaufende Kunden: */
select * from customers c
  left join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013
/* Wähle für jeden Mann die Frauen aus, die größer sind als er. 
                Männer, für die keine solche Frau existiert, sind nicht enthalten. */
select m.fullname as man, f.fullname as woman
  from males m
  join females f on f.height > m.height
/* Wähle alle Schüler mit ihrer Klasse und ihrem Mentor aus. 
                Schüler ohne Mentor sind ebenfalls enthalten. Schüler ohne 
                Klasse sind nicht enthalten. */
select p.firstname, p.middlename, p.lastname,
       c.name, m.name
  from pupils p
  join classes c on c.id = p.class
  left join mentors m on m.id = p.mentor
Joins für benannte Spalten

Equi-Joins vergleichen häufig Spalten, die in beiden Tabellen denselben Namen haben. Wenn dies der Fall ist, können wir auch den zweiten Typ von qualifiziertem Join verwenden: die Joins für benannte Spalten.

Anmerkung

Joins für benannte Spalten werden in Dialekt 1 nicht unterstützt.

Joins für benannte Spalten besitzen eine USING-Klausel, welche nur die Spaltennamen enthält. Anstelle dieser Variante:

select * from flotsam f
  join jetsam j
  on f.sea = j.sea
  and f.ship = j.ship

können wir auch diese schreiben:

select * from flotsam
  join jetsam using (sea, ship)

welche deutlich kürzer ist. Der Ergebnissatz ist etwas andersThe result set is a little—zumindest bei der Verwendung von „SELECT *“:

  • Der Join mit expliziter Bedingung—mit der ON-Klausel— wird jede der Spalten SEA und SHIP zweimal enthalten: einmal für Tabelle FLOTSAM und einmal für Tabelle JETSAM. Offensichtlich werden sie die gleichen Werte haben.

  • Der Join für benannte Spalten—mit der USING-Klausel—enthält diese Spalten nur einmal.

Wenn Sie alle Spalten in der Ergebnismenge der benannten Spalten verknüpfen möchten, richten Sie Ihre Abfrage wie folgt ein:

select f.*, j.*
  from flotsam f
  join jetsam j using (sea, ship)

Dadurch erhalten Sie genau das gleiche Ergebnis wie beim Join der expliziten Bedingung.

Für einen Join mit benannten Spalte vom Typ OUTER gibt es eine zusätzliche Wendung, wenn „SELECT * “ oder ein nicht qualifizierter Spaltenname aus der USING-Liste verwendet wird:

Wenn eine Zeile aus einer Quellgruppe keine Übereinstimmung in der anderen enthält, muss sie dennoch aufgrund der LEFT-, RIGHT- oder FULL-Direktive enthalten sein. Die zusammengeführte Spalte im zusammengeführten Satz erhält den Wert nicht-NULL. Das ist soweit gut, aber jetzt können Sie nicht sagen, ob dieser Wert aus der linken, rechten oder beiden Mengen stammt. Dies kann besonders trügerisch sein, wenn der Wert von der rechten Seite stammt, weil „*“ immer kombinierte Spalten im linken Teil zeigt—selbst im Falle eines RIGHT Join.

Ob dies ein Problem ist oder nicht, hängt von der Situation ab. Wenn ja, benutzen Sie die „ a.*-, b.*“-Ansatz wie oben gezeigt, mit a und b als Namen oder Alias der beiden Quellen. Oder noch besser, vermeiden Sie „*“ insgesamt in Ihren seriösen Abfragen und qualifizieren Sie alle Spaltennamen in verbundenen Mengen. Dies hat den zusätzlichen Vorteil, dass Sie gezwungen sind, darüber nachzudenken, welche Daten Sie abrufen möchten und woher.

Es liegt in Ihrer Verantwortung sicherzustellen, dass die Spaltennamen in der USING-Liste kompatible Typen zwischen den beiden Quellen sind. Wenn die Typen kompatibel aber nicht gleich sind, konvertiert die Engine sie in den Typ mit dem breitesten Wertebereich, bevor sie die Werte vergleicht. Dies ist auch der Datentyp der zusammengeführten Spalte, die in der Ergebnismenge angezeigt wird, wenn „SELECT * “ oder der nicht qualifizierte Spaltenname verwendet wird. Qualifizierte Spalten behalten ihren ursprünglichen Datentyp immer bei.

Natürliche Joins

Greift man die Idee der benannten Spalten auf und geht noch einen Schritt weiter, führt ein natürlicher Join einen automatischen Equi-Join für alle Spalten durch, die in der linken und rechten Tabelle den gleichen Namen haben. Die Datentypen dieser Spalten müssen kompatibel sein.

Anmerkung

Natürliche Joins werden in Dialekt 1-Datenbanken nicht unterstützt.

Syntax: 

<natural-join>  ::=  NATURAL [<join-type>] JOIN <source>

<join-type>     ::=  INNER | {LEFT | RIGHT | FULL} [OUTER]

Gegeben sind diese beiden Tabellen

create table TA (
  a bigint,
  s varchar(12),
  ins_date date
)
create table TB (
  a bigint,
  descr varchar(12),
  x float,
  ins_date date
)

Ein natürlicher Join auf TA und TB würde die Spalten a und ins_date einbeziehen, und die folgenden zwei Anweisungen würden die gleiche Wirkung haben:

select * from TA
  natural join TB
select * from TA
  join TB using (a, ins_date)

Wie alle Joins sind natürliche Joins standardmäßig innere Joins, die Sie jedoch durch Angabe von LEFT, RIGHT oder FULL vor dem Schlüsselwort JOIN in äußere Joins umwandeln können.

Vorsicht: Wenn in den beiden Quellbeziehungen keine Spalten mit demselben Namen vorhanden sind, wird ein CROSS JOIN ausgeführt. Wir kommen in einer Minute zu dieser Art von Join.

Eine Anmerkung zur Gleichheit

Wichtig

Diese Notiz über Gleichheits- und Ungleichheitsoperatoren gilt überall in der Firebird SQL-Sprache, nicht nur unter JOIN-Bedingungen.

Der Operator „=“, welcher explizit für diverse bedingte Joins und imlizit in Joins mit benannten Spalten und natürlichen Joins verwendet wird, vergleicht nur Werte mit Werten. Nach dem SQL-Standard gilt, dass NULL kein Wert ist und somit zwei NULLen wedet identisch noch unidentisch zueinander sind. Wenn Sie NULL benötigen, um in einem Join übereinzustimmen, verwenden Sie den Operator IS NOT DISTINCT FROM. Dieser Operator gibt "true" zurück, wenn die Operanden denselben Wert haben oder wenn sie beide NULL sind.

select *
  from A join B
  on A.id is not distinct from B.code

In den —extrem seltenen—Fällen, in denen Sie im Join auf die in-Gleichheit prüfen möchsten, verwenden Sie IS DISTINCT FROM, nicht „<>“, falls Sie NULL von anderen Werten unterscheiden müssen und zwei NULLen als gleich betrachtet werden sollen:

select *
  from A join B
  on A.id is distinct from B.code

Cross Joins

Ein Cross Join erzeugt das vollständige Set-Produkt der beiden Datenquellen. Dies bedeutet, dass jede Zeile in der linken Quelle mit jeder Zeile in der rechten Quelle übereinstimmt.

Syntax: 

<cross-join>  ::=  {CROSS JOIN | ,} <source>

Bitte beachten Sie, dass die Kommasyntax veraltet ist! Es wird nur unterstützt, um Legacy-Code zu erhalten, und wird möglicherweise in einer zukünftigen Version verschwinden.

Das Zusammenführen von zwei Sätzen ist gleichbedeutend damit, dass sie sich einer Tautologie anschließen (eine Bedingung, die immer wahr ist). Die folgenden beiden Aussagen haben den gleichen Effekt:

select * from TA
  cross join TB
select * from TA
  join TB on 1 = 1

Cross Joins sind innere Joins, da sie nur übereinstimmende Datensätze enthalten - dies ergibt sich daraus, dass jeder-Eintrag übereinstimmt! Ein äußerer Cross Join würde, falls vorhanden, dem Ergebnis nichts hinzufügen, weil die äußeren Joins keine übereinstimmenden Datensätze sind und diese nicht in Cross Joins existieren.

Cross Joins sind selten nützlich, außer wenn Sie alle möglichen Kombinationen von zwei oder mehr Variablen auflisten möchten. Angenommen, Sie verkaufen ein Produkt in verschiedenen Größen, Farben und Materialien. Wenn diese Variablen jeweils in einer eigenen Tabelle aufgeführt sind, gibt diese Abfrage alle Kombinationen zurück:

select m.name, s.size, c.name
  from materials m
  cross join sizes s
  cross join colors c

Mehrdeutige Feldnamen in Joins

Firebird weist unqualifizierte Feldnamen in einer Abfrage zurück, wenn diese Feldnamen in mehr als einem Datensatz vorhanden sind, der an einem Join beteiligt ist. Dies gilt sogar für innere Equi-Joins, bei denen der Feldname in der ON-Klausel so aussieht:

select a, b, c
  from TA
  join TB on TA.a = TB.a

Es gibt eine Ausnahme zu dieser Regel: Bei Joins mit benannten Spalten und natürlichen Joins kann der nicht qualifizierte Feldname einer Spalte, die am Matching-Prozess teilnimmt, legal verwendet werden und verweist auf die zusammengeführte Spalte mit demselben Namen. Für Joins mit benannten Spalten sind dies die in der USING-Klausel aufgelisteten Spalten. Bei natürlichen Joins sind dies die Spalten, die in beiden Relationen denselben Namen haben. Aber bitte beachten Sie noch einmal, dass insbesondere in Outer Joins der reine Spaltenname nicht immer dasselbe ist wie links.Spaltenname oder rechts.Spaltenname. Die Typen können sich unterscheiden, und eine der qualifizierten Spalten kann NULL sein, während die andere nicht. In diesem Fall kann der Wert in der zusammengeführten, nicht qualifizierten Spalte die Tatsache maskieren, dass einer der Quellwerte nicht vorhanden ist.

Joins mit gespeicherten Prozeduren

Wenn eine Verknüpfung mit einer gespeicherten Prozedur durchgeführt wird, die nicht über Eingabeparameter mit anderen Datenströmen korreliert, gibt es keine Kuriositäten. Wenn es Korrelationen gibt, zeigt sich eine unangenehme Eigenart. Das Problem ist, dass der Optimierer sich selbst jede Möglichkeit nimmt, die Beziehungen der Eingabeparameter der Prozedur zu den Feldern in den anderen Datenströmen zu bestimmen:

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1
          

Hier wird die Prozedur ausgeführt, bevor ein einzelner Datensatz aus der Tabelle MY_TAB abgerufen wurde. Der Fehler isc_no_cur_rec error (kein aktueller Datensatz für die Abrufoperation) wird ausgelöst, wodurch die Ausführung unterbrochen wird.

Die Lösung besteht darin, eine Syntax zu verwenden, die die Join-Reihenfolge explizit angibt:

SELECT *
FROM MY_TAB
LEFT JOIN MY_PROC(MY_TAB.F) ON 1 = 1
          

Dies erzwingt, dass die Tabelle vor der Prozedur gelesen wird und alles ordnungsgemäß funktioniert.

Tipp

Diese Eigenart wurde als Fehler im Optimierer erkannt und wird in der nächsten Version von Firebird behoben.

Die WHERE-Klausel

Die WHERE-Klausel dient dazu, die zurückgegebenen Zeilen auf diejenigen zu beschränken, an denen der Aufrufer interessiert ist. Die Bedingung nach dem Schlüsselwort WHERE kann so einfach sein wie „Anzahl = 3 “ oder ein mehrschichtiger, geschachtelter Ausdruck, der Unterabfragen, Prädikate, Funktionsaufrufe, mathematische und logische Operatoren, Kontextvariablen und mehr enthält.

Die Bedingung in der WHERE-Klausel wird häufig als die Suchbedingung, der Suchausdruck oder einfach die Suche bezeichnet.

In DSQL und ESQL kann der Suchausdruck Parameter enthalten. Dies ist nützlich, wenn eine Abfrage mehrmals mit unterschiedlichen Eingabewerten wiederholt werden muss. In der SQL-Zeichenfolge, die an den Server übergeben wird, werden Fragezeichen als Platzhalter für die Parameter verwendet. Sie heißen Positionsparameter, weil sie nur durch ihre Position in der Zeichenfolge voneinander getrennt werden können. Konnektivitätsbibliotheken unterstützen oft benannte Parameter der Form :id, :amount, :a usw. Diese sind benutzerfreundlicher; Die Bibliothek sorgt dafür, dass die benannten Parameter in Positionsparameter übersetzt werden, bevor die Anweisung an den Server übergeben wird.

Die Suchbedingung kann auch lokale (PSQL) oder Host (ESQL)-Variablennamen enthalten, denen ein Doppelpunkt vorangestellt ist.

Syntax: 

SELECT ...
   FROM ...
   [...]
   WHERE <search-condition>
   [...]

<search-condition>  ::=  Ein boolescher Ausdruck, der
                         TRUE, FALSE oder möglicherweise UNKNOWN (NULL)
            zurückgibt

Nur die Zeilen, für die die Suchbedingung TRUE ergibt, sind in der Ergebnismenge enthalten. Seien Sie vorsichtig mit möglichen NULL-Ergebnissen: Wenn Sie einen NULL-Ausdruck mit NOT negieren, ist das Ergebnis immer NULL und die Zeile wird nicht berücksichtigt. Dies wird in einem der folgenden Beispiele demonstriert.

Beispiele

select genus, species from mammals
  where family = 'Felidae'
  order by genus
select * from persons
  where birthyear in (1880, 1881) 
     or birthyear between 1891 and 1898
select name, street, borough, phone
  from schools s
  where exists (select * from pupils p where p.school = s.id)
  order by borough, street
select * from employees
  where salary >= 10000 and position <> 'Manager'
select name from wrestlers
  where region = 'Europe'
    and weight > all (select weight from shot_putters
                      where region = 'Africa')
select id, name from players
  where team_id = (select id from teams where name = 'Buffaloes')
select sum (population) from towns
  where name like '%dam'
  and province containing 'land'
select password from usertable
  where username = current_user

Das folgende Beispiel zeigt, was passieren kann, wenn die Suchbedingung auf NULL ausgewertet wird.

Angenommen, Sie haben eine Tabelle mit den Namen einiger Kinder und der Anzahl der Murmeln, die sie besitzen. Zu einem bestimmten Zeitpunkt enthält die Tabelle diese Daten:

CHILD MARBLES
Anita 23
Bob E. 12
Chris <null>
Deirdre 1
Eve 17
Fritz 0
Gerry 21
Hadassah <null>
Isaac 6

Zuerst beachten Sie bitte den Unterschied zwischen NULL und 0: Fritz ist bekannt dafür überhaupt keine Murmeln zu haben, Chris' und Hadassahs Murmelanzahlen unbekannt.

Nun, wenn Sie diese SQL-Anweisung ausgeben:

select list(child) from marbletable where marbles > 10

Sie werden die Namen Anita, Bob E., Eve und Gerry bekommen. Diese Kinder haben alle mehr als 10 Murmeln.

Wenn Sie den Ausdruck negieren:

select list(child) from marbletable where not marbles > 10

Deirdre, Fritz und Isaac sind an der Reihe. Chris und Hadassah sind nicht enthalten, weil nicht bekannt ist, dass sie zehn oder weniger Murmeln besitzen. Sollten Sie diese letzte Abfrage ändern in:

select list(child) from marbletable where marbles <= 10

wird das Ergebnis immer noch dasselbe sein, weil der Ausdruck NULL <= 10 nun UNKNOWN ergibt. Das ist nicht dasselbe wie TRUE, also sind Chris und Hadassah nicht aufgelistet. Wenn Sie sie mit den „armen“-Kindern anzeigen möchten, ändern Sie die Abfrage in:

select list(child) from marbletable where marbles <= 10 or marbles is null

Jetzt wird die Suchbedingung für Chris und Hadassah wahr, weil „ marbles is null “ gibt in diesem Fall offensichtlich TRUE zurück. Tatsächlich kann die Suchbedingung jetzt für niemanden <konstant>NULL</konstant> sein.

Zuletzt zwei Beispiele für SELECT-Abfragen mit Parametern in der Suche. Es hängt von der Anwendung ab, wie Sie Abfrageparameter definieren sollten und selbst wenn es überhaupt möglich ist. Beachten Sie, dass Abfragen wie diese nicht sofort ausgeführt werden können: Sie müssen zuerst vorbereitet (prepared) sein. Nachdem eine parametrisierte Abfrage vorbereitet wurde, kann der Benutzer (oder der Aufrufcode) Werte für die Parameter bereitstellen und sie mehrmals ausführen lassen, wobei vor jedem Aufruf neue Werte eingegeben werden. Wie die Werte eingegeben werden und die Ausführung gestartet wird, ist Sache der Anwendung. In einer GUI-Umgebung gibt der Benutzer die Parameterwerte in der Regel in ein oder mehrere Textfelder ein und klickt dann auf die Schaltfläche „Ausführen“ oder „Aktualisieren“.

select name, address, phone frome stores
  where city = ? and class = ?
select * from pants
  where model = :model and size = :size and color = :col

Die letzte Abfrage kann nicht direkt an die Engine übergeben werden. Die Anwendung muss sie zuerst in das andere Format konvertieren und benannte Parameter den Positionsparametern zuordnen.

Die GROUP BY-Klausel

Inhaltsverzeichnis

HAVING

GROUP BY führt Ausgangszeilen mit derselben Kombination von Werten in der Elementliste in eine einzelne Zeile zusammen. Aggregatfunktionen in der Auswahlliste werden für jede Gruppe einzeln und nicht für das gesamte Dataset angewendet.

Wenn die Auswahlliste nur Aggregatspalten oder allgemeiner Spalten enthält, deren Werte nicht von einzelnen Zeilen in der zugrunde liegenden Menge abhängen, ist GROUP BY optional. Wenn sie weggelassen wird, besteht die endgültige Ergebnismenge aus einer einzelnen Zeile (vorausgesetzt, dass mindestens eine aggregierte Spalte vorhanden ist).

Wenn die Auswahlliste sowohl Aggregatspalten als auch Spalten enthält, deren Werte je Zeile variieren können, wird die Klausel GROUP BY obligatorisch.

Syntax: 

SELECT ... FROM ...
   GROUP BY <grouping-item> [, <grouping-item> ...]
   [HAVING <grouped-row-condition>]
   ...

<grouping-item>         ::=  <non-aggr-select-item>
                               | <non-aggr-expression>

<non-aggr-select-item>  ::=  column-copy
                               | column-alias
                               | column-position
          

Tabelle 6.5. Argumente der GROUP BY-Klausel

Argument Beschreibung
non-aggr-expression Jeder nicht aggregierende Ausdruck, der nicht in der SELECT-Liste enthalten ist, d.h. nicht ausgewählte Spalten aus der Quellenmenge oder Ausdrücke, die überhaupt nicht von den Daten in der Menge abhängen
column-copy Eine Literalkopie aus der SELECT-Liste eines Ausdrucks, der keine Aggregatfunktion enthält
column-alias Der Alias aus der SELECT-Liste eines Ausdrucks (Spalte), der keine Aggregatfunktion enthält
column-position Die Positionsnummer in der SELECT-Liste eines Ausdrucks (Spalte), der keine Aggregatfunktion enthält


Eine allgemeine Faustregel besagt, dass jedes nicht aggregierte Element in der SELECT-Liste ebenfalls in der GROUP BY-Liste enthalten sein muss. Sie können dies auf drei Arten tun:

  1. Indem der Gegenstand wörtlich aus der Auswahlliste kopiert wird, z.B. „class“ oder „'D:' || upper(doccode)“.

  2. Durch Angabe des Spaltenalias, falls vorhanden.

  3. Durch Angabe der Spaltenposition als Ganzzahl literal zwischen 1 und der Anzahl der Spalten. Ganzzahlwerte, die sich aus Ausdrücken oder Parametersubstitutionen ergeben, sind einfach unveränderlich und werden als solche in der Gruppierung verwendet. Sie werden jedoch keinen Effekt haben, da ihr Wert für jede Zeile gleich ist.

Anmerkung

Wenn Sie nach einer Spaltenposition gruppieren, wird der Ausdruck an dieser Position intern aus der Auswahlliste kopiert. Wenn es sich um eine Unterabfrage handelt, wird diese Unterabfrage in der Gruppierungsphase erneut ausgeführt. Das bedeutet, dass die Gruppierung nach der Spaltenposition, anstatt den Unterabfrageausdruck in der Gruppierungsklausel zu duplizieren, Tastenanschläge und Bytes speichert, dies ist jedoch keine Möglichkeit, Verarbeitungszyklen zu speichern!

Zusätzlich zu den erforderlichen Elementen kann die Gruppierungsliste auch Folgendes enthalten:

  • Spalten aus der Quelltabelle, die nicht in der Auswahlliste enthalten sind, oder Nicht-Aggregat-Ausdrücke, die auf solchen Spalten basieren. Das Hinzufügen solcher Spalten kann die Gruppen weiter unterteilen. Da diese Spalten jedoch nicht in der Auswahlliste enthalten sind, können Sie nicht feststellen, welche aggregierte Zeile mit welchem Wert in der Spalte übereinstimmt. Wenn Sie also an diesen Informationen interessiert sind, fügen Sie auch die Spalte oder den Ausdruck in die Auswahlliste—ein, die Sie wieder zur Regel führt: „Jede Nicht-Aggregat-Spalte in der Auswahlliste muss ebenfalls in der Gruppierungsliste sein“.

  • Ausdrücke, die nicht von den Daten in dem zugrunde liegenden Satz abhängen, z. Konstanten, Kontextvariablen, einwertige nicht-korrelierte Subselects usw. Dies wird nur der Vollständigkeit halber erwähnt, da das Hinzufügen solcher Elemente völlig sinnlos ist: Sie beeinflussen die Gruppierung überhaupt nicht. „Harmlose, aber nutzlose“ Elemente wie diese können auch in der Auswahlliste erscheinen, ohne in die Gruppierungsliste kopiert zu werden.

Beispiele

Wenn die Auswahlliste nur Aggregatspalten enthält, ist GROUP BY nicht obligatorisch:

select count(*), avg(age) from students
  where sex = 'M'

Dies wird eine einzelne Zeile zurückgeben, die die Anzahl der männlichen Studenten und deren Durchschnittsalter auflistet. Das Hinzufügen von Ausdrücken, die nicht von Werten in einzelnen Zeilen der Tabelle STUDENTS abhängen, ändert das nicht:

select count(*), avg(age), current_date from students
  where sex = 'M'

Die Zeile wird jetzt eine zusätzliche Spalte haben, die das aktuelle Datum anzeigt, aber ansonsten hat sich nichts Grundlegendes geändert. Eine Klausel GROUP BY ist weiterhin nicht erforderlich.

In beiden obigen Beispielen ist dies jedoch erlaubt. Dies ist absolut gültig:

select count(*), avg(age) from students
  where sex = 'M'
  group by class

und gibt eine Reihe für jede Klasse zurück, in der sich Jungen befinden, die die Anzahl der Jungen und ihr Durchschnittsalter in dieser bestimmten Klasse auflistet. (Wenn Sie auch das Feld current_date beibehalten, wird dieser Wert in jeder Zeile wiederholt, was nicht besonders aufregend ist.)

Die obige Abfrage hat jedoch einen großen Nachteil: Sie gibt Ihnen Informationen über die verschiedenen Klassen, aber Sie erfahren nicht, welche Zeile für welche Klasse gilt. Um diese zusätzlichen Informationen zu erhalten, muss die nicht aggregierte Spalte CLASS zur Auswahlliste hinzugefügt werden:

select class, count(*), avg(age) from students
  where sex = 'M'
  group by class

Jetzt haben wir eine nützliche Abfrage. Beachten Sie, dass durch das Hinzufügen der Spalte CLASS auch die Klausel GROUP BY obligatorisch wird. Wir können diese Klausel nicht mehr löschen, es sei denn, wir entfernen auch CLASS aus der Spaltenliste.

Die Ausgabe unserer letzten Abfrage könnte etwa so aussehen:

CLASS COUNT AVG
2A 12 13.5
2B 9 13.9
3A 11 14.6
3B 12 14.4
... ... ...

Die Überschriften „COUNT“ und „AVG“ sind nicht sehr informativ. In einem einfachen Fall wie diesem, könnten Sie damit durchkommen, aber im Allgemeinen sollten Sie aggregierten Spalten einen aussagekräftigen Namen geben, indem wir je einen Alias nutzen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class

Wie Sie aus der formalen Syntax der Spaltenliste entnehmen können, ist das Schlüsselwort AS optional.

Wenn Sie weitere nicht aggregierte (oder besser: zeilenabhängige) Spalten hinzufügen, müssen Sie sie auch der Klausel GROUP BY hinzufügen. Zum Beispiel möchten Sie vielleicht die oben genannten Informationen auch für Mädchen sehen; und Sie möchten vielleicht auch zwischen Internats- und Tagesschülern unterscheiden:

select class,
       sex,
       boarding_type,
       count(*) as number,
       avg(age) as avg_age
  from students
  group by class, sex, boarding_type

Dies kann zu folgendem Ergebnis führen:

CLASS SEX BOARDING_TYPE NUMBER AVG_AGE
2A F BOARDING 9 13.3
2A F DAY 6 13.5
2A M BOARDING 7 13.6
2A M DAY 5 13.4
2B F BOARDING 11 13.7
2B F DAY 5 13.7
2B M BOARDING 6 13.8
... ... ... ... ...

Jede Zeile in der Ergebnismenge entspricht einer bestimmten Kombination der Variablen class, sex und boarding type. Die zusammengefassten Ergebnisse—Anzahl und durchschnittliches Alter—sind für jede dieser eher spezifischen Gruppen einzeln angegeben. In einer Abfrage wie dieser sehen Sie keine Gesamtzahl für Jungen als Ganzes oder Tagesschüler als Ganzes. Das ist der Nachteil: Je mehr Nicht-Aggregat-Spalten Sie hinzufügen, desto mehr können Sie sehr spezifische Gruppen bestimmen, aber desto mehr verlieren Sie auch das allgemeine Bild aus den Augen. Natürlich können Sie die „gröberen“ Aggregate auch über separate Abfragen erhalten.

HAVING

Genau wie eine WHERE-Klausel die Zeilen in einer Datenmenge auf solche begrenzt, die die Suchbedingung erfüllen, so beschränkt die Unterklasse HAVING die aggregierten Zeilen in einer gruppierten Gruppe. HAVING ist optional und kann nur in Verbindung mit GROUP BY verwendet werden.

Die Bedingung(en) in der HAVING-Klausel können sich beziehen auf:

  • Jede aggregierte Spalte in der Auswahlliste. Dies ist die am häufigsten verwendete Alternative.

  • Jeder aggregierte Ausdruck, der nicht in der Auswahlliste enthalten ist, aber im Kontext der Abfrage zulässig ist. Dies ist manchmal auch nützlich.

  • Eine beliebige Spalte in der Liste GROUP BY. Obwohl dies legal ist, ist es effizienter, diese nicht aggregierten Daten zu einem früheren Zeitpunkt zu filtern: in der Klausel WHERE.

  • Ein beliebiger Ausdruck, dessen Wert nicht vom Inhalt des Datasets abhängt (wie eine Konstante oder eine Kontextvariable). Das ist zwar stichhaltig, aber völlig sinnlos, weil es entweder die gesamte Menge unterdrückt oder sie unberührt lässt, basierend auf Bedingungen, die nichts mit der Menge selbst zu tun haben.

Eine HAVING-Klausel kann nicht enthalten:

  • Nicht aggregierte Spaltenausdrücke, die nicht in der GROUP BY-Liste enthalten sind.

  • Spaltenpositionen. Eine Ganzzahl in der HAVING-Klausel ist nur eine Ganzzahl.

  • Spaltenaliase - nicht einmal wenn sie in der GROUP BY-Klausel vorkommen!

Beispiele

Aufbauend auf unseren früheren Beispielen könnte dies verwendet werden, um kleine Gruppen von Schülern zu überspringen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having count(*) >= 5

So wählen Sie nur Gruppen mit einem Mindestalter aus:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having max(age) - min(age) > 1.2

Beachten Sie, dass Sie, wenn Sie wirklich an diesen Informationen interessiert sind, diese normalerweise einschließen würden mittels min(age) und max(age) – oder dem Ausdruck „max(age) - min(age)“ – auch in der Select-Liste!

Um nur die 3. Klassen einzuschließen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having class starting with '3'

Besser wäre es, diese Bedingung in die WHERE-Klausel zu verschieben:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M' and class starting with '3'
  group by class

Die PLAN-Klausel

Die PLAN-Klausel ermöglicht es dem Benutzer, einen Datenabrufplan einzureichen, wodurch der Plan überschrieben wird, den der Optimierer automatisch erstellt hätte.

Syntax: 

PLAN <plan-expr>

<plan-expr>    ::=  (<plan-item> [, <plan-item> ...])
                    | <sorted-item>
                    | <joined-item>
                    | <merged-item>

<sorted-item>  ::=  SORT (<plan-item>)

<joined-item>  ::=  JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item>  ::=  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<plan-item>    ::=  <basic-item> | <plan-expr>

<basic-item>   ::=  <relation>
                    {NATURAL
                     | INDEX (<indexlist>)
                     | ORDER index [INDEX (<indexlist>)]}

<relation>     ::=  table
                    | view [table]

<indexlist>    ::=  index [, index ...]
          

Tabelle 6.6. Argumente der PLAN-Klausel

Argument Beschreibung
table Tabellenname oder sein Alias
view Ansichtname
index Indexname


Jedes Mal, wenn ein Benutzer eine Abfrage an die Firebird-Engine sendet, berechnet der Optimierer eine Datenabrufstrategie. Die meisten Firebird Clients können diesen Abrufplan für den Benutzer sichtbar machen. In Firebirds eigenem isql-Dienstprogramm geschieht dies mit dem Befehl SET PLAN ON. Wenn Sie Abfragepläne analysieren und keine Abfragen ausführen, zeigt SET PLANONLY ON den Plan an, ohne die Abfrage auszuführen.

In den meisten Situationen können Sie darauf vertrauen, dass Firebird den optimalen Abfrageplan für Sie auswählt. Wenn Sie jedoch komplizierte Abfragen haben, die nicht leistungsfähig sind, lohnt es sich möglicherweise, den Plan zu prüfen und zu prüfen, ob Sie ihn verbessern können.

Einfache Pläne

Die einfachsten Pläne bestehen nur aus einem Beziehungsnamen gefolgt von einer Abrufmethode. Z. B. für eine unsortierte Ein-Tabellen-Auswahl ohne eine WHERE-Klausel:

select * from students
  plan (students natural)

Wenn eine WHERE- oder eine HAVING-Klausel vorhanden ist, können Sie den Index angeben, der zum Auffinden von Übereinstimmungen verwendet werden soll:

select * from students
  where class = '3C'
  plan (students index (ix_stud_class))

Die Anweisung INDEX wird auch für Join-Bedingungen verwendet (etwas später diskutiert). Es kann eine Liste von Indizes enthalten, die durch Kommata getrennt sind.

ORDER gibt den Index zum Sortieren des Satzes an, wenn eine Klausel ORDER BY oder GROUP BY vorhanden ist:

select * from students
  plan (students order pk_students)
  order by id

ORDER und INDEX können kombiniert werden:

select * from students
  where class >= '3'
  plan (students order pk_students index (ix_stud_class))
  order by id

Es ist völlig in Ordnung, wenn ORDER und INDEX denselben Index angeben:

select * from students
  where class >= '3'
  plan (students order ix_stud_class index (ix_stud_class))
  order by class

Wenn Sie einen Sortiersatz verwenden möchten, wenn kein verwendbarer Index verfügbar ist (oder wenn Sie die Verwendung des Index unterdrücken möchten), lassen Sie ORDER aus und stellen Sie dem Planausdruck SORT voran:

select * from students
  plan sort (students natural)
  order by name

Oder wenn ein Index für die Suche verwendet wird:

select * from students
  where class >= '3'
  plan sort (students index (ix_stud_class))
  order by name

Beachten Sie, dass sich SORT im Gegensatz zu ORDER außerhalb der Klammern befindet. Dies spiegelt die Tatsache wider, dass die Datenzeilen ungeordnet abgerufen und anschließend von der Engine sortiert werden.

Geben Sie bei der Auswahl aus einer Ansicht die Ansicht und die betreffende Tabelle an. Zum Beispiel, wenn Sie eine Ansicht FRESHMEN haben, die nur die Erstsemester auswählt:

select * from freshmen
  plan (freshmen students natural)

Oder zum Beispiel:

select * from freshmen
  where id > 10
  plan sort (freshmen students index (pk_students))
  order by name desc

Wichtig

Wenn eine Tabelle oder Sicht mit einem Alias versehen wurde, muss der Alias und nicht der ursprüngliche Name in der Klausel PLAN verwendet werden.

Zusammengesetzte Pläne

Wenn ein Join erstellt wird, können Sie den Index angeben, der für den Abgleich verwendet werden soll. Sie müssen auch die Anweisung JOIN für die beiden Streams im Plan verwenden:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s natural, c index (pk_classes))

Derselbe Join, sortiert nach einer indizierten Spalte:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s order pk_students, c index (pk_classes))
  order by s.id

Und auf einer nicht indizierten Spalte:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan sort (join (s natural, c index (pk_classes)))
  order by s.name

Mit einer Suche hinzugefügt:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  where s.class <= '2'
  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  order by s.name

Als linker Outer Join:

select s.id, s.name, s.class, c.mentor
  from classes c
  left join students s on c.name = s.class
  where s.class <= '2'
  plan sort (join (c natural, s index (fk_student_class)))
  order by s.name

Wenn für die Join-Kriterien kein Index verfügbar ist (oder wenn Sie ihn nicht verwenden möchten), muss der Plan zuerst beide Streams in ihren Join-Spalten sortieren und dann zusammenführen. Dies wird mit der Anweisung SORT (die wir bereits erreicht haben) und MERGE anstelle von JOIN erreicht:

select * from students s
  join classes c on c.cookie = s.cookie
  plan merge (sort (c natural), sort (s natural))

Durch das Hinzufügen einer ORDER BY-Klausel muss das Ergebnis der Zusammenführung ebenfalls sortiert werden:

select * from students s
  join classes c on c.cookie = s.cookie
  plan sort (merge (sort (c natural), sort (s natural)))
  order by c.name, s.id

Schließlich fügen wir eine Suchbedingung für zwei indexierbare Spalten der Tabelle STUDENTS hinzu:

select * from students s
  join classes c on c.cookie = s.cookie
  where s.id < 10 and s.class <= '2'
  plan sort (merge (sort (c natural),
                    sort (s index (pk_students, fk_student_class))))
  order by c.name, s.id

Wie aus der formalen Syntaxdefinition hervorgeht, können JOINs und MERGEs im Plan mehr als zwei Datenströme kombinieren. Außerdem kann jeder Planausdruck als Planposten in einem umfassenden Plan verwendet werden. Dies bedeutet, dass Pläne bestimmter komplizierter Abfragen verschiedene Verschachtelungsebenen haben können.

Schließlich können Sie anstelle von MERGE auch SORT MERGE schreiben. Da dies absolut keinen Unterschied macht und zu Verwechslungen mit „realSORT-Direktiven führen kann (diejenigen, die etwas tun machen einen Unterschied), ist es wahrscheinlich am besten zu bleiben zu einfach MERGE.

Warnung

Gelegentlich akzeptiert der Optimierer einen Plan und folgt ihm dann nicht, obwohl er ihn nicht als ungültig zurückweist. Ein solches Beispiel war

MERGE (unsortierter Stream, unsortierter Stream)
          

Es ist ratsam, einen solchen Plan als „veraltet“ zu behandeln .

UNION

Ein UNION verkettet zwei oder mehr Datasets und erhöht so die Anzahl der Zeilen, nicht aber die Anzahl der Spalten. Datasets, die an einer UNION teilnehmen, müssen die gleiche Anzahl von Spalten haben, und Spalten an entsprechenden Positionen müssen vom selben Typ sein. Abgesehen davon können sie völlig unabhängig sein.

Standardmäßig unterdrückt eine Union doppelte Zeilen. UNION ALL zeigt alle Zeilen einschließlich aller Duplikate an. Das optionale Schlüsselwort DISTINCT macht das Standardverhalten explizit.

Syntax: 

<union>               ::=  <individual-select>
                           UNION [DISTINCT | ALL]
                           <individual-select>
                           [UNION [DISTINCT | ALL]
                            <individual-select>
                            ...]
                           [<union-wide-clauses>]

<individual-select>   ::=  SELECT
                           [TRANSACTION name]
                           [FIRST <m>] [SKIP <n>]
                           [DISTINCT | ALL] <columns>
                           [INTO <host-varlist>]
                           FROM source [[AS] alias]
                           [<joins>]
                           [WHERE <condition>]
                           [GROUP BY <grouping-list>
                           [HAVING <aggregate-condition>]]
                           [PLAN <plan-expr>]

<union-wide-clauses>  ::=  [ORDER BY <ordering-list>]
                           [ROWS m [TO n]]
                           [FOR UPDATE [OF <columns>]]
                           [WITH LOCK]
                           [INTO <PSQL-varlist>]

Unions ermitteln ihre Spaltennamen aus der ersten Abfrage. Wenn Sie einen Alias für Vereinigungsspalten verwenden möchten, tun Sie dies in der Spaltenliste des obersten SELECT. Aliasnamen in anderen teilnehmenden Selects sind zulässig und können sogar nützlich sein, werden jedoch nicht auf Unionsebene weitergegeben.

Wenn eine Union eine ORDER BY-Klausel hat, sind die einzigen zulässigen Sortierelemente Integerliterale, die 1-basierte Spaltenpositionen angeben, optional gefolgt von einem ASC/DESC und/oder einer NULLS FIRST/LAST-Direktive. Dies bedeutet auch, dass Sie eine Union nicht nach etwas sortieren können, die keine Spalte in der Union ist. (Sie können jedoch eine abgeleitete Tabelle einfügen, die Ihnen alle üblichen Sortieroptionen zurückgibt.)

Unions sind in Unterabfragen jeglicher Art erlaubt und können selbst Unterabfragen enthalten. Sie können auch Joins enthalten und an einem Join teilnehmen, wenn sie in eine abgeleitete Tabelle eingebunden werden.

Beispiele

Diese Abfrage präsentiert Informationen aus verschiedenen Musiksammlungen in einem Datensatz mithilfe von Unionen:

select id, title, artist, length, 'CD' as medium
  from cds
union
select id, title, artist, length, 'LP'
  from records
union
select id, title, artist, length, 'MC'
  from cassettes
order by 3, 2  -- artist, title

Wenn id, title, artist und length die einzigen Felder in den involvierten Tabellen sind, kann die Abfrage auch so geschrieben werden:

select c.*, 'CD' as medium
  from cds c
union
select r.*, 'LP'
  from records r
union
select c.*, 'MC'
  from cassettes c
order by 3, 2  -- artist, title

Das Qualifizieren der „Sternchen“ ist hier notwendig, da sie nicht das einzige Element in der Spaltenliste sind. Beachten Sie, dass die Aliase von „c“ in der ersten und dritten Auswahl nicht miteinander in Konflikt stehen: ihre Gültigkeitsbereiche sind nicht unionsweit, sondern gelten nur für ihre jeweiligen Auswahlabfragen.

Die nächste Abfrage ruft Namen und Telefonnummern von Übersetzern und Korrektoren ab. Übersetzer, die auch als Korrekturleser arbeiten, werden nur einmal im Ergebnis angezeigt, sofern ihre Telefonnummer in beiden Tabellen identisch ist. Das gleiche Ergebnis kann ohne DISTINCT erzielt werden. Mit ALL würden diese Personen zweimal angezeigt.

select name, phone from translators
  union distinct
select name, telephone from proofreaders

Ein UNION innerhalb einer Unterabfrage:

select name, phone, hourly_rate from clowns
where hourly_rate < all
  (select hourly_rate from jugglers
     union
   select hourly_rate from acrobats)
order by hourly_rate

ORDER BY

Wenn eine SELECT-Anweisung ausgeführt wird, ist die Ergebnismenge in keiner Weise sortiert. Es kommt häufig vor, dass Zeilen chronologisch sortiert angezeigt werden, weil sie in derselben Reihenfolge zurückgegeben werden, in der sie von INSERT-Anweisungen zur Tabelle hinzugefügt wurden. Um eine Sortierreihenfolge für die Mengenspezifikation anzugeben, wird eine ORDER BY-Klausel verwendet.

Syntax: 

SELECT ... FROM ...
...
ORDER BY <ordering-item> [, <ordering-item> …]

<ordering-item> ::=
  {col-name | col-alias | col-position | expression}
  [COLLATE collation-name]
  [ASC[ENDING] | DESC[ENDING]]
  [NULLS {FIRST|LAST}]
        

Tabelle 6.7. Argumente für die ORDER BY-Klausel

Argument Beschreibung
col-name Vollständiger Spaltenname
col-alias Spaltenalias
col-position Spaltenposition in der SELECT-Liste
expression Jeder Ausdruck
collation-name Collations-Name (Sortierreihenfolge für String-Typen)


Beschreibung

Die ORDER BY-Klausel besteht aus einer durch Komma getrennten Liste der Spalten, auf denen der Ergebnisdatensatz sortiert werden soll. Die Sortierreihenfolge kann durch den Namen der Spalte—angegeben werden, jedoch nur, wenn die Spalte zuvor in der Spaltenliste SELECT nicht mit einem Alias versehen war. Der Alias muss verwendet werden, wenn er dort verwendet wurde. Die Ordnungsnummer der Spalte, des Alias, der der Spalte in der SELECT-Liste mit Hilfe des Schlüsselworts AS zugewiesen wurde, oder die Nummer der Spalte in der Liste SELECT können uneingeschränkt verwendet werden.

Die drei Arten, die Spalten für die Sortierreihenfolge auszudrücken, können in der gleichen ORDER BY-Klausel gemischt werden. Zum Beispiel kann eine Spalte in der Liste durch ihren Namen spezifiziert werden und eine andere Spalte kann durch ihre Nummer spezifiziert werden.

Anmerkung

Wenn Sie die Spaltenposition verwenden, um die Sortierreihenfolge für eine Abfrage des SELECT * -Stils anzugeben, erweitert der Server das Sternchen auf die vollständige Spaltenliste, um die Spalten für die Sortierung zu bestimmen. Es wird jedoch als „schlampige Praxis“ angesehen, um auf diese Weise geordnete Mengen zu entwerfen.

Sortierrichtung

Das Schlüsselwort ASCENDING, normalerweise abgekürzt als ASC, gibt eine Sortierrichtung vom niedrigsten zum höchsten an. ASCENDING ist die Standardsortierrichtung.

Das Schlüsselwort DESCENDING, normalerweise abgekürzt als DESC, gibt eine Sortierrichtung vom höchsten zum niedrigsten an.

Angeben der aufsteigenden Reihenfolge für eine Spalte und der absteigenden Reihenfolge für eine andere Spalte ist zulässig.

Collations-Reihenfolge

Das Schlüsselwort COLLATE gibt die Sortierreihenfolge für eine Zeichenfolgespalte an, wenn Sie eine Sortierung benötigen, die sich von der normalen Sortierung für diese Spalte unterscheidet. Die normale Sortierreihenfolge ist entweder die Standardreihenfolge für den Datenbankzeichensatz oder eine, die explizit in der Definition der Spalte festgelegt wurde.

NULLen positionieren

Das Schlüsselwort NULLS gibt an, wo NULL in der betroffenen Spalte in der Sortierung stehen wird:NULLS FIRST platziert die Zeilen mit der NULL-Spalte oberhalb der Zeilen mit den Spaltenwerten; NULLS LAST platziert diese Zeilen hinter den Spaltenwerten.

NULLS FIRST ist der Standard.

Sortieren von UNION-Sätzen

Die einzelnen Abfragen, die zu einer UNION beitragen, können keine ORDER BY-Klausel verwenden. Die einzige Option besteht darin, die gesamte Ausgabe mit einer ORDER BY-Klausel am Ende der gesamten Abfrage zu sortieren.

Das einfachste—und in einigen Fällen die einzige—Methode zum Angeben der Sortierreihenfolge ist die Ordinalspaltenposition. Es ist jedoch auch zulässig, die Spaltennamen oder Aliase nur aus der ersten beitragenden Abfrage zu verwenden.

Die Anweisungen ASC / DESC und / oder NULLS sind für diese globale Gruppe verfügbar.

Wenn eine diskrete Reihenfolge innerhalb der beitragenden Menge erforderlich ist, kann die Verwendung von abgeleiteten Tabellen oder allgemeinen Tabellenausdrücken für diese Mengen eine Lösung sein.

Beispiele

Sortierung der Ergebnismenge in aufsteigender Reihenfolge, Sortierung nach den Spalten RDB$CHARACTER_SET_ID, RDB$COLLATION_ID der Tabelle DB$COLLATIONS:

SELECT
    RDB$CHARACTER_SET_ID AS CHARSET_ID,
    RDB$COLLATION_ID AS COLL_ID,
    RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY RDB$CHARACTER_SET_ID, RDB$COLLATION_ID
        

Das Gleiche, aber Sortieren nach den Spaltenaliasnamen:

SELECT
    RDB$CHARACTER_SET_ID AS CHARSET_ID,
    RDB$COLLATION_ID AS COLL_ID,
    RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY CHARSET_ID, COLL_ID
        

Sortieren der Ausgabedaten nach den Spaltenpositionsnummern:

SELECT
    RDB$CHARACTER_SET_ID AS CHARSET_ID,
    RDB$COLLATION_ID AS COLL_ID,
    RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY 1, 2
        

Sortierung einer SELECT *-Abfrage nach Positionsnummern möglich, aber hässlich und nicht empfohlen:

SELECT *
FROM RDB$COLLATIONS
ORDER BY 3, 2
        

Sortierung nach der zweiten Spalte in der BOOKS-Tabelle:

SELECT
    BOOKS.*,
    FILMS.DIRECTOR
FROM BOOKS, FILMS
ORDER BY 2
        

Achtung

Ausdrücke, deren Berechnungsergebnisse nicht negative ganze Zahlen sind, werden als Spaltenpositionszahlen interpretiert und verursachen eine Ausnahme, wenn sie außerhalb des Bereichs von 1 bis zur Anzahl der Spalten liegen.

Beispiel: 

SELECT
  X, Y, NOTE
FROM PAIRS
ORDER BY X+Y DESC
            

  • Die von einer Funktion oder Prozedur zurückgegebene Zahl ist nicht vorhersehbar, unabhängig davon, ob die Sortierreihenfolge durch den Ausdruck selbst oder durch die Spaltennummer definiert ist.
  • Nur nicht negative ganze Zahlen werden als Spaltennummern interpretiert
  • Eine Ganzzahl, die durch einmalige Auswertung eines Ausdrucks oder durch Parametersubstitution erhalten wurde, wird als Konstante gespeichert, da dieser Wert für alle Zeilen gilt.

Beispiele, Fortsetzung

Sortierung in absteigender Reihenfolge nach den Werten der Spalte PROCESS_TIME, wobei NULL am Anfang der Menge steht:

SELECT *
FROM MSG
ORDER BY PROCESS_TIME DESC NULLS FIRST
        

Sortieren der Menge, die von einer UNION von zwei Abfragen erhalten wurde. Die Ergebnisse werden in absteigender Reihenfolge für die Werte in der zweiten Spalte sortiert, wobei NULLen am Ende der Menge stehen; und in aufsteigender Reihenfolge für die Werte der ersten Spalte mit NULLen am Anfang.

SELECT
  DOC_NUMBER, DOC_DATE
FROM PAYORDER
UNION ALL
SELECT
  DOC_NUMBER, DOC_DATE
FROM BUDGORDER
ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST
        

ROWS

Verwendet für:  Abrufen eines Stücks von Zeilen aus einer geordneten Menge

Verfügbar in: DSQL, PSQL

Syntax: 

SELECT <columns> FROM ...
   [WHERE ...]
   [ORDER BY ...]
   ROWS <m> [TO <n>]
           

Tabelle 6.8. Argumente für die ROWS-Klausel

Argument Beschreibung
m, n Beliebiger Integer-Ausdrücke


Beschreibung: Begrenzt die Anzahl der Zeilen, die von der Anweisung SELECT an eine angegebene Zahl oder einen angegebenen Bereich zurückgegeben werden.

Die Klauseln FIRST und SKIP haben die gleiche Aufgabe wie ROWS sind jedoch nicht SQL-konform. Die Verwendung von ROWS ist daher im neuen Code vorzuziehen. Im Gegensatz zu FIRST und SKIP akzeptieren die Klauseln ROWS und TO beliebige Integerausdrücke als Argumente ohne Klammern. Natürlich können Klammern für verschachtelte Auswertungen innerhalb des Ausdrucks noch immer benötigt werden und eine Unterabfrage muss immer in Klammern eingeschlossen sein.

Wichtig

  • Nummerierung der Zeilen in der Zwischenmenge—die Gesamtmenge, die auf der Festplatte zwischengespeichert wird, bevor die „Scheibe“ extrahiert wird—beginnt bei 1.
  • Sowohl FIRST / SKIP als auch ROWS können ohne ORDER BY-Klausel verwendet werden, obwohl dies selten sinnvoll ist, es sei denn Sie möchten nur einen kurzen Blick auf die Tabellendaten werfen und es ist nicht wichtig, dass Zeilen in zufälliger Reihenfolge stehen. Zu diesem Zweck würde eine Abfrage wie „SELECT * FROM TABLE1 ROWS 20“ die ersten 20 Zeilen anstelle einer ganzen Tabelle, die ziemlich groß sein könnte, zurückgeben.

Der Aufruf von ROWS m gibt die ersten m Zeilen des angegebenen Satzes zurück.

Merkmale der Verwendung von ROWS m ohne eine TO-Klausel:

  • Wenn m größer als die Gesamtzahl der Datensätze im Zwischendatensatz ist, wird die gesamte Menge zurückgegeben
  • Wenn m = 0, wird ein leerer Satz zurückgegeben
  • Wenn m < 0, wird der SELECT-Aufruf in einem Fehler enden

Der Aufruf von ROWS m TO n gibt die Zeilen aus dem Satz zurück, beginnend mit Zeile m und endend nach Zeile n—inklusive Satz.

Merkmale der Verwendung von ROWS m mit einer TO-Klausel:

  • Wenn m größer ist als die Gesamtzahl der Zeilen in der Zwischengruppe und n < = m, wird eine leere Menge zurückgegeben
  • Ist m nicht größer als n und n größer als die Gesamtzahl der Zeilen in der Zwischengruppe, ist die Ergebnismenge beschränkt auf Zeilen beginnend mit m bis zum Ende des Satzes
  • Wenn m < 1 und n < 1, schlägt der SELECT-Anweisungsaufruf mit einem Fehler fehl
  • Wenn n = m - 1 ist, wird eine leere Menge zurückgegeben
  • Wenn n < m - 1, schlägt der Aufruf der Anweisung SELECT mit einem Fehler fehl

Verwenden einer TO-Klausel ohne eine ROWS-Klausel:

Während ROWS die nicht standardmäßige Syntax FIRST und SKIP ersetzt, gibt es eine Situation, in der die Standardsyntax nicht das gleiche Verhalten bietet: Mit Angabe von SKIP n wird der gesamte Zwischensatz ohne die ersten n-Reihen zurückgegeben. Die ROWS ... TO-Syntax benötigt ein wenig Hilfe, um dies zu erreichen.

Bei der Syntax ROWS benötigen Sie eine ROWS-Klausel in Verbindung mit der TO-Klausel. Anschließend machen Sie das zweite Argument ( n ) größer als die Größe des Zwischendatensatzes. Dies wird erreicht, indem ein Ausdruck für n erstellt wird, der eine Unterabfrage verwendet, um die Anzahl der Zeilen in der Zwischengruppe abzurufen, und 1 dazu addiert.

Das Mischen von ROWS und FIRST / SKIP

Die Syntax ROWS kann nicht mit der Syntax FIRST / SKIP im selben SELECT-Ausdruck gemischt werden. Die Verwendung der verschiedenen Syntaxen in verschiedenen Unterabfragen in derselben Anweisung ist jedoch zulässig.

ROWS-Syntax in UNION-Abfragen

Wenn ROWS in einer UNION-Abfrage verwendet wird, wird die ROWS-Direktive auf UNION-Satzes angewendet und muss hinter dem letzten SELECT-Statement stehen.

Wenn es erforderlich ist, die Teilmengen zu begrenzen, die von einer oder mehreren SELECT-Anweisungen innerhalb von UNION zurückgegeben werden, gibt es eine Reihe von Optionen:

  1. Verwenden Sie die FIRST / SKIP-Syntax in diesen SELECT-Anweisungen — bedenken Sie, dass eine ordering-Klausel (ORDER BY) nicht lokal auf die einzelnen Abfragen angewendet werden kann, aber nur für den kombinierten Ausgang.
  2. Konvertieren Sie die Abfragen in abgeleitete Tabellen mit ihren eigenen ROWS-Klauseln.

Beispiele

Die folgenden Beispiele schreiben die Beispiele des Abschnitts über FIRST und SKIP, weiter oben in diesem Kapitel, neu.

Gib die ersten zehn Namen aus der Ausgabe einer sortierten Abfrage in der Tabelle PEOPLE aus:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 1 TO 10
        

oder äquivalent dazu:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 10
        

Gib alle Datensätze aus der PEOPLE-Tabelle mit Ausnahme der ersten 10 Namen zurück:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 11 TO (SELECT COUNT(*) FROM People)
        

Und diese Abfrage gibt die letzten 10 Datensätze zurück (achten Sie auf die Klammern):

SELECT id, name
FROM People
ORDER BY name ASC
ROWS (SELECT COUNT(*) - 9 FROM People)
TO (SELECT COUNT(*) FROM People)
        

Diese gibt die Zeilen 81-100 aus der Tabelle PEOPLE zurück:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 81 TO 100
        

Anmerkung

ROWS kann außerdem in Kombination mit den Anweisungen UPDATE und DELETE verwendet werden.

FOR UPDATE [OF]

Syntax: 

SELECT ... FROM single_table
   [WHERE ...]
   [FOR UPDATE [OF ...]]
          

FOR UPDATE tut nicht, was es vorgibt. Der einzige Effekt ist derzeit, den Pre-Fetch-Puffer zu deaktivieren.

Tipp

Dies wird sich wahrscheinlich in Zukunft ändern: Es ist geplant, mit FOR UPDATE markierte Cursor zu validieren, wenn sie wirklich aktualisierbar sind und positionierte Aktualisierungen und Löschungen für Cursor ablehnen, die als nicht aktualisierbar eingestuft werden.

Die OF-Unterklausel tut rein gar nichts.

WITH LOCK

Verfügbar in: DSQL, PSQL

Verwendet für: Begrenzte pessimistische Sperrung

Beschreibung: WITH LOCK bietet eine begrenzte explizite pessimistische Sperrfunktion für die vorsichtige Verwendung unter Bedingungen, bei denen für den betroffenen Zeilensatz Folgendes gilt:

  1. extrem klein (idealerweise ein Singleton), und

  2. genau gesteuert durch den Anwendungscode.

Dies ist nur für Experten!

Die Notwendigkeit einer pessimistischen Sperre in Firebird ist in der Tat sehr selten und sollte gut verstanden werden, bevor die Verwendung dieser Erweiterung in Betracht gezogen wird.

Es ist wichtig, die Auswirkungen der Transaktionsisolation und anderer Transaktionsattribute zu verstehen, bevor Sie das explizite Sperren in Ihrer Anwendung implementieren.

Syntax: 

SELECT ... FROM single_table
   [WHERE ...]
   [FOR UPDATE [OF ...]]
   WITH LOCK

Wenn die WITH LOCK-Klausel erfolgreich ist, sichert sie eine Sperre für die ausgewählten Zeilen und verhindert, dass eine andere Transaktion Schreibzugriff auf eine dieser Zeilen oder deren abhängige Elemente erhält, bis die Transaktion endet.

WITH LOCK kann nur mit einer SELECT-Anweisung der obersten Ebene verwendet werden. Es steht nicht zur Verfügung:

  • in einer Unterabfrage-Spezifikation

  • für Join-Sätze

  • mit dem DISTINCT-Operator, einer GROUP BY-Klausel oder einer anderen Aggregat-Operation

  • mit einer Ansicht

  • mit der Ausgabe einer abfragbaren gespeicherten Prozedur

  • mit einem externen Tabelle

  • mti einer UNION-Abfrage

Da die Engine wiederum berücksichtigt, dass jeder Datensatz unter eine explizite Sperranweisung fällt, gibt sie entweder die derzeit festgeschriebene Datensatzversion (zum Zeitpunkt als die Anweisung gesendet wurde) zurück, unabhängig vom Datenbankstatus, oder eine Ausnahme.

Das Warteverhalten und die Konfliktmeldung hängen von den im TPB-Block angegebenen Transaktionsparametern ab:

Tabelle 6.9. Wie TPB-Einstellungen das explizite Sperren beeinflussen

TPB-Modus Verhalten

isc_tpb_consistency

Explizite Sperren werden von impliziten oder expliziten Sperren auf Tabellenebene außer Kraft gesetzt und ignoriert.

isc_tpb_concurrency

+ isc_tpb_nowait

Wenn ein Datensatz von einer Transaktion geändert wird, die festgeschrieben wurde, seit die Transaktion versucht hat, die explizite Sperre zu starten, oder eine aktive Transaktion eine Änderung dieses Datensatzes durchgeführt hat, wird sofort eine Aktualisierungskonfliktausnahme ausgelöst.

isc_tpb_concurrency

+ isc_tpb_wait

Wenn der Datensatz von einer Transaktion geändert wird, die seit dem Versuch der Ausführung der expliziten Sperre festgeschrieben wurde, wird sofort eine Ausnahme für den Aktualisierungskonflikt ausgelöst.

Wenn eine aktive Transaktion den Besitz dieses Datensatzes innehat (durch explizites Sperren oder durch eine normale optimistische Schreibsperre), wartet die Transaktion, die die explizite Sperre verursacht, auf das Ergebnis der blockierenden Transaktion und, wenn sie beendet ist, versucht sie, die Sperre zu wieder aufzuheben. Wenn die blockierende Transaktion eine geänderte Version dieses Datensatzes erstellt hat, wird eine Ausnahme für den Aktualisierungskonflikt ausgelöst.

isc_tpb_read_committed

+ isc_tpb_nowait

Wenn es eine aktive Transaktion gibt, die den Besitz für diesen Datensatz innehat (durch explizites Sperren oder normale Aktualisierung), wird sofort eine Aktualisierungskonfliktausnahme ausgelöst.

isc_tpb_read_committed

+ isc_tpb_wait

Wenn es eine aktive Transaktion gibt, die den Besitz dieses Datensatzes innehat (durch explizites Sperren oder durch eine normale optimistische Schreibsperre), wartet die Transaktion, die die explizite Sperre verursacht, auf das Ergebnis der Blockierungstransaktion und wenn sie beendet ist, versucht sie die Sperre zu wieder aufzuheben.

Aktualisierungskonfliktausnahmen können niemals durch eine explizite Sperranweisung in diesem TPB-Modus ausgelöst werden.


Verwendung einer FOR UPDATE-Klausel

Wenn die Unterklausel FOR UPDATE der Unterklausel WITH LOCK vorangestellt ist, werden gepufferte Abrufe unterdrückt. Somit wird die Sperre zum Zeitpunkt des Abrufens auf jede Zeile einzeln angewendet. Es wird dann möglich, dass eine Sperre, die bei Anforderung erfolgreich zu sein scheint, trotzdem anschließend fehlschlägt, wenn versucht wird, eine Zeile abzurufen, die in der Zwischenzeit durch eine andere Transaktion gesperrt wurde.

Tipp

Als eine Alternative kann es in Ihren Zugriffskomponenten möglich sein, die Größe des Abrufpuffers auf 1 zu setzen. Dies würde es Ihnen ermöglichen, die aktuell gesperrte Zeile zu verarbeiten, bevor die nächste geholt und gesperrt wird, oder Fehler zu behandeln, ohne ihre Transaktion zurückzurollen.

OF <Spaltennamen>

Diese optionale Unterklausel macht überhaupt nichts.

Siehe auch: FOR UPDATE [OF]

Wie die Engine mit WITH LOCK umgeht

Wenn eine UPDATE-Anweisung versucht, auf einen Datensatz zuzugreifen, der durch eine andere Transaktion gesperrt ist, löst sie abhängig vom TPB-Modus entweder eine Aktualisierungskonfliktausnahme aus oder wartet auf den Abschluss der Sperrtransaktion. Das Verhalten der Engine ist hier so, als wäre dieser Datensatz bereits durch die Sperrtransaktion modifiziert worden.

Bei Konflikten mit pessimistischen Sperren werden keine speziellen gdscodes zurückgegeben.

Die Engine garantiert, dass alle von einer expliziten Lock-Anweisung zurückgegebenen Datensätze tatsächlich gesperrt sind und die in der WHERE-Klausel angegebenen Suchbedingungen erfüllen, solange die Suchbedingungen nicht von anderen Tabellen, Joins, Unterabfragen usw. abhängen. Außerdem wird garantiert, dass Zeilen, die die Suchbedingungen nicht erfüllen, nicht von der Anweisung gesperrt werden. Sie kann nicht garantieren, dass es keine Zeilen gibt, die zwar die Suchbedingungen erfüllen, aber nicht gesperrt sind.

Anmerkung

Diese Situation kann auftreten, wenn andere parallele Transaktionen ihre Änderungen im Verlauf der Ausführung der Sperranweisung festschreiben.

Die Engine sperrt Zeilen zum Abrufzeitpunkt. Dies hat wichtige Konsequenzen, wenn Sie mehrere Zeilen gleichzeitig sperren. Bei vielen Zugriffsmethoden für Firebird-Datenbanken wird die Ausgabe standardmäßig in Paketen mit einigen hundert Zeilen abgerufen („gepufferte Abrufe“). Die meisten Datenzugriffskomponenten können die Zeilen, die im zuletzt abgerufenen Paket enthalten sind, nicht anzeigen, wenn ein Fehler aufgetreten ist.

Fallstricke mit WITH LOCK

  • Durch das Zurücksetzen eines impliziten oder expliziten Sicherungspunkts werden Datensatzsperren freigegeben, die unter diesem Sicherungspunkt ausgeführt wurden, jedoch keine wartenden Transaktionen. Anwendungen sollten nicht von diesem Verhalten abhängig sein, da sie sich in Zukunft ändern können.

  • Während explizite Sperren zum Verhindern und / oder Behandeln ungewöhnlicher Fehler beim Updatekonflikt verwendet werden können, steigt die Anzahl der Deadlockfehler, wenn Sie Ihre Sperrstrategie nicht sorgfältig planen und streng steuern.

  • Die meisten Anwendungen benötigen keine expliziten Sperren. Die Hauptzwecke expliziter Sperren sind (1) die teure Behandlung von Fehlern bei der Aktualisierung von Konflikten in stark ausgelasteten Anwendungen zu verhindern und (2) die Integrität von Objekten zu erhalten, die einer relationalen Datenbank in einer Clusterumgebung zugeordnet sind. Wenn Ihre explizite Sperrung nicht in eine dieser beiden Kategorien fällt, ist dies die falsche Vorgehensweise in Firebird.

  • Explizites Sperren ist eine erweiterte Funktion. Missbrauchen Sie sie nicht! Während Lösungen für diese Art von Problemen sehr wichtig für Websites sein können, die Tausende von gleichzeitigen Schreibzugriffen behandeln, oder für ERP / CRM-Systeme, die in großen Unternehmen arbeiten, müssen die meisten Anwendungsprogramme unter solchen Bedingungen nicht arbeiten.

Beispiele, die explizites Sperren verwenden

  1. Einfach:

    SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK
  2. Mehrere Zeilen, eins-zu-eins-Verarbeitung mit SQL-Cursor:

    SELECT * FROM DOCUMENT WHERE PARENT_ID=?
       FOR UPDATE WITH LOCK

INTO

Verwendet für: Übergabe von SELECT-Ausgaben an Variablen

Verfügbar in: PSQL

Im PSQL-Code (Trigger, gespeicherte Prozeduren und ausführbare Blöcke) können die Ergebnisse einer SELECT-Anweisung Zeile für Zeile in lokale Variablen geladen werden. Es ist oft die einzige Möglichkeit, etwas mit den zurückgegebenen Werten zu tun. Die Anzahl, Reihenfolge und Typen der Variablen müssen mit den Spalten in der Ausgabezeile übereinstimmen.

Eine „reineSELECT-Anweisung kann nur in PSQL verwendet werden, wenn sie höchstens eine Zeile zurückgibt, d.h. wenn es ein Singleton ist. Bei mehrzeiligen Auswahlmöglichkeiten bietet PSQL das FOR SELECT-Schleifenkonstrukt, das später im PSQL-Kapitel erläutert wird. PSQL unterstützt auch die Anweisung DECLARE CURSOR, die einen benannten Cursor an eine Anweisung SELECT bindet. Der Cursor kann dann verwendet werden, um die Ergebnismenge zu durchlaufen.

Syntax: In PSQL wird die INTO-Klausel am Ende des SELECT-Statements platziert.

SELECT [...] <column-list>
FROM ...
[...]
[INTO <variable-list>]

<variable-list> ::= [:]psqlvar [, [:]psqlvar ...]
        

Anmerkung

Das Doppelpunkt-Präfix vor lokalen Variablennamen in PSQL ist optional.

Beispiele

Einige aggregierte Werte auswählen und an zuvor deklarierte Variablen min_amt, avg_amt und max_amt übergeben:

select min(amount), avg(cast(amount as float)), max(amount)
  from orders
  where artno = 372218
  into min_amt, avg_amt, max_amt;

Anmerkung

Die CAST dient dazu, den Durchschnitt zu einer reellen Zahl zu machen; Sonst würde amount vermutlich ein Integer-Feld sein, SQL-Regeln würden es auf die nächste niedrigere Ganzzahl abschneiden.

Ein PSQL-Trigger der zwei Werte als BLOB zurückliefert (Verwendung der LIST()-Funktion) und diese mittels INTO einem dritten Feld zuweist:

select list(name, ', ')
  from persons p
  where p.id in (new.father, new.mother)
  into new.parentnames;

Common Table Expressions („WITH ... AS ... SELECT“)

Inhaltsverzeichnis

Rekursive CTEs

Verfügbar in: DSQL, PSQL

Ein allgemeiner Tabellenausdruck oder CTE kann als virtuelle Tabelle oder Ansicht beschrieben werden, die in einer Präambel einer Hauptabfrage definiert ist und nach der Ausführung der Hauptabfrage den Gültigkeitsbereich verlässt. Die Hauptabfrage kann auf alle CTEs verweisen, die in der Präambel definiert sind, als wären sie reguläre Tabellen oder Sichten. CTEs kann rekursiv sein, d.h. sich selbst referenzieren, aber sie können nicht verschachtelt sein.

Syntax: 

<cte-construct>  ::=  <cte-defs>
                      <main-query>

<cte-defs>       ::=  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte>            ::=  name [(<column-list>)] AS (<cte-stmt>)

<column-list>    ::=  column-alias [, column-alias ...]
            

Tabelle 6.10. Argumente für Common Table Expressions

Argument Beschreibung
cte-stmt Jede SELECT-Anweisung, einschließlich UNION
main-query Die Hauptanweisung SELECT, die sich auf die in der Präambel definierten CTEs beziehen kann
name Alias für einen Tabellenausdruck
column-alias Alias für eine Spalte in einem Tabellenausdruck


Beispiel: 

with dept_year_budget as (
  select fiscal_year,
         dept_no,
         sum(projected_budget) as budget
  from proj_dept_budget
  group by fiscal_year, dept_no
)
select d.dept_no,
       d.department,
       dyb_2008.budget as budget_08,
       dyb_2009.budget as budget_09
from department d
     left join dept_year_budget dyb_2008
       on d.dept_no = dyb_2008.dept_no
       and dyb_2008.fiscal_year = 2008
     left join dept_year_budget dyb_2009
       on d.dept_no = dyb_2009.dept_no
       and dyb_2009.fiscal_year = 2009
where exists (
  select * from proj_dept_budget b
  where d.dept_no = b.dept_no
)

CTE-Hinweise

  • Eine CTE-Definition kann ein beliebiges SELECT-Statement sein, sofern es keine „WITH...“-Präampel besitzt (keine Verschachtelung).

  • CTEs für die gleiche Hauptabfrage definiert sind, können sich gegenseitig referenzieren, es sollte jedoch darauf geachtet werden, Schleifen zu vermeiden.

  • CTEs kann von überall in der Hauptabfrage referenziert werden.

  • Jede CTE kann in der Hauptabfrage mehrmals referenziert werden, wobei bei Bedarf verschiedene Aliase verwendet werden.

  • Ein in Klammern eingeschlossenes CTE-Konstrukt kann als Unterabfrage in SELECT-Statements verwendet werden, aber auch in UPDATEs, MERGEs etc.

  • In PSQL, werden CTEs auch in FOR-Schleifen verwendet:

    for
      with my_rivers as (select * from rivers where owner = 'me')
        select name, length from my_rivers into :rname, :rlen
    do
    begin
      ..
    end
              

Wichtig

Wenn eine CTE deklariert ist, muss sie später verwendet werden: Andernfalls erhalten Sie einen Fehler wie diesen: 'CTE "AAA" is not used in query'.

Rekursive CTEs

Eine rekursive (selbstverweisende) CTE ist eine UNION, die mindestens ein nicht-rekursives Member haben muss, das als Anker bezeichnet wird. Die nicht rekursiven Member müssen vor den rekursiven Membern platziert werden. Rekursive Member sind untereinander und mit ihrem nicht rekursiven Nachbarn durch UNION ALL-Operatoren verknüpft. Die Verbindungen zwischen nicht-rekursiven Mitgliedern können beliebiger Art sein.

Für rekursive CTEs muss das Schlüsselwort RECURSIVE unmittelbar nach WITH vorhanden sein. Jedes rekursive Union-Member darf sich nur einmal selbst referenzieren, und zwar in einer FROM-Klausel.

Ein großer Vorteil von rekursiven CTEs ist, dass sie viel weniger Speicher und CPU-Zyklen als eine äquivalente rekursive gespeicherte Prozedur verwenden.

Ausführungsmuster

Das Ausführungsmuster einer rekursiven CTE lautet wie folgt:

  • Die Engine beginnt mit der Ausführung von einem nicht rekursiven Element.

  • Für jede ausgewertete Zeile beginnt die Ausführung jedes rekursiven Elements nacheinander, wobei die aktuellen Werte aus der äußeren Reihe als Parameter verwendet werden.

  • Wenn die aktuell ausgeführte Instanz eines rekursiven Members keine Zeilen erzeugt, führt die Ausführung eine Schleife zurück und ruft die nächste Zeile aus der äußeren Ergebnismenge ab.

Beispiele für CTEs: 

WITH RECURSIVE DEPT_YEAR_BUDGET AS (
  SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
  FROM PROJ_DEPT_BUDGET
  GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS (
  SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
  FROM DEPARTMENT
  WHERE HEAD_DEPT IS NULL
  UNION ALL
  SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
  FROM DEPARTMENT D
    JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
)
SELECT
    D.DEPT_NO,
    D.INDENT || D.DEPARTMENT DEPARTMENT,
    DYB_2008.BUDGET AS BUDGET_08,
    DYB_2009.BUDGET AS BUDGET_09
FROM DEPT_TREE D
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
      (D.DEPT_NO = DYB_2008.DEPT_NO) AND
      (DYB_2008.FISCAL_YEAR = 2008)
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
      (D.DEPT_NO = DYB_2009.DEPT_NO) AND
      (DYB_2009.FISCAL_YEAR = 2009)
              

Das nächste Beispiel gibt den Stammbaum eines Pferdes zurück. Der Hauptunterschied besteht darin, dass Rekursion gleichzeitig in zwei Zweigen des Stammbaums auftritt.

WITH RECURSIVE PEDIGREE (
    CODE_HORSE,
    CODE_FATHER,
    CODE_MOTHER,
    NAME,
    MARK,
    DEPTH)
AS (SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        CAST('' AS VARCHAR(80)),
        0
    FROM
        HORSE
    WHERE
        HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'F' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM
        HORSE
        JOIN PEDIGREE
      ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
        PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
        HORSE.CODE_HORSE,
        HORSE.CODE_FATHER,
        HORSE.CODE_MOTHER,
        HORSE.NAME,
        'M' || PEDIGREE.MARK,
        PEDIGREE.DEPTH + 1
    FROM
        HORSE
        JOIN PEDIGREE
      ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
        PEDIGREE.DEPTH < :MAX_DEPTH
)
SELECT
    CODE_HORSE,
    NAME,
    MARK,
    DEPTH
FROM
    PEDIGREE
              

Hinweise zu rekursiven CTEs: 

  • Aggregate (DISTINCT, GROUP BY, HAVING) und Aggregatfunktionen (SUM, COUNT, MAX etc) sind in rekursiven Union-Membern nicht erlaubt.

  • Eine rekursive Referenz kann nicht an einem Outer Join teilnehmen.

  • Die maximale Rekursionstiefe beträgt 1024.

Zurück: Statements der Data Manipulation Language (DML)Firebird Documentation IndexNach oben: Statements der Data Manipulation Language (DML)Weiter: INSERT
Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Manipulation Language (DML) → SELECT