THE education site for computer science and ICT
3. The WHERE clause
Consider a single database table below
Table name: Employees
| ID | Title | First name | Surname | Address | City | Postcode | Telephone |
| 1 | Mr | Tom | Smith | 42 Mill Street | London | WE13GW | 010344044 |
| 2 | Mrs | Sandra | Jones | 10 Low Lane | Hull | HU237HJ | 022344033 |
| 3 | Mr | John | Jones | 10 Low Lane | Hull | HU237HJ | 022344033 |
The statement : SELECT * FROM Employees extracts all the records from the Employee table. But what if we only wanted the Mr Tom Smith record?
This is achieved using the WHERE keyword followed by some logical condition. Like this
SELECT * FROM Employees WHERE { some logical condition }
So the WHERE keyword is used whenever a sub-set of records need to be extracted.
For example, let's pull out the Smith record. This is done like this
SELECT * FROM Employee WHERE Surname = 'Smith'
The logicial condition states that the field called 'Surname' has to match the value 'Smith'
Note: where the field value is text (string) i.e. 'Smith' you should use single quotes around the text to tell SQL that this is not a table name. Where numbers are used as the field value i.e. 20 you do not need to use quote marks as SQL can deal with numbers. However, if you want to make things simple for yourself you can use quotes around numbers as well as SQL can still deal with numbers within quotes |
A logical condition must eventually evaluate to TRUE, FALSE or NULL in order to decide which records are to be returned and which are not. The statement is NULL if a field is empty for example so it is neither true nor false.
Challenge see if you can find out one extra fact on this topic that we haven't already told you
Click on this link: Using the WHERE clause in SQL