The SELECT statement is the cornerstone of SQL Server. Its primary purpose is to retrieve data from one or more tables or views. Because SQL is a declarative language, you use SELECT to describe what data you want, and the SQL Server Query Optimizer determines the most efficient way to get it.

1. The Basic Syntax

The most fundamental SELECT query requires two parts: the columns you want and the table they come from.

SQL

SELECT Column1, Column2 
FROM TableName;

2. Selecting All vs. Specific Columns

  • SELECT *: Retrieves every column in the table. While convenient for quick checks, it is bad practice in production code because it increases network traffic and can break your code if the table schema changes.
  • SELECT ColumnNames: Retrieves only the data you need. This is faster and more secure.

Example:

SQL

-- Selecting specific columns with Aliases
SELECT 
    FirstName AS [First Name], 
    LastName AS [Last Name],
    YearlyIncome / 12 AS MonthlyEstimate
FROM Employees;

3. Filtering Data with WHERE

The WHERE clause limits the rows returned by the query based on specific criteria. It supports various operators:

  • Comparison: =, <>, <, >, <=, >=
  • Range: BETWEEN (inclusive)
  • Pattern Matching: LIKE (using % for any string and _ for a single character)
  • List: IN ('Value1', 'Value2')
  • Logic: AND, OR, NOT

Example:

SQL
SELECT ProductName, Price
FROM Products
WHERE (Price > 100 OR CategoryID = 4)
  AND ProductName LIKE 'Smart%';

4. Organizing Results: ORDER BY and TOP

By default, SQL Server does not guarantee the order of results unless you explicitly ask for it.

  • ORDER BY: Sorts the result set by one or more columns in ASC (ascending, default) or DESC (descending) order.
  • TOP (n): Limits the output to a specific number of rows. It is best used in conjunction with ORDER BY.

SQL

SELECT TOP (10) ProductName, Price
FROM Products
ORDER BY Price DESC; -- Gets the 10 most expensive products

5. Summarizing Data: GROUP BY and HAVING

When you need to perform calculations (Aggregations) like SUM, AVG, COUNT, MIN, or MAX, you use the GROUP BY clause.

  • GROUP BY: Groups rows that have the same values into summary rows.
  • HAVING: Acts like a WHERE clause, but it filters groups after the aggregation has occurred.

SQL

SELECT CategoryID, COUNT(*) AS TotalProducts, AVG(Price) AS AveragePrice
FROM Products
GROUP BY CategoryID
HAVING AVG(Price) > 50; -- Only shows categories with an average price over 50

6. SELECT INTO: Creating Tables on the Fly

You can use SELECT to create a new table and populate it with data from an existing table in one step. This is commonly used for creating backups or temporary work tables.

SQL
SELECT * INTO Employees_Backup_2024
FROM Employees
WHERE Active = 1;

Categorized in:

SQL Server,