SQL Select

The SELECT instruction is one of the most basic instructions in SQL. The select instruction is used to select data from database tables. It is possible to filter the selected data on several criteria. It is also possible to alter the way the data is sorted and the way the result set is displayed. The structure of the SELECT instruction is the following:

  <select-component>
  <from-component>
[ <where-component> ]
[ <group-by-component>
[ <having-component> ] ]
[ <order-by-component> ]


When using the select instruction you have to keep in mind the following things:

• Each SELECT instruction consists of at least two components: The SELECT and the FROM component. The other components such as WHERE, GROUP BY and ORDER BY are not mandatory.
• The order of the components cannot be changed. For instance: a GROUP BY component may never be in front of the SELECT component and an ORDER BY component is always the last component when it is used.
• A HAVING component can only be used if a GROUP BY component is used.

Examples

SELECT * FROM USERS
This query will select all data from the USERS table and display it.

SELECT USER_NAME FROM USERS
This query will select all user names from the USERS table for display.

SELECT USER_NAME FROM USERS
WHERE USER_ID = 5302
This query will select the user name of the user with unique ID 5302 from the USERS table for display.

SELECT USER_NAME FROM USERS
ORDER BY USER_NAME DESC
This query will select all user names from the USERS table and order the results in a descending order taking the USER_NAME column as sorting criteria.

SELECT * FROM USERS
WHERE USER_ID < 5302
ORDER BY USER_NAME ASC
This query will select all user names of users with an unique ID smaller than 5302 from the USERS table and order the results in a ascending order taking the USER_NAME column as sorting criteria.

SELECT USER_NAME FROM USERS
WHERE USER_ID >= 5302
AND USER_EMAIL LIKE '%.com'
ORDER BY USER_NAME
This query will select all user names of users with an unique ID of 5302 or higher and with an e-mail address ending with ".com" from the USERS table and order the results in a ascending order taking the USER_NAME column as sorting criteria. Note that the default order for sorting is ascending.

SELECT USER_NAME FROM USERS
WHERE USER_ID BETWEEN 5302 AND 6000
ORDER BY USER_STATUS, ID
This query will select all user names of users with an unique ID between 5302 and 6000. The results have to be ordered by the status of the users and the user's unique ID has to be the second criteria for the ordering. This second criteria will be used in a case that at least two of the selected users will have the same status. To determine the order of all users with the same status the user with the smallest ID will be displayed first. Remember that the default order for sorting is ascending.

SELECT * FROM USERS
WHERE USER_NAME LIKE '%SON%'
This query will select everything in the USERS table for the users with a user name in which the string "SON" can be found. So user names like JOHNSON, JACKSON, SONNY and ASONDER will appear in this list if they are present in the USERS table.

Important note: These examples are just meant to give an indication what is possible to do with SQL select statements. There is much more possible than is displayed here.

See Also

• SQL Section
• Create Statement
• Delete Statement
• Drop Statement
• Insert Statement
• Update Statement

Contact the author: Send an electronic mail to: pajtroon@dds.nl.
Peter's ICQ Number is: #3900785.

This page: Copyright © 2002 Peter A. J. Troon

Note: This page is part of the Peter Troon Site.