You are here

Add new comment

SELECT - retrieve data

-- english, deutsch, russian --

Syntax

Description:

    SELECT-commands get data from the tables of one or, in special cases, more than one database. SELECT returns a result-data-table, which is created from the data in the specifyed tables of the current database. You can get with the SELECT-command only some records from one database table, if you want, or sum them up, or you can create a new result table from different database tables or even databases on the same system.

DISTINCT

    DISTINCT tells the DBMS to select only unique records. Redundant data would not be selectted in this case (the same record would not be included more than once).

    Example:

SELECT DISTINCT vnum
 FROM torder;

Selects all unique values of the column vnum from the table torder.

FROM { Tablename [ [AS] Alias ] } [,...]

    FROM tells the DBMS from which tables the data must be retrieved.

    Example:

SELECT vnum
 FROM torder;

Selects all values of the whole column vnum from the table torder..

WHERE logical construct

    With WHERE you can decrease the selected amount of data or you can specify in which way multiple tables are joined.

    Example:

SELECT tkunden.knum, tverkauf.vnum, tverkauf.prov
 FROM tkunden, tverkauf
 WHERE tkunden.vnum=tverkauf.vnum;

Here is an resulting table with the column knum from the table tkunden and the columns vnum and prov from table tverkauf created, where the logical construct of the WHERE-clause specifyes the way in which the correct record from second table is selected.

GROUP BY { Fieldname | Integer } [,...]

    GROUP BY is always used with data aggregation functions to let them work only on specifyed groups of records. Instead of explicit using of an fieldname the number of the field in the column list can be used.

    Example:

SELECT vnum, MAX(preis)
 FROM torder
 GROUP BY vnum;

Selects only the maxumum values of the field preis from each record group with the same vnum.

HAVING logical construct

    HAVING is used only with GROUP when the data aggregation functions shall work only in groups of records, which fullfill the logical construct.

    Example:

SELECT vnum, MAX(preis)
 FROM torder
 GROUP BY vnum
 HAVING AVG(preis) > 10

Selects only the maximum values of the field preis from each record group with the same vnum, where the average of preis is greater than 10.

ORDER BY { Fieldname | Integer [ ASC|DESC ] } [,...]

    ORDER BY sorts the records of the resulting table. When ORDER BY is used in the GROUP BY statement, then the records in the record groups will be sorted too. Instead of explicit using of an fieldname the number of the field in the column list can be used. ASC sorts the records in ascending order, DESC in descending.

    Example:

UNION [ALL] SELECT-Command

    UNION is used to merge the results of two or more SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With UNION ALL all records would be included into the result.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
 UNION
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman (from the table tverkauf) and cutomers (from the table tkunden) living in Berlin selected. Only unique records are included in the resulting table, which is sorted in ascending order by the first column.

INTERSECT [ALL] SELECT-Command

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes yes yes ? ?

    INTERSECT is used to merge the results of two or more SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With INTERSECT only records find in all results of the SELECT commands are included into the resulting table.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
INTERSECT
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman selected, that are customers too. The result is sorted by the first column. Only unique records are selected.

EXCEPT [ALL] SELECT-Command

    whichtig: in einigen SQL-Dialekten wird anstatt EXCEPT - MINUS oder DIFFERENCE verwendet, die Syntax bleibt aber gleich.

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes ? ? ? ?

    EXCEPT is used to merge the results of two SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With EXCEPT are only the records included into the resulting table, which are in the first SELECT and not in the second SELECT.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
EXCEPT
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman secelted, which are not customers too. The result is sorted by the first column. Only unique records are selected.

INTO TEMP Tablename

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes yes ? ? ?

    INTO TEMP is used to create temporary database tables, which are deleted after the SQL-session. Is often used to optimize SQL queries.