SQL SELECT

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.

Company	Country	Model	Year	Color
Toyota	Japan	Camry	2014	Silver
Toyota	Japan	Avalon	2012	Grey
Honda	Japan	Accord	2012	Black
Chevrolet	USA	Malibu	2014	Red
Ford	USA	Fusion	2014	White
BMW	Germany	BMW 3	2013	Red
Audi	Germany	A4	2014	Blue

Above you see all entries in the table Cars and below is a simple statement that retrieves all these entries:

SELECT Company, Country, Model, Year, Color FROM Cars;

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:

SELECT * FROM Cars;

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:

SELECT Company FROM Cars;

Here is the result:

Company

Toyota

Honda

Chevrolet

Ford

BMW

Audi

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:

SELECT DISTINCT Company FROM Cars;

Company

Toyota

Honda

Chevrolet

Ford

BMW

Audi

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.

SELECT DISTINCT Company, Country FROM Cars;

And the result looks like this:

Company	Country
Toyota	Japan
Honda	Japan
Chevrolet	USA
Ford	USA
BMW	Germany
Audi	Germany

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:

SELECT TOP 2 * FROM Cars;

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:

SELECT TOP 2 Company, Country, Model, Year, Color FROM Cars;

The result will be:

Company	Country	Model	Year	Color
Toyota	Japan	Camry	2014	Silver
Toyota	Japan	Avalon	2012	Grey

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:

SELECT TOP 2 Company, Country, Model, Year, Color FROM Cars ORDER BY Year;

And here is the result of your ORDER BY:

Company	Country	Model	Year	Color
Toyota	Japan	Avalon	2012	Grey
Toyota	Japan	Camry	2014	Silver

You can tell the ORDER BY clause to sort the query result in ascending or descending order by adding ASC or DESC respectively:

SELECT TOP 2 Company, Country, Model, Year, Color FROM Cars ORDER BY Year DESC;

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:

SELECT * INTO CarsCopy FROM Cars;

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:

SELECT Model, Year INTO CarModels FROM Cars;

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:

SELECT Model, Continent = CASE Country WHEN 'Japan' THEN 'Asia' WHEN 'Germany' THEN 'Europe' WHEN 'USA' THEN 'North America' END FROM Cars;

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:

Model	Continent
Camry	Asia
Avalon	Asia
Accord	Asia
Malibu	North America
Fusion	North America
BMW 3	Europe
A4	Europe