SQL - SELECT Statement
CIW Course in a Nutshell
SQL SELECT
The SELECT keyword is the SQL statement used to return records from a database. It is likely that this will be the most used statement you will utilise when dealing with a relational database. The SELECT statement is used to construct a QUERY to return SELECTed data from a table in your database.
SELECT *
FROM MyTable
SELECT * FROM MyTable
SQL treats spaces and carriage returns as white space so you may use either in your statements. So the above two examples are equivalent. The asterisk (*) is a wild card used in the column list to denote ALL columns.
SELECT Column-List
FROM Tablename
WHERE Where-Clause
The basic syntax is shown above, where column list is a list of column, or field, names to be returned. Column List, Tablename and Where Clause are all expressions, and each can be a SQL statement in it's own right, so SQL statements may be nested within other SQL statements.
SELECT Clause
The Select Clause determines which columns, or fields, will be returned and is, simply, a comma-delimited list of column names.
SELECT ID, Firstname, Lastname
FROM Clause
The From Clause specifies the table, query or view to be used to return the records from. This may be multiple tables, in which case the table must be related in some manner, either by the expression in the WHERE Clause or, more correctly, with the JOIN operator in the FROM Clause.
FROM MyTable
FROM Table1, Table2
WHERE Clause
The Where Clause is used to limit the number of records, or rows, returned by the query. Without it, all records from the table will be returned.
WHERE ID = 21
As the ID column is a unique column, a query with the above WHERE clause will return only one record. The WHERE clause can also be used to form a relationship between multiple tables.
WHERE Tabel1.ID = Table2.Table1ID
AND Table1.ID = 21
This is perfectly acceptable for smaller queries but is not too efficient for larger queries. For these, it is better to use one of the JOIN operators.
JOIN Operators
There are four join operators: INNER JOIN, OUTER JOIN, LEFT JOIN, and RIGHT JOIN which all work in much the same way. The purpose of each is to form a relationship between two tables which is achieved by matching the values in one column of the first table with the values in another column in the second table.
The above screen-shot is actually from Microsoft Access, but it helps illustrate the principle behind joins. 1. is an Inner Join, 2. is a Left Join, and 3. is a Right Join. The resultant code would be:
FROM Merchants INNER JOIN Projects ON Merchants.ProjectID = Projects.ID
LIKE Operator
The LIKE operator is used for string matching. The LIKE operator supports a number of wildcard characters for pattern matching:
| Wildcard character | Description | Example |
|---|---|---|
| % | Any string of zero or more characters. | WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title. |
| _ (underscore) | Any single character. | WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on). |
| [ ] | Any single character within the specified range ([a-f]) or set ([abcdef]). | WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. |
| [^] | Any single character not within the specified range ([^a-f]) or set ([^abcdef]). | WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l. |
The above information applies to Microsoft SQL and as far as I can establish, only the first two wildcards are supported in MySQL.
See the MySQL manual for the full SELECT syntax.