SQL SELECT is the first thing we are going to learn in our simple SQL tutorial. Before attempting to modify, insert or delete data from a database table, it's a good idea to be able to read data from the database first.
The SELECT clause is used to retrieve information from database table(s). Let's have a look at a very simple SQL SELECT statement selecting data from a database table called Cars, as this is the easiest way to understand how it works.
Above you see all entries in the table Cars and below is a simple statement that retrieves all these entries:
The SQL statement starts with the SELECT keyword, followed by a comma-separated list of column names. Then we have the FROM SQL keyword, which simply specifies which table the columns belong to. This instructs the SQL interpreter to select all values for the listed columns from the table Cars.
In the example above we requested to list all columns, and there is a short-cut statement that does exactly the same:
The * simply means that you require all columns in the table to be returned. Using the * symbol when doing selects is not advisable due to readability and performance issues it may introduce.
SQL SELECT DISTINCT
So far we selected all columns from the table Cars, however you can retrieve any number of columns from the table, in any order you want. For example the following SQL statement retrieves only the Company column:
Here is the result:
As you can see in the result above we have two entries for Toyota because in our table cars Toyota had two different models for that company. Sometimes you might want to remove duplicates like this and get a list with unique companies only. To achieve that you can use the SQL DISTINCT clause. Here is how:
You can use more than one column with SELECT DISTINCT. For example if you want to select all unique companies and countries from the Cars table you could do the following.
And the result looks like this:
When you have more than one column in your SQL DISTINCT statement, the SQL interpreter selects all unique combinations between those columns. In our Cars table we have two entries for Toyota (Company column) and those records both have Japan (Country column). So instead of Toyota and Japan twice, the DISTINCT clause returns it only once.
SQL SELECT TOP
Our Cars table has only 7 records in it, but in real life there are many cases when you will work with tables with 100's of thousands or even millions of records. Most of the time you will have to work with a very small sub-set of all the data stored in a DB table. When using SELECT to retrieve information from the database you can use the TOP clause to tell the SQL engine to return only certain number of records. Here is how it works:
The statement above will return only the first 2 records from our table. Of course it's better to re-write the SQL code above to the following, explicitly specifying which columns do we want to retrieve:
The result will be:
SQL SELECT ORDER BY
Quite often you will want to order the results of your queries by one or more columns. In the last example above we might want to order the results of the query by year. Here is how to do that:
And here is the result of your ORDER BY:
You can tell the ORDER BY clause to sort the query result in ascending or descending order by adding ASC or DESC respectively:
If you don't specify either ASC or DESC, then the results are in ascending order by default. You can also order by more than one column, and each of the columns in your ORDER BY can have ASC or DESC modifier.
SQL SELECT INTO
There is an easy way to create a new table which is exact copy of another using the SQL SELECT INTO command. Consider the following SELECT INTO statement:
The result will be a new table called CarsCopy which will have the same structure and data as the Cars table. Of course instead of * you can specify a list of columns like this:
This will create a new table named CarModels with Model and Year columns and all the data from the respective columns in the original Cars table.
SQL SELECT CASE
The CASE SQL clause evaluates one or more conditions and returns a result expression. Here is how to use CASE with SELECT statement:
The above SELECT case statement retrieves 2 columns, the first one is the Model, and the second one called Continent, and the values of this column depend on the country. You can see the result of the statement below: