If you're starting your journey as a SQL developer, mastering the SELECT statement is your first and most important step. The SELECT statement is the foundation of data retrieval in SQL Server, and understanding how to use it effectively will unlock your ability to work with databases confidently.
In this comprehensive guide, I'll walk you through the SQL SELECT basics using real-world examples from the WideWorldImporters sample database. Whether you're querying customer data, analyzing orders, or filtering inventory, you'll learn the essential techniques that every SQL developer needs to know.
Understanding the SELECT Statement
The SELECT statement is how we retrieve data from SQL Server tables. Think of it as asking the database a question: "Show me this information from that table." Every time you need to view, analyze, or report on data, you'll use SELECT.
The basic syntax is straightforward:
SELECT column1, column2, column3
FROM schema_name.table_name;
Let's start with a simple example using the WideWorldImporters database. Here's how to retrieve all customer information:
SELECT CustomerID, CustomerName, PhoneNumber, WebsiteURL
FROM Sales.Customers;
This query returns four specific columns from the Customers table. When you run this, SQL Server fetches every row in the table and displays only the columns you specified.
Selecting All Columns with the Asterisk (*)
Sometimes you want to see everything in a table. The asterisk (*) is a shortcut that selects all columns:
SELECT *
FROM Warehouse.StockItems;
This retrieves every column from the StockItems table, including StockItemID, StockItemName, UnitPrice, TypicalWeightPerUnit, and all other fields.
Developer Insight: While SELECT * is convenient during development and exploration, I avoid using it in production code. Explicitly naming columns makes your queries more maintainable, performs better, and prevents issues when table structures change.
Filtering Data with the WHERE Clause
Retrieving all data is rarely what you need. The WHERE clause lets you filter results based on specific conditions. This is where SELECT becomes truly powerful.
Basic WHERE Clause Examples
Let's find customers in a specific city:
SELECT CustomerID, CustomerName, DeliveryPostalCode
FROM Sales.Customers
WHERE DeliveryCityID = 19586;
You can also filter text data. Here's how to find a specific stock item:
SELECT StockItemID, StockItemName, UnitPrice, RecommendedRetailPrice
FROM Warehouse.StockItems
WHERE StockItemName = 'USB rocket launcher (Green)';
Filtering with Comparison Operators
SQL supports standard comparison operators: =, <>, <, >, <=, and >=. Here's how to find high-value orders:
SELECT OrderID, CustomerID, OrderDate, ExpectedDeliveryDate
FROM Sales.Orders
WHERE OrderID > 50000;
To find stock items below a certain price point:
SELECT StockItemID, StockItemName, UnitPrice
FROM Warehouse.StockItems
WHERE UnitPrice < 10.00;
Combining Conditions with AND and OR
Real-world queries often need multiple conditions. Use AND when all conditions must be true:
SELECT OrderID, CustomerID, OrderDate
FROM Sales.Orders
WHERE CustomerID = 832
AND OrderDate >= '2016-01-01';
Use OR when any condition can be true:
SELECT SupplierID, SupplierName, SupplierCategoryID
FROM Purchasing.Suppliers
WHERE SupplierCategoryID = 2
OR SupplierCategoryID = 4;
You can combine AND and OR, but always use parentheses to make your logic clear:
SELECT StockItemID, StockItemName, UnitPrice, SupplierID
FROM Warehouse.StockItems
WHERE (SupplierID = 4 OR SupplierID = 7)
AND UnitPrice > 50.00;
Developer Insight: Parentheses are your friends. Even when operator precedence makes them optional, they make your queries easier to read and prevent logic errors. I always use them when mixing AND and OR.
Working with Text Data: The LIKE Operator
When you need to search for partial text matches, the LIKE operator is essential. It uses wildcards:
%matches any sequence of characters_matches any single character
Find customers whose names start with "Tailspin":
SELECT CustomerID, CustomerName
FROM Sales.Customers
WHERE CustomerName LIKE 'Tailspin%';
Search for items with "USB" anywhere in the name:
SELECT StockItemID, StockItemName, UnitPrice
FROM Warehouse.StockItems
WHERE StockItemName LIKE '%USB%';
Find items ending with a specific term:
SELECT StockItemID, StockItemName
FROM Warehouse.StockItems
WHERE StockItemName LIKE '%mug';
Filtering with Lists: The IN Operator
The IN operator checks if a value matches any item in a list. Instead of writing multiple OR conditions, use IN:
SELECT CustomerID, CustomerName, DeliveryCityID
FROM Sales.Customers
WHERE DeliveryCityID IN (19586, 28077, 32310);
This is much cleaner than:
-- Less readable approach
SELECT CustomerID, CustomerName, DeliveryCityID
FROM Sales.Customers
WHERE DeliveryCityID = 19586
OR DeliveryCityID = 28077
OR DeliveryCityID = 32310;
You can also use NOT IN to exclude values:
SELECT SupplierID, SupplierName
FROM Purchasing.Suppliers
WHERE SupplierCategoryID NOT IN (1, 2, 3);
Filtering by Range: The BETWEEN Operator
BETWEEN checks if a value falls within a range (inclusive of both endpoints):
SELECT OrderID, CustomerID, OrderDate
FROM Sales.Orders
WHERE OrderDate BETWEEN '2016-01-01' AND '2016-01-31';
For numeric ranges:
SELECT StockItemID, StockItemName, UnitPrice
FROM Warehouse.StockItems
WHERE UnitPrice BETWEEN 20.00 AND 50.00;
Developer Insight: BETWEEN is inclusive, meaning it includes both boundary values. If you need exclusive boundaries, use comparison operators instead: UnitPrice > 20.00 AND UnitPrice < 50.00.
Handling NULL Values
NULL represents missing or unknown data. You cannot use = or <> with NULL. Instead, use IS NULL or IS NOT NULL:
SELECT PersonID, FullName, EmailAddress
FROM Application.People
WHERE EmailAddress IS NULL;
To find records with values:
SELECT PersonID, FullName, EmailAddress
FROM Application.People
WHERE EmailAddress IS NOT NULL;
Sorting Results with ORDER BY
The ORDER BY clause sorts your results. By default, it sorts in ascending order (ASC):
SELECT CustomerID, CustomerName
FROM Sales.Customers
ORDER BY CustomerName;
Sort in descending order using DESC:
SELECT StockItemID, StockItemName, UnitPrice
FROM Warehouse.StockItems
ORDER BY UnitPrice DESC;
You can sort by multiple columns:
SELECT OrderID, CustomerID, OrderDate, ExpectedDeliveryDate
FROM Sales.Orders
WHERE CustomerID = 832
ORDER BY OrderDate DESC, OrderID ASC;
Limiting Results with TOP
The TOP clause limits how many rows are returned. This is useful for sampling data or finding top performers:
SELECT TOP 10 StockItemID, StockItemName, UnitPrice
FROM Warehouse.StockItems
ORDER BY UnitPrice DESC;
You can also use TOP with PERCENT:
SELECT TOP 5 PERCENT OrderID, CustomerID, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC;
Removing Duplicates with DISTINCT
DISTINCT eliminates duplicate rows from results:
SELECT DISTINCT CustomerID
FROM Sales.Orders
WHERE OrderDate >= '2016-01-01';
This shows each unique customer who placed orders in 2016, even if they placed multiple orders.
Practical Real-World Examples
Example 1: Find Recent Orders for Specific Customers
SELECT o.OrderID, o.CustomerID, c.CustomerName, o.OrderDate
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2016-05-01'
AND c.CustomerName LIKE 'Tailspin%'
ORDER BY o.OrderDate DESC;
Example 2: Analyze Low-Stock Items
SELECT si.StockItemID, si.StockItemName, sih.QuantityOnHand, sih.ReorderLevel
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
WHERE sih.QuantityOnHand < sih.ReorderLevel
ORDER BY sih.QuantityOnHand ASC;
Example 3: Find High-Value Purchase Orders
SELECT TOP 20
PurchaseOrderID,
SupplierID,
OrderDate,
ExpectedDeliveryDate
FROM Purchasing.PurchaseOrders
WHERE OrderDate >= '2016-01-01'
AND IsOrderFinalized = 1
ORDER BY PurchaseOrderID DESC;
Common Mistakes to Avoid
1. Forgetting the WHERE Clause
Without WHERE, you retrieve every row. On large tables, this can cause performance issues or timeout errors. Always filter your data unless you genuinely need everything.
2. Incorrect String Comparison
SQL Server string comparisons are case-insensitive by default (depending on collation), but always use single quotes for strings, not double quotes:
-- Correct
WHERE CustomerName = 'Tailspin Toys'
-- Incorrect
WHERE CustomerName = "Tailspin Toys"
3. Not Using Parentheses with AND/OR
When mixing AND and OR, parentheses prevent logic errors:
-- Ambiguous - may not work as intended
WHERE CategoryID = 2 OR CategoryID = 4 AND UnitPrice > 50
-- Clear and correct
WHERE (CategoryID = 2 OR CategoryID = 4) AND UnitPrice > 50
4. Using = Instead of IS NULL
This doesn't work:
WHERE EmailAddress = NULL -- Always returns no results
Always use IS NULL:
WHERE EmailAddress IS NULL -- Correct
5. Not Considering NULL in Comparisons
NULL behaves unexpectedly in comparisons. If a column might contain NULL, handle it explicitly:
WHERE UnitPrice > 10.00 OR UnitPrice IS NULL
Summary and Key Takeaways
Mastering SQL SELECT basics is your gateway to working effectively with databases. Here's what we covered:
- SELECT retrieves data from tables; specify columns or use * for all
- WHERE filters data based on conditions using comparison operators, LIKE, IN, and BETWEEN
- AND/OR combine multiple conditions; use parentheses for clarity
- ORDER BY sorts results in ascending or descending order
- TOP limits the number of rows returned
- DISTINCT removes duplicate rows
- NULL requires special handling with IS NULL and IS NOT NULL
The WideWorldImporters database provides realistic scenarios for practicing these skills. As you continue learning, these fundamentals will support every advanced technique you encounter.
Next Steps: Practice writing queries on the WideWorldImporters database. Start simple, then gradually add complexity. The more you write and test queries, the more natural SQL syntax will become.
Frequently Asked Questions
Q: What's the difference between WHERE and HAVING?
A: WHERE filters rows before grouping occurs, while HAVING filters after grouping. For basic SELECT queries without GROUP BY, you'll use WHERE. HAVING comes into play when you work with aggregate functions like COUNT, SUM, or AVG.
Q: Can I use multiple ORDER BY columns, and how does it work?
A: Yes! SQL Server sorts by the first column, then by the second column for rows that have the same value in the first column, and so on. For example, ORDER BY CustomerName, OrderDate DESC sorts by customer name first, then by order date (newest first) within each customer.
Q: Should I use SELECT * in my queries?
A: Generally no, especially in production code. While SELECT * is convenient for quick exploration, explicitly listing columns makes queries more maintainable, performs better, and prevents breaking changes when table structures are modified. Only select the columns you actually need.