This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The SELECT statement retrieves data from one or more Tables. Here is the required syntax for a simple SELECT statement (or, as the SQL Standard calls it, a <query specification>):

SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]                                     
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
 INTO DUMPFILE 'file_name' |
 INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]

The SELECT statement queries your SQL-data: it returns a results Table derived from the Tables referred to in the FROM clause. The derivation of the result Table can be described as a sequence of operations in which the result of each operation is input for the next. The sequence of the operations is FROM, then WHERE, then GROUP BY, then HAVING, then the select list (that is, the list of Column expressions) and the descriptions of the clauses that follow appear in this order.

FROM clause

The FROM clause supplies a list of <Table references> for the query. A <Table reference> is any expression which results in a Table but is usually just a <Table name> or a <Correlation name> that identifies a Table that contains SQL-data you want to query. <Column name>s throughout the SELECT statement must be unambiguous; that is, they must be qualified with a <Correlation name> if one was defined for the Table that owns them, and with their <Table name> if SELECT is retrieving data from multiple Tables which have Columns with identical names. The required syntax for the FROM clause is as follows.

FROM <Table reference> [ {,<Table reference>} ... ]

Table reference

Several times throughout this book, we make the comment that the result of an SQL query is a Table. To understand the entire syntax that you may use to formulate a query then, you'll have to start with what a Table is and we've already shown you that a Table is a set of Columns and row. Up until now, though, whenever we've talked about Tables, we've referred to them only with a <Table name>. Since the result of a query is a Table derived by evaluating that query, not all SQL Tables have an explicit <Table name> so SQL allows you to refer to any Table using a <Table reference>. The required syntax for a <Table reference> is as follows.

<Table reference> ::=
[ ONLY ]{<Table name> | <query name>} [ [ AS ] <Correlation name> [ (<derived Column list>) ] ] |
<Table subquery> [ AS ] <Correlation name> [ (<derived Column list>) ] |
<joined Table> |
LATERAL (<query expression>) [ AS ] <Correlation name> [ (<derived Column list>) ]

   <derived Column list> ::=
   <Column name> [ {,<Column name> }... ]

A <Table reference> is simply a reference to some Table: this may be a reference to a named Table (that is, a <Table name> that identifies a Base table or a View) or a reference to a Table returned by a query. Thus there are five possible options for a <Table reference>:

  1. It can refer to a Base table or a View using a <Table name>. The optional <keyword> ONLY in front of such a reference can only be used if the reference is to a typed Table. In that case, the <Table reference> refers to every row of the Table (or result Table), except for any rows that have a subrow in a proper subtable of that Table.
  2. It can refer to a result Table using a <query name>.
  3. It can refer to the result of a Table subquery.
  4. It can refer the result of a join of multiple Tables.
  5. It can refer to a lateral Table: the result of a parenthesized <query expression> preceded by the <keyword> LATERAL.

In each case, you can optionally provide a <Correlation name> for the Table being referred to, as well as explicit names for each of the Columns belonging to the Table being referred to (the names specified must, of course, be unique for that reference).

If you want to restrict your code to Core SQL, don't use a <query name> to make up a <Table reference> and don't use the <keyword> ONLY to make up a <Table reference> that refers to a typed Table.

WHERE clause

The optional WHERE clause is used to set the retrieval conditions for rows. Any rows that don't fall into the guidelines specified are eliminated from the results Table. The search conditions specified may include the arithmetic and Boolean operators, the SQL predicates (e.g.: comparison, BETWEEN, LIKE) and the SQL scalar functions, as well as parentheses to set the desired evaluation order. The required syntax for the WHERE clause is as follows.

WHERE <search condition>

GROUP BY clause

The optional GROUP BY clause logically rearranges the interim result returned by the WHERE clause into groups. The result is a set of rows where each common datum is gathered into one group. That is, within a group, all values of a grouping Column are the same value. For grouping, all NULLs are considered equal: they form one group. Because every row that contains a group contains the same value for that group, the name of a grouping Column can be used in a condition for the HAVING clause or to identify a result Column in the select list. We'll show you the required syntax for the GROUP BY clause in Chapter 33 "Searching with Groups".

HAVING clause

The optional HAVING clause is used to set the retrieval conditions for groups. Any groups that don't fall into the guidelines specified are eliminated from the results Table. The search conditions specified may include the arithmetic and Boolean operators, the SQL predicates (e.g.: comparison, BETWEEN, LIKE) and the SQL scalar functions, as well as parentheses to set the desired evaluation order. HAVING is normally applied to the interim result returned by the GROUP BY clause. If a SELECT statement doesn't include a GROUP BY clause, then HAVING treats all rows of the interim result as a single group. We'll show you the required syntax for the HAVING clause in Chapter 33 "Searching with Groups".

LIMIT clause

LIMIT can be used to restrict which rows should be returned. LIMIT takes one of two arguments: [OFFSET,] ROW_COUNT. With one argument you specify number of rows, with 2 arguments you first specify the OFFSET (starting from 0) and then the number of rows. This means that 'LIMIT 5' is the same as 'LIMIT 0,5'.

MariaDB also supports the 'LIMIT row_count OFFSET offset' syntax (same syntax as PostgreSQL).

If LIMIT is used within a sub-query and also for the outer query, the outermost LIMIT takes precedence.

(SELECT ... LIMIT 1) LIMIT 2;

The above will return up to 2 rows, not 1 row.

SELECT LIST clause

The select list produces a final results Table by selecting only those Columns (or Column expressions) specified. The select list may include <Column name>s, <Column reference>s, Column expressions (that is, any expression which evaluates to a single Column, such as a scalar subquery) or an asterisk, as well as one of the <keyword>s DISTINCT or ALL. (The asterisk is a shorthand for a list of all the Columns of the Tables named in the FROM clause. The DISTINCT option ensures that duplicate rows are eliminated from the result. The ALL option, which is the default, ensures that duplicate rows are included in the result.) A Column expression can be a <literal>, a scalar function or some other expression derived from the Columns whose values you want to retrieve but may not include any expression that will evaluate to a Column with a BLOB, CLOB, or NCLOB <data type> if DISTINCT is specified. You can use the optional AS name clause to specify a name for a Column expression; it will be used to identify that result for the entire SELECT statement. The required syntax for a select list is as follows.

SELECT [ ALL | DISTINCT ] Column list

   Column list ::=
   expression [ [ AS ] <Column name> ] [ , ... ] |
   *

Let's try some SELECT examples on a small group of Tables. They look like this (a question mark in a Column represents a null value):

DEPARTMENT

DEPTMANAGERFUNCTIONCODE
ASMITH AACCOUNTING1
BJONES BINF SYSTEMS2
CBROWN CCUST REL3
DBLACK DOPERATIONS4
EGREEN ESALES5

EMPLOYEE

EMPNUMDEPTSURNAMEGNAMEADDRESS
1AKOOSARA234 WEST
2BMARSHJOHN456 EAST
3CJONESMABEL567 NORTH
4DMORGANCHUCK963 SOUTH
10ASMITHALICE234 WEST
11BJONESBOB325 RIVER
20EFRANCISCHRIS861 BERLIN
28BTURNERLINDA114 ROBIN
35EOLSENCAROL555 RIVER
40BWARRENNANCY?

PAYROLL

EMPNUMRATELOCATIONPAIDAPPT
16.0010TH FLOOR1989-10-3110:15:00
25.0016TH FLOOR1989-09-3010:20:00
35.00WAREHOUSE1989-09-3010:30:00
48.00BASEMENT1989-10-1512:00:10
1016.0016TH FLOOR1989-09-3012:30:00
1116.0016TH FLOOR1989-10-1513:15:10
209.00WAREHOUSE1989-10-1514:00:00
28?16TH FLOOR1989-09-1514:10:00
359.0010TH FLOOR1989-10-3114:20:00
4016.0010TH FLOOR1989-10-3114:35:07

Simple retrieval

To find all departments with employees (retrieve a single Column from a Table), the following SQL statements are equivalent:

SELECT dept FROM Employee;
SELECT ALL dept FROM Employee;
SELECT Employee.dept FROM Employee;
SELECT ALL Employee.dept FROM Employee;

The first two examples use unqualified <Column name>s in the select list, while the last three use <Column reference>s (that is, <Column name>s qualified with their <Table name>s). Unless the lack of a qualifier makes a <Column name> ambiguous, the qualifier is unnecessary. The result in all cases is:

DEPT
A
B
C
D
A
B
E
B
E
B

Departments are duplicated in the result because SELECT doesn't eliminate them unless the DISTINCT option is used, as in these equivalent SQL statements:

SELECT DISTINCT dept FROM Employee;
SELECT DISTINCT Employee.dept FROM Employee;

The result in both cases is:

DEPT
A
B
C
D
E

To find the name of each department's manager (retrieve multiple Columns from one Table):

SELECT dept,manager FROM Department;

The result is:

DEPTMANAGER
ASMITH A
BJONES B
CBROWN C
DBLACK D
EGREEN E

To retrieve all Columns of one Table, these three SQL statements are equivalent:

SELECT empnum,rate,location,paid FROM Payroll;
SELECT * FROM Payroll;
SELECT Payroll.* FROM Payroll;

The result in all three cases is the entire PAYROLL Table. (An asterisk can be used as shorthand for "all Columns" and can be qualified just as a <Column name> can be.)

Qualified retrieval

To find all employees working in department A (retrieve one Column which fulfills one search condition):

SELECT surname FROM Employee WHERE dept='A';

The result is:

SURNAME
KOO
SMITH

Remember that <character string literal>s must always be enclosed in single quotes.

To find department A employees with an employee number smaller than 10 (retrieve one Column fulfilling multiple search conditions):

SELECT surname FROM Employee
WHERE  dept='A' AND empnum<10;

The result is:

SURNAME
KOO

To find the full name of the department A employee whose employee number is 10 (retrieve multiple Columns fulfilling multiple conditions from a Table):

SELECT gname,surname FROM Employee
WHERE  dept='A' AND empnum=10;

The result is:

GNAMESURNAME
ALICESMITH

Retrieval with a <literal>

To include a <literal> in a result:

SELECT empnum,
      'Hourly Rate=' AS hourly_rate,
      rate
FROM   Payroll
WHERE  empnum=1 OR empnum=10;

The result is:

EMPNUMHOURLY_RATERATE
1Hourly Rate=6.00
10Hourly Rate=16.00

The second Column of the result is derived from the <character string literal> expression in the select list.

Retrieval with an arithmetic expression

To calculate an employee's daily pay from the hourly rate earned (retrieve multiple Columns from a Table with an arithmetic expression):

SELECT empnum,
      'Daily Rate=' AS comment,
      rate*8 AS daily_rate
FROM   Payroll
WHERE  empnum=1 OR empnum=10;

The result is:

EMPNUMCOMMENTDAILY_RATE
1Daily Rate=48.00
10Daily Rate=128.00

The third Column of the result is derived from the arithmetic expression in the select list.

Retrieval with LIKE

To find all employees with surnames beginning with "M" (retrieve all values matching a simple string pattern):

SELECT empnum,surname FROM Employee
WHERE  surname LIKE 'M%' AND empnum<3;

The result is:

EMPNUMSURNAME
2MARSH

To find the departments whose manager's surname has the letter "R" as the second character:

SELECT dept,manager FROM Department
WHERE  surname LIKE '_R%';

The result is:

DEPTMANAGER
CBROWN C
EGREEN E

To find all employees whose given name does not include the letter "A" (retrieve values which do not match a simple string pattern):

SELECT empnum,gname FROM Employee
WHERE  gname NOT LIKE '%A%';

The result is:

EMPNUMGNAME
2JOHN
4CHUCK
11BOB

(We discussed the LIKE predicate in Chapter 7 "Character Strings".)

Retrieval with SIMILAR

To find all employees whose location starts with 2 digits (retrieve all values matching a complicated string pattern):

SELECT empnum,location FROM Payroll
WHERE  location SIMILAR TO '[:DIGIT:][:DIGIT:]%';

The result is:

EMPNUMLOCATION
110TH FLOOR
216TH FLOOR
1016TH FLOOR
1116TH FLOOR
2816TH FLOOR
3510TH FLOOR
4010TH FLOOR

To find all employees whose location doesn't start with 2 digits (retrieve all values that don't match a complicated string pattern):

SELECT empnum,location FROM Payroll
WHERE  location NOT SIMILAR TO '[:DIGIT:][:DIGIT:]%';

The result is:

EMPNUMLOCATION
3WAREHOUSE
4BASEMENT
20WAREHOUSE

(We discussed the SIMILAR predicate in Chapter 7 "Character Strings".)

Retrieval with IS NULL

To find all employees with unknown addresses on file (retrieve all rows containing a null value):

SELECT empnum,surname,gname
FROM   Employee
WHERE  address IS NULL;

The result is:

EMPNUMSURNAMEGNAME
40WARRENNANCY

To find the departments with known managers (retrieve all rows that don't contain null values):

SELECT manager FROM Department
WHERE  manager IS NOT NULL;

The result is:

MANAGER
SMITH A
JONES B
BROWN C
BLACK D
GREEN E

(We discussed the IS NULL predicate in Chapter 13 "NULLs".)

Retrieval with a Scalar Function

To concatenate an employee's first initial and surname:

SELECT empnum,
      SUBSTRING(gname FROM 1 FOR 1) || '. ' || surname AS fullname
FROM   Employee
WHERE  empnum=10;

The result is:

EMPNUMFULLNAME
10A. SMITH

To concatenate the values retrieved from a Column with a <literal>:

SELECT 'HELLO ' || gname AS greeting
FROM   Employee
WHERE  empnum=4;

The result is:

GREETING
HELLO CHUCK

To find the length of a Column value and a <literal>:

SELECT surname,
      CHAR_LENGTH(surname) AS surname_length,
      CHAR_LENGTH('MARY') AS literal_length
FROM   Employee
WHERE  dept='A';

The result is:

SURNAMESURNAME_LENGTHLITERAL_LENGTH
KOO34
SMITH54

(The CHAR_LENGTH function returns a character string's length inclusive of blanks and trailing zeros. This example assumes that SURNAME is a variable length Column.)

Retrieval using date arithmetic

To find the number of days since the last pay date (assume the current date is November 10, 1989):

SELECT paid,
      (DATE '1989-11-10' - paid) INTERVAL DAY AS last_paid
FROM   PAYROLL
WHERE  empnum=1;

The result is:

PAIDLAST_PAID
1989-10-3110

To add three months and two days to the last pay date:

SELECT empnum,
      paid,
      ((paid + INTERVAL '3' MONTH) + INTERVAL '2' DAY) AS new_date
FROM   PAYROLL
WHERE  empnum=1;

The result is:

EMPNUMPAIDNEW_DATE
11989-10-311990-02-02

Joins

The ability to join a Table to others is one of the most powerful features of SQL. A join is an operation in which data is retrieved from multiple Tables. Here are some examples.

To find all information available on all employees (retrieve a join of all Columns) the following SQL statements are equivalent:

SELECT Employee.*,Payroll.*
FROM   Employee,Payroll
WHERE  Employee.empnum=Payroll.empnum;

SELECT *
FROM   Employee,Payroll
WHERE  Employee.empnum=Payroll.empnum;

The result is the entire EMPLOYEE Table joined with the entire PAYROLL Table over their matching employee numbers; ten rows and ten columns in all. Note the <Column reference>s for the EMPNUM Column, necessary to avoid ambiguity. To eliminate duplicate Columns from the result, specific <Column reference>s (rather than *) must also be listed in the select list, as in this SQL statement:

SELECT Employee.empnum,dept,surname,rate,location
FROM   Employee,Payroll
WHERE  Employee.empnum=1 AND Employee.empnum=Payroll.empnum;

The result is:

EMPNUMDEPTSURNAMERATELOCATION
1AKOO6.0010TH FLOOR

To find an employee's manager (retrieve one Column from multiple Tables):

SELECT surname,manager
FROM   Employee,Department
WHERE  empnum=28 AND Employee.dept=Department.dept;

The result is:

SURNAMEMANAGER
TURNERJONES B

To find the pay rates and locations of all department A employees (join values fulfilling multiple conditions from multiple Tables):

SELECT Employee.*,Payroll.*
FROM   Employee,Payroll
WHERE  dept='A' AND Employee.empnum=Payroll.empnum;

The result is the EMPLOYEE Table joined with the PAYROLL Table, for all rows where the DEPT column contains 'A'.

To find the department and payroll data for employee 35:

SELECT Employee.empnum,surname,Employee.dept,manager,rate
FROM   Employee,Department,Payroll
WHERE  Employee.empnum=35 AND
       Employee.empnum=Payroll.empnum AND
       Employee.dept=Department.dept;

The result is:

EMPNUMSURNAMEDEPTMANAGERRATE
35OLSENEGREEN E9.00

To find the manager and locations of department C's employees:

SELECT Department.dept,manager,location
FROM   Department,Payroll,Employee
WHERE  Department.dept='C' AND
       Department.dept=Employee.dept AND
       Employee.empnum=Payroll.empnum;

The result is:

DEPTMANAGERLOCATION
CBROWN CWAREHOUSE

Comments

Comments loading...