In other computing languages, SELECT is the equivalent of the PRINT command.
In T-SQL, the language of SQL SERVER, the SELECT clause is the principal focus, and one of the first parts of, a SELECT statement.
(There may be some things which can precede it, such as a WITH clause, but it is the main focus none the less).
How to use
First of all, the word “SELECT” can be in capitals or lower case, or even a mixed case – SQL SERVER is not case sensitive in this way (although it can be in others).
What follows it is generally fields. For example, you may have a table with a field Address1 and Address2, then you can have a SELECT clause starting:
SELECT Address1, Address2
This would create a table with two columns. Notice that these two fields are separated by a comma. This is very important, as you will see below.
Sometimes you may not want the output of a field to have the same column name going in as going out. For example, you may want Address1 to become AddressPart1 (for example). You can then use the word AS to show the new column heading.
SELECT Address1 AS AddressPart1, Address2 AS AddressPart2
This is called an Alias, i.e. another name for the same thing. You can use aliases for a single column in a SELECT clause, some of those columns, or all the columns – your choice.
Now, you don’t actually need the AS keyword – and some people prefer to not use it. For example, the following is perfectly acceptable:
SELECT Address1 AddressPart1, Address2 AddressPart2
However, this can be a bit dangerous. Remember what I said about the two fields, Address1 and Address2 being separated by a comma. What would happen if there was no comma? What would be the result of:
SELECT Address1 Address2
The result would that there would be one column. It would take the input of Address1, but renaming it as Address2.
If you are using fields, such as Address1 from a table, then you must use a FROM clause – and generally, you will be using FROM clauses.
However, it is not compulsory in SQL SERVER’s T-SQL (however, it is compulsory in other forms of SQL). For example, you could do mathematical calculations without using a FROM clause:
You could use functions:
Want to find out more? Well, the SELECT clause is covered initially in Session 1 of my video course 70-461, and is then expanded on in 70-461 Session 2, together with other parts of the SELECT statement.
There are other ways to use a SELECT statement without a FROM clause. For example, if you have previously created variables, then you could can refer to them:
or even set them (but this is fairly advanced)
SELECT @myVar = GETDATE()
However, in all of the above cases, the column name would be “(No column name)” – if you want an actual column name, then you must use an alias.
Now, let’s have a look at the full syntax of the SELECT clause. It is:
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
Looking at ALL and DISTINCT first, ALL is the default – you don’t need to write it, unless you want to make a point with your code (it’s like saying “I REALLY want to say”, as opposed to “I want to say” – it doesn’t change your meaning, but it add emphasis to the statement.)
- “ALL” returns every row which results from the rest of the SELECT statement, including duplicates.
- “DISTINCT” returns unique rows – if there are duplicate rows, then only one is returned. It does take longer for SQL SERVER to do a DISTINCT in a SELECT clause, because it has to work out the full results, and then remove duplicates before it returns results.
Note that the combination of all field in your SELECT clause is considered. For example, the clause
SELECT DISTINCT myField
would return every unique myField. However,
SELECT DISTINCT myField1, myField2
would return every unique COMBINATION of myField1 and myField2.
Turning to TOP, this reduces the number of rows that are returned. To take examples:
SELECT TOP (5)
would return a maximum of 5 rows. If there were only 3 rows to begin with, then it would would return 3 rows. If there were 33, then it would return the first 5.
SELECT TOP (5) PERCENT
would return the first 5 percent of rows. For example, if there would otherwise be 1,000 rows, this SELECT clause would only return 50 rows.
Finally, if you are using ORDER BY, and there were additional rows that would then be excluded, but which are in the same ranking as the last row, then WITH TIES would return those rows, like this:
SELECT TOP (5) PERCENT WITH TIES
It’s not often used, but in case you need to return ties, it can be useful. And you MUST use ORDER BY to use WITH TIES.
One final word. You may have noticed that I have used brackets around the (5). You can get away with not using them – you could say “SELECT TOP 5” instead – but is official discouraged. And in fact, if you want to use a variable next to TOP, then the brackets are required. Trying to do the below without brackets will result in an error:
SELECT TOP (@myvar)
Do you have any questions about the SELECT clause? If so, why not use the comments box below to ask about them. You never know – other people may also be asking about them.
For now, thank you for reading, and I hope you found this useful.