Basic SELECT Statements
The SELECT statement is fundamental to retrieving data from SQL Server databases. It allows you to specify which columns and rows you want to retrieve from one or more tables.
Retrieving All Columns
To retrieve all columns from a table, you can use the asterisk (*) wildcard. This is often useful for initial data exploration.
-- Selects all columns from the 'Customers' table
SELECT *
FROM Customers;
Retrieving Specific Columns
You can list the specific columns you want to retrieve, separated by commas. This is generally more efficient than using *, as it only fetches the necessary data.
-- Selects 'CustomerID' and 'CompanyName' from the 'Customers' table
SELECT CustomerID, CompanyName
FROM Customers;
Aliasing Columns
You can rename columns in the result set using the AS keyword or by simply providing an alias after the column name. This can make your query results more readable, especially when dealing with complex expressions or joins.
-- Selects 'ProductName' and its 'UnitPrice', aliasing 'UnitPrice' as 'Price'
SELECT ProductName, UnitPrice AS Price
FROM Products;
-- Another example without AS keyword
SELECT EmployeeID Employee_ID, FirstName || ' ' || LastName AS FullName
FROM Employees;
Tip:
Using meaningful aliases can significantly improve the clarity of your query results, especially when presenting them to others or using them in reports.
Selecting Distinct Values
The DISTINCT keyword is used to return only unique values in the specified column(s). If you have duplicate entries, DISTINCT will filter them out.
-- Selects only the unique 'Country' values from the 'Customers' table
SELECT DISTINCT Country
FROM Customers;
Note:
DISTINCT applies to the entire row of selected columns. If you select multiple columns with DISTINCT, it will return rows where the combination of values across all selected columns is unique.
Selecting Data with Expressions
You can perform calculations or manipulate data directly within the SELECT statement using expressions.
-- Calculates the total price for each order item (Quantity * UnitPrice)
SELECT OrderID, ProductID, Quantity, UnitPrice, (Quantity * UnitPrice) AS ExtendedPrice
FROM [Order Details];
Working with Literal Values
You can also select literal values, which are constants, directly in your query.
-- Selects a fixed string and a number along with data from a table
SELECT 'This is a constant string', 123 AS ConstantNumber, GETDATE() AS CurrentDateTime;
These basic SELECT statements form the foundation for querying data in SQL Server Management Studio. Mastering them is the first step towards more complex data manipulation and analysis.