[Home]  [Edit this page]  [Recent Changes]  [Special Pages]  [Help] 

---

SQLSelect

---

(SQL) SELECT

The SELECT statement is used to interogate a database. It returns the data that was found based upon the query.

SELECT queries take the general form:-

SELECT fields
FROM table(s)
WHERE conditions
ORDER BY fields
GROUP BY fields
LIMIT Offset, RecordsToReturn

Most queries only involve the SELECT, FROM and WHERE clauses. Generally, SELECT and FROM are compulsary (may be exceptions in special cases), and WHERE can be omitted if you just want all records in the table(s) specified to be returned.

SELECT

When writing a SELECT query, the first thing to write is SELECT. Then a list of fields that should be fetched can be specified. Note that if a query spans multiple tables it is a good idea to write these in the form tablename.fieldname as two tables invovled in the query may both have a field with the same name, which will cause the query to error out. If the query just selects from one table, these can be safely left out.

 ?FROM

This clause specifies what tables the query is based upon. For one table it is simple, you just write the name of the table. If there are multiple tables then they need to be joined, and unless the relationships have already been defined then it will normally be required to specify what fields to join them on. See joining tables for more information.

 ?WHERE

This clause defines the conditions that certain fields must meet for that record to be returned. There are a number of different comparissons you can do:-

• field = 'value' - Checks if the field contains the value inside the apostrophes, returns true if it is. If it's not a text based field (e.g. numerical), don't use the apostrophes.
• field > 10 - Returns true if the value of the field is greater than 10.
• field < 10 - Returns true if the value of the field is less than 10.
• field >= 10 - Returns true if the value of the field is greater than or equal to 10.
• field =< 10 - Returns true if the value of the field is less than or equal to 10.

Example 1

SELECT name, empno
FROM emp
WHERE name LIKE 'John%';
NAME         EMPNO
John Smart   10
John Black   40

ORDER BY Clause

This sorts the records that were found. When used in conjunction with LIMIT you can return the top X items; other times it's useful just for sorting. You can sort in ascending or descending order (ascending is default). You can also specify multiple sorts to be applied. The first sort will be applied first, then if there are items that were equal after that first sort, the second will be applied and so on. An example of a query involving the ORDER BY clause is shown below.

SELECT name, street, city, state, country
FROM restaurants
WHERE country = 'USA' AND state = 'Alabama'
ORDER BY city, street

This will return all the restaurants in Alabama, USA, and they will be sorted first by what city they are in, then those in each city will be sorted by what street they are on.

LIMIT

The LIMIT clause limits the search.

-- Select one row from a database
SELECT * FROM database
LIMIT 1

From http://www.petefreitag.com/item/466.cfm this addition:

-- Select one random row from a database with MySQL
SELECT * FROM database
ORDER BY RAND()
LIMIT 1

-- Select a random row with PostgreSQL
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

-- Select a random row with Microsoft SQL Server
SELECT TOP 1 column FROM table
ORDER BY NEWID()

-- Select a random row with IBM DB2
SELECT column FROM table
ORDER BY RAND()
FETCH FIRST 1 ROWS ONLY

-- Select a random record with Oracle
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

[Edit this page]  [Page history]  [What links here]  [Discuss this topic]  [Printer Friendly]