The SELECT statement is a fundamental SQL command used to retrieve data from a database. In this article, we will explore the syntax and usage of the SELECT statement in SQL Server.

Motivation

Before diving into the SELECT statement, let’s understand its importance in real-life scenarios. When working with databases, you often need to analyze data, track system performance, and make changes to support new features. The SELECT statement plays a crucial role in these tasks, allowing you to retrieve and manipulate data to gain insights and make informed decisions.

SELECT Statement Syntax

The SELECT statement syntax in SQL Server can be complex, but we will focus on the key elements:

SELECT [TOP X] attributes & values
FROM first_table
[INNER/LEFT/RIGHT JOIN second_table ON condition(s)]
...
[WHERE condition(s)]
[GROUP BY set of attributes]
[HAVING condition(s)]
[ORDER BY list attributes and order];

Let’s break down the syntax:

• SELECT: Specifies the columns or expressions to retrieve from the database.
• FROM: Specifies the table(s) from which to retrieve the data.
• JOIN: Allows you to combine data from multiple tables based on specified conditions.
• WHERE: Filters the rows based on specified conditions.
• GROUP BY: Groups the rows based on specified attributes.
• HAVING: Filters the groups based on specified conditions.
• ORDER BY: Sorts the result set based on specified attributes and order.

Simple SELECT Statement Examples

Let’s start with some simple examples to understand the basic usage of the SELECT statement:

SELECT 1;
SELECT 1 + 2;
SELECT 1 + 2 AS result;
SELECT 1 + 2 AS first_result, 2 * 3 AS second_result;
SELECT (CASE WHEN 1 + 2 > 2 * 3 THEN 'greater' ELSE 'smaller' END) AS comparison;

In these examples, we haven’t used any tables from our database. Instead, we performed mathematical operations and used the AS keyword to assign aliases to the result columns. These examples demonstrate the flexibility and power of the SELECT statement.

Using the SELECT Statement with a Single Table

Now, let’s move on to using the SELECT statement with data from our database. We’ll start by selecting all columns from a single table:

SELECT * FROM country;
SELECT * FROM city;

The * symbol represents all columns in the specified table. If you only need specific columns, you can list them after the SELECT keyword:

SELECT id, country_name FROM country;

It’s good practice to only select the columns you need to minimize the amount of data returned. This becomes especially important when dealing with large tables or joining multiple tables.

Filtering Rows with the WHERE Clause

The WHERE clause allows you to filter rows based on specified conditions. Let’s look at some examples:

SELECT id, country_name_eng FROM country WHERE id = 2;
SELECT id, country_name_eng FROM country WHERE id > 2;
SELECT id, country_name_eng FROM country WHERE id = 6;

In these examples, we used the id column as the filter condition. The first query returns a single row where id is equal to 2. The second query returns all rows where id is greater than 2. The third query doesn’t return any rows because there is no record with id equal to 6.

Using the SELECT Statement with Multiple Tables

Finally, let’s explore how to select data from multiple tables using the JOIN keyword. In our example, the city and country tables are related via a foreign key:

SELECT city.id AS city_id, city.city_name, country.id AS country_id, country.country_name, country.country_name_eng, country.country_code
FROM city
INNER JOIN country ON city.country_id = country.id
WHERE country.id IN (1, 4, 5);

In this query, we used an INNER JOIN to combine the city and country tables based on the foreign key relationship. We also added a condition in the WHERE clause to filter the result to only include rows where the country.id is 1, 4, or 5.

Conclusion

In this article, we covered the basics of the SELECT statement in SQL Server. The SELECT statement is a powerful tool for retrieving and manipulating data from databases. In future articles, we will explore more advanced features such as GROUP BY, HAVING, and ORDER BY. Stay tuned!