Esat Erkec
SQL SELECT statement multiple columns result set

SQL examples for beginners: SQL SELECT statement usage

May 27, 2019 by

SQLSELECT statements are used to retrieve data from the database and also, they populate the result of the query into the result-sets. The SQL examples of this article discourse and explain the fundamental usage of the SELECT statement in the queries.

SQL (Structured Query Language) queries can be used to select, update and delete data from the database. If anyone desires to learn SQL, to learn the SELECT statements can be the best starting point. On the other hand, we can use T-SQL query language particularly for SQL Server databases and it is a proprietary extension form of the SQL.

SELECT statement overview

The most basic form of the SQL SELECT statement must be include SELECT, FROM clauses. In addition, if we want to filter the result set of the query, we should use the WHERE clause.

The above query template specifies a very basic SQL SELECT statement. As you can see, column names are placed after the SELECT clause and these columns are separated with a comma sign with (,). After the FROM clause, we add the table name in which we want to populate the data into the result set. In addition, the following query template illustrates the usage of the WHERE clause in the SELECT query.

With the WHERE clause, we can filter the result set of the select statement. Filtering patterns are used after the WHERE clause. Now, we will make some SQL examples of the SQL SELECT statement and reinforce these theoretical notions.

Basic SQL examples: Your first step into a SELECT statement

Assume that, we have a fruits table which likes the below and includes the following rows;

ID

Fruit_Name

Fruit_Color

1

Banana

Yellow

2

Apple

Red

3

Lemon

Yellow

4

Strawberry

Red

5

Watermelon

Green

6

Lime

Green

We want to get all data of the Fruit_Name from the Fruits table. In this case, we must write a SQL SELECT statement which looks like the below.SQL Server database engine processes this query and then returns the result-set of the query.

SQL SELECT statement result set

As you can see, the query returns only Fruit_Name column data.

Now, we will practice other SQL examples which are related to the SELECT statement. In this example first example, we will retrieve all columns of the table. If we want to return all columns of the table, we can use a (*) asterisk sign instead of writing whole columns of the table. Through the following query, we can return all columns of the table.

SQL examples which is using (*) asterisk wildcard.

At the same time, to retrieve all columns, we can do this by writing them all separately. However, this will be a very cumbersome operation.

SQL SELECT statement multiple columns result set

SQL examples: How to filter a SELECT statement

In this section, we will take a glance at simple clause usage of the WHERE clause. If we want to filter the result set of the SQL SELECT statement, we have to use the WHERE clause. For example, we want to filter the fruits whose colors are red. In order to filter results of the query, at first we add the column name which we want to filter and then specify the filtering condition. In the below SQL example, we will filter the red fruits of the Fruits table.

SQL SELECT example; which is using (=) equal operator.

As you can see that, the result set only includes the red fruits data. However, in this example, we filter the exact values of the columns with (=) equal operator. In some circumstances, we want to compare the similarity of the filtered condition. LIKE clause and (%) percent sign operator combination helps us to overcome these type of issues. For example, we can filter the fruits who start with the letter “L” character. The following query will apply a filter to Fruit_Name and this filter enables to retrieve fruits who start with “L” chracter.

Filtering SQL SELECT statement with LIKE clause with single '%' percentage sign.

At the same time, we can apply (%) percentage operator at any place or multiple times to thw filter pattern. In the following example, we will filter the fruits name which includes ‘n’ chracter.

Filtering SELECT statement with LIKE clause with multiple '%' percentage sign.

Another commonly used operator is (_) the underscore operator. This operator represents any character in the filter pattern. Assume that, we want to apply a filter to the fruit names which meet the following criterias:

  • The first character of the fruit name could be any character
  • The second character of the fruit name must be ‘a’
  • The remaining part of the fruit name can contain any character

The following SQL example will meet all criteria.

Filtering SELECT statement with LIKE clause with '_' underscore sign.

SQL examples: SELECT TOP statement

The SELECT TOP statement is used to limit the number of rows which returns the result of the query. For example, if want to retrieve only two rows from the table we can use the following query. Therefore, we can limit the result set of the query. In the following SQL examples, we will limit the result set of the query. Normally, the result of the query without TOP operator can returns much more rows but we force to limit returning row numbers of the query with TOP clause.

SQL examples; which is using TOP clause.

At the same time, we can limit the result set of the SQL SELECT statement with a percent value. Such as, the following query returns only %60 percentage of result set.

SQL examples; which is using TOP PERCENT combination

As you can see we added PERCENT expression to TOP operator and limit the result set of the query.

See also

For more articles on the SQL SELECT statement including SQL examples see

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec
Esat Erkec
SQL commands, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

913 Views