SQL Select
The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.
Lets have a look at a simple SELECT statement:
This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.
How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns — it's much easier than writing out the names of all columns (especially if there are a lot of columns).
Of course, this SQL SELECT statement assumes that there is a table called Individual. If there wasn't, an error would be generated.
Lets have a look at the table the statement is trying to select data from:
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Because our select statement asks to display all columns and all records, we would see all columns and all records, like this:
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Select from Multiple Tables
You can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.
We have another table called Occupation, which contains the individual's occupation.
| OccupationId | IndividualId | JobTitle |
|---|---|---|
| 1 | 1 | Engineer |
| 2 | 2 | Accountant |
| 3 | 3 | Cleaner |
| 4 | 4 | Attorney |
| 5 | 5 | Sales Executive |
SQL statement
We will select from both the Individual table and the Occupation table. We will qualify any column names by prefixing them with its table's name and a dot.
Result
| IndividualId | FirstName | LastName | UserName | OccupationId | IndividualId | JobTitle |
|---|---|---|---|---|---|---|
| 2 | Homer | Simpson | homey | 2 | 2 | Accountant |
| 3 | Homer | Brown | notsofamous | 3 | 3 | Cleaner |
| 5 | Homer | Gain | noplacelike | 5 | 5 | Sales Executive |
Displaying Less Columns
If you don't need every column to be displayed you can single out just the columns you're interested in. It's good programming practice to do this — the more columns your program has to return, the more it will impact its performance.
To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list of the column names.
SQL statement
Result
| IndividualId | LastName | UserName |
|---|---|---|
| 2 | Simpson | homey |
| 3 | Brown | notsofamous |
| 5 | Gain | noplacelike |