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.

SELECT - step by step

The SQL command for querying data in a database is very powerfull and therefore can be quite complex. To adres sthat problem I provide here a small step by step tutorial how to build a SELECT command. May be it is easier for you to answer the following questions and to build so your custom SELECT command step by step:

  1. Which data do you need? In which data fields is this data stored?
  2. AT first you should know and, when you do not know, search, in which data fields of which database tables (or database views) the needed information is located/stored. In this example we want a relatively simple statistics about the customer count per salesman. The id and name of the salesman (vnum, vname) and the customer id (knum) should be enough for this purpose.

    SELECT vnum, vname, knum
    ;
  3. In which tables are the needed data fields stored?
  4. The name and id of the salesman (vnum, vname) are stored in the salesman table (tverkauf) and for the customer id (knum) the customer table (tkunden) seems to be most suitable.

    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, tkunden b
    ;
  5. What kind of relationship do the needed tables have to each other, how can they be joined?
  6. We have following knowledge about the two tables needed in this example:
    Not every salesman has customers (may be a new employee), so the data must be joined without loosing any salesman, even he has no corresponding customers: we need all records from the table tverkauf and search matching records from the table tkunden --> LEFT JOIN
    In the customer table (tkunde) we find the salesman id (vnum) and, because this is the primary key of the salesman table (tverkauf), we can use this data field for joining both of the tables together --> a.vnum = b.vnum

    SQL92::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    ;

    When you omit LEFT JOIN or the OUTER, then you get only salesman, which have at least one corresponding record in the customer table (tkunden), so we get only salesman with customers.
    When you omit the join criteria a.vnum=b.vnum, then you get a so called cartesian product - the combination of all salesman with all customers, in our case quite a bunch of data vaste.

  7. Do you need all of the data or do you have to filter it?
  8. Let us assume, we are interested only on salesman from Berlin:

    SQL92::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    AND a.stadt='Berlin'
    ;
  9. Do you need the original record fields or is a statistics, an aggregation of the original data needed here?
  10. Because we want to know, how much custimers do each salesman have, we should count the customers per salesman --> COUNT.
    That means, we have to aggregate, to group data --> GROUP BY

    SQL92::
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, COUNT(*) AS anzahl
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    AND a.stadt='Berlin'
    GROUP BY vnum, vname
    ;
  11. Is all of the resulting aggregated statistics data needed?
  12. Let as futher assume, we are not interested in good salesman with lots of customers. And let us assume, that a good salesman have 100 or more customers. Then we have to choose only salesman with less than 100 customers:

    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    ;
  13. Should the query results be sorted?
  14. In our search for bad salesman we probably want to see salesman with less customers first. So we have to order the results by the customer count ascending:

    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) ORDER BY anzahl ASC
    ;
  15. Should the amount of the resulting data be limited to a specific number of records?
  16. Because we do not want to talk with every laisy employee, we probably want to limit the amount of the resulting data records. Let us set the limit to 10 records:

    SQL92::
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) LIMIT 10
    ;
    Informix::
    SELECT FIRST 10 a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    
  17. Should the query results be stored in a new or temporary table?
  18. When the resulting data have to be processed in some you sometimes want to have it in a handy (temporary) database table, so you can easily use ot in other, futher database query. So the salesmanager can probably decide by him self, how much employees he want to speak. In this case we need to remove the record limit from our select query and write the records into a new (temporary) table:

    Temporäre Tabelle
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) INTO TEMP verkauf_score
    ;
    Regular, existing table (must be created before with CREATE TABLE)
    INSERT INTO verkauf_score
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    
Categories: 

INNER JOIN - normal table join

Syntax

    WHERE a.Field1 = b.Field2
    before SQL92 (implicit declaration of table joins)

    FROM Table1 [Alias1] INNER JOIN Table2 [Alias2] ON {Table1|Alias1}.TableKey = {Table2|Alias2}.TableKey
    SQL92 (explicit declaration of table joins)

Description

    The inner or equal join is the "normal" way for joining two tables: the records of two tables are joined, when the join expression build upon the fields from both tables evaluates as true.

    Example:

-- before SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE a.vnum = b.vnum
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a INNER JOIN tkunden b ON a.vnum=b.vnum
ORDER BY vnum;

Result:

vnum vname   knum kname
1    Mueller 1    Lehmann
1    Mueller 2    Schmidt
1    Mueller 3    Schumacher
2    Meyer   4    Schroeder
2    Meyer   5    Stoiber
3    Schulz  6    Lohmann
3    Schulz  7    Krupp
3    Schulz  8    Stradivari
3    Schulz  9    Schumacher
Categories: 

LEFT JOIN - left outer join

Syntax

    FROM Table1 [Alias1], OUTER Table2 [Alias2]
    WHERE a.Field1 = b.Field2

    Informix, before SQL92 (implicit declaration of table join)

    WHERE (a.Field1 = b.Field2(+))
    Oracle, before SQL92 (implicit declaration of table join)

    FROM Table1 [Alias1] LEFT JOIN Tabelle2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit join declaration)

Description

    The result of a left join (or left outer join) includes all data from the left/normal table, data from the right /outer table is included, when the specifyed join expression evaluates as true.

    Example:

-- Informix
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, OUTER tkunden b
WHERE a.vnum = b.vnum
ORDER BY vnum;

-- Oracle
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE (a.vnum = b.vnum(+))
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
ORDER BY vnum;

Result:

vnum vname       knum kname
1    Mueller     1    Lehmann
1    Mueller     2    Schmidt
1    Mueller     3    Schumacher
2    Meyer       4    Schroeder
2    Meyer       5    Stoiber
3    Schulz      6    Lohmann
3    Schulz      7    Krupp
3    Schulz      8    Stradivari
3    Schulz      9    Schumacher
4    Burgmueller 

In addition to a normal or equal or inner join, there are the salesman (values from table tverkauf) with vnum=4 in the result, which has no matching records in the table tkunden.

Categories: 

RIGHT JOIN - right outer join

Syntax

    FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE a.Field1 = b.Field2

    Informix, before SQL92 (implicit join declaration)

    WHERE (a.Field1(+) = b.Field2)
    Oracle, before SQL92 (implicit join declaration)

    FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit join declaration)

Description

    The result of a right join (or right outer join) includes all data from the right/normal table, data from the left/outer table is only included, when the specifyed join expression evaluates as true.

    Beispiel:

-- Informix
SELECT a.vnum, a.vname, b.knum, b.kname
FROM OUTER tverkauf a, tkunden b
WHERE a.stadt = b.stadt
ORDER BY vnum;

-- Oracle
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE (a.stadt(+) = b.stadt)
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a RIGHT JOIN tkunden b ON a.stadt = b.stadt
ORDER BY vnum;

Result:

vnum vname   knum kname
1    Mueller 1    Lehmann
1    Mueller 2    Schmidt
1    Mueller 4    Schroeder
3    Schulz  5    Stoiber
             3    Schumacher
             6    Lohmann
             7    Krupp
             8    Stradivari
             9    Schumacher
             10   Testcustomer

List of all customers with corresponding salespeople, but only when they live in the same city.

Categories: 

FULL JOIN - two sided outer join

Syntax

    FROM Table1 [Alias1] FULL JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit declaration of a full or union join)

    SELECT Fieldlist FROM Table1 [Alias1] LEFT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    SQL92 (if a direct declaration of a full or union join is not supported by the database system)

    SELECT Fieldlist FROM Table1 [Alias1], OUTER Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    Informix before SQL92 (implicit join declaration)

    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1 = {Table2|Alias2}.Field2(+)) UNION
    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1(+) = {Table2|Alias2}.Field2)

    Oracle before SQL92 (implicit join declaration)

Description

    The full or the full outer join includes all records from both tables in one resulting row, where the specifyed join expression evaluates as true. Additionally all records from the left table, where is no match in the right table, and all records from the right table, where is no match in the left table, are included in the query results.

    Attention, if the database system does not directly support a full join (Syntax 1), all matching records in both tables are selected in the first select statement with the left join and then are selected a second time in the second select statement with the right join. At the and duplicate rows are eliminated with UNION (without ALL, this is importand).

    Example:

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a FULL JOIN tkunden b ON a.vnum = b.vnum
ORDER BY vnum;

Ergebnis:

vnum vname       knum kname
                 10   Testcustomer
1    Mueller     1    Lehmann
1    Mueller     2    Schmidt
1    Mueller     3    Schumacher
2    Meyer       4    Schroeder
2    Meyer       5    Stoiber
3    Schulz      6    Lohmann
3    Schulz      7    Krupp
3    Schulz      8    Stradivari
3    Schulz      9    Schumacher
4    Burgmueller 		

List of all salesman (tverkauf) and customers (tkunde), matching salesman and customers are in the same result record, salesman without customers and customers wothout salesman are uncluded without their counterparts.

Categories: 

CROSS JOIN - kartesian product

Syntax

    SELECT {Table1|Alias1}.Field1 [,...] , {Table2|Alias2}.Field2 [,...]
    FROM Table1 [Alias1], Table2 [Alias2]

    before SQL92 (no join declaration at all)

    FROM Table1 [Alias1] CROSS JOIN Table2 [Alias2]
    SQL92 (explicit declaration of a cross join)

Desciption

    The cross join or the so called kartesian product involves no record match at all, all posible combinations of all records from both tables are included in the query results.

    Example:

-- before SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a CROSS JOIN tkunden b
ORDER BY vnum;

Result:

vnum	vname	knum	kname
1	Mueller 	1	Lehmann
1	Mueller 	2	Schmidt
1	Mueller 	3	Schumacher
1	Mueller 	4	Schroeder
1	Mueller 	5	Stoiber
1	Mueller 	6	Lohmann
1	Mueller 	7	Krupp
1	Mueller 	8	Stradivari
1	Mueller 	9	Schumacher
1	Mueller 	10	Testcustomer
2	Meyer 	1	Lehmann
2	Meyer 	2	Schmidt
2	Meyer 	3	Schumacher
2	Meyer 	4	Schroeder
2	Meyer 	5	Stoiber
2	Meyer 	6	Lohmann
2	Meyer 	7	Krupp
2	Meyer 	8	Stradivari
2	Meyer 	9	Schumacher
2	Meyer 	10	Testcustomer
3	Schulz 	1	Lehmann
3	Schulz 	2	Schmidt
3	Schulz 	3	Schumacher
3	Schulz 	4	Schroeder
3	Schulz 	5	Stoiber
3	Schulz 	6	Lohmann
3	Schulz 	7	Krupp
3	Schulz 	8	Stradivari
3	Schulz 	9	Schumacher
3	Schulz 	10	Testcustomer
4	Burgmueller 	1	Lehmann
4	Burgmueller 	2	Schmidt
4	Burgmueller 	3	Schumacher
4	Burgmueller 	4	Schroeder
4	Burgmueller 	5	Stoiber
4	Burgmueller 	6	Lohmann
4	Burgmueller 	7	Krupp
4	Burgmueller 	8	Stradivari
4	Burgmueller 	9	Schumacher
4	Burgmueller 	10	Testcustomer
Categories: