sql select

In this tutorial I will explain what a SQL SELECT statement is, when can you use it and I will give code examples how to use it.

A database stores data in tables. Data in tables is stored using columns and rows. You can retrieve data from tables using SQL SELECT Statement.

SELECT can be used to:

• Retrieve data from specific columns which exist in one or more tables.
• Create calculated fields that contains specific logic to product new results and return it to the user as extra column.
• Use Manipulation Functions (Text, Data and Time, Numeric). For instance retrieve on the fly today’s date.
• Use aggregate functions to summary the rows. For instance Sum sales for each country.
• Limit Results. For instance show only top 10 customers.
• Retrieve only distinct rows. For instance to get only a list of values that are used in a specific field.

In this tutorial we will cover the first two examples and the rest will be covered in separate tutorials that we will add to our SQL Tutorial page.

Note: We have video version of this tutorial. Please click the button below otherwise scroll down to read text based tutorial.

SQL Syntax and examples

Below is a very simple SQL SELECT syntax:

SELECT FieldName1, FieldName2, FieldName3
FROM tableName

I would like to show you now a few simple examples using Select.My table is called PersonDetails and contains only three fields (PersonID, PersonFirstName, PersonSurname). Based on this table I will present you how to retrieve only the fields you need and retrieve ALL rows from the table which is 4 in this case.

First example retrieves data from PersonName field which is taken from PersonDetails table :
SELECT PersonName
FROM PersonDetails

See below screenshot with our code sample and retrieved data.

In next example I will use exactly the same table as before (table PersonDetails) but this time I will retrieve two fields (PersonName, PersonSurname).Retrieving multiple columns is exactly the same as what I showed you on the first example, the only change we need to make is put comma (,) after the first field in select and then put the second field name.

Below is the code we use to retrieve rows (data) from multiple columns:
SELECT PersonName, PersonSurname
FROM PerosnDetails

See below the outcome of our code

As you can see retrieving fields from one table using SQL is very simple. In the next example we will show you how to retrieve all fields from one table without specifying the columns.

Using table PersonDetails I will retrieving all columns. There are two ways to retrieve data from all columns first one is to put all fields names in SELECT but there is an easier way to do that. We can use special character * which retrieved all fields from the specified table(s).

Below is the code sample:SELECT *
FROM PerosnDetails

Let’s check the output. As you can see the special character * retrieved data from all columns.

SQL Tip: Retrieving all columns can be very convenient but use only if you want to see what is in the table. If your code is going to be part of some kind of application/reporting system make sure you specify the fields which will make your queries run faster, will keep network data traffic to minimum and it is usually easier to understand your code (which fields are involved).

You may have noticed that our results pane column header is exactly the same as the field name in our select and sometimes you will want to rename it. This is easy to do with SQL. You can as an alias which is very simple in use; you just have put as NewFieldName after the column you want to rename.

Let’s see it in action:
SELECT PersonName as Name FROM PerosnDetails


As you can see our retrieved field in called ‘Name’ in the results pane and not ‘PersonName’ anymore.
 

SQL Select calculated column

You can retrieve field values using select but you can also use select to perform calculations and created calculated fields and below I will give several examples.

The simpliest form of calculated field (or calculated column) is below:

SELECT 2+2 AS CalculatedField



As you can see I provided two values and performed addition on them to get 4 as result. This is calculated field. Notice that I haven't used from as I don't need it for calculations.

In the following examples I will use calculated field as well but using more realistic scenarios.
 

SQL Select concatenate

Using Select you can concatenate values, fields (columns) that are strings but also you can convert data types and perform concatenation as well. See below examples:

In the example above we:

--> Concatenated two strings (I have placed a space at the end of  'word1 ' so it is displayed in the results)

SELECT ‘word1 ’ + ‘word 2’ as SimpleStrings

--> Concatenated a string with a field (I have placed a space after 'My name is ' to separate the words)

SELECT ‘My name is ‘ + FirstName as MyNameIs

FROM Customer

--> Concatenated two fields (notice how the space is added between the fields)

SELECT FirstName + ‘ ‘ + Surname as FullName

FROM Customer

--> Concatenated a string with an integer. In this case I used CAST to convert the year field which is an integer into a string (nvarchar with 4 characters)

SELECT ‘Born in ‘ + CAST(YearOfBirth AS NVARCHAR(4)) as BornInYear

FROM Customer

--> Concatenated a string (this could be any data type) with NULL. Be careful here as any concatenation with NULL (or a field containing NULL) will give you a NULL result.

SELECT ‘Problem with null’ + NULL as ProblemWithNULL

FROM Customer

--> Concatenated a string with NULL and handled NULL using the ISNULL function. In this case we changed NULL to an empty string by using 2 apostraphes(‘’)

SELECT ‘I am not NULL’ + ISNULL(NULL,’’) as NULLHandled

FROM Customer
 

Visit SQL Tutorial for more tutorials.

Take care
Katie & Emil