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.
|
1 |
SELECT column1, column2 FROM table |
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.
|
1 |
SELECT column1, column2 FROM table WHERE column1='value' |
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.
|
1 |
SELECT Fruit_Name FROM Fruits |
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.
|
1 |
SELECT * FROM Fruits |
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.
|
1 |
SELECT ID,Fruit_Name ,Fruit_Color FROM Fruits |
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.
|
1 |
SELECT * FROM Fruits WHERE Fruit_Color='Red' |
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.
|
1 |
SELECT * FROM Fruits WHERE Fruit_Name LIKE 'L%' |
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.
|
1 |
SELECT * FROM Fruits WHERE Fruit_Name LIKE '%n%' |
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.
|
1 |
SELECT * FROM Fruits WHERE Fruit_Name LIKE '_a%' |
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.
|
1 |
SELECT TOP (2) * FROM Fruits |
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.
|
1 |
SELECT TOP (60) PERCENT * FROM Fruits |
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
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023