Querying Data in SQL Server
This document provides a comprehensive guide to querying data in Microsoft SQL Server using Transact-SQL (T-SQL). We will cover the fundamental concepts and syntax for retrieving information from your databases.
The SELECT Statement
The cornerstone of data retrieval in SQL Server is the SELECT statement. It allows you to specify which columns and rows you want to retrieve from one or more tables.
Basic Syntax
The simplest form of the SELECT statement retrieves all columns and all rows from a table:
SELECT *
FROM YourTableName;To select specific columns, list their names after SELECT, separated by commas:
SELECT Column1, Column2, AnotherColumn
FROM YourTableName;Filtering Data with WHERE Clause
The WHERE clause is used to filter records. It specifies a condition that must be met for a row to be included in the result set.
SELECT ProductName, Price
FROM Products
WHERE Category = 'Electronics';You can use various comparison operators:
=(Equal to)<>or!=(Not equal to)>(Greater than)<(Less than)>=(Greater than or equal to)<=(Less than or equal to)
And logical operators to combine conditions:
AND: Both conditions must be true.OR: At least one of the conditions must be true.NOT: Reverses the result of a condition.
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate > '2023-01-01' AND TotalAmount < 100.00;Sorting Data with ORDER BY Clause
The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.
SELECT CustomerName, City
FROM Customers
ORDER BY City ASC, CustomerName DESC;ASC is for ascending order (default), and DESC is for descending order.
Working with Multiple Tables
Often, you need to retrieve data that spans across multiple related tables. This is typically achieved using JOIN operations.
INNER JOIN
An INNER JOIN returns rows when there is at least one match in both tables being joined.
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;LEFT JOIN
A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;RIGHT JOIN
A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the left side.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;FULL OUTER JOIN
A FULL OUTER JOIN returns all rows when there is a match in either the left or the right table. If there is no match, the result is NULL on the side where there is no match.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:
COUNT(): Counts the number of rows.SUM(): Calculates the sum of values.AVG(): Computes the average of values.MIN(): Finds the minimum value.MAX(): Finds the maximum value.
SELECT COUNT(*) AS NumberOfOrders
FROM Orders;
SELECT AVG(Price) AS AveragePrice
FROM Products;GROUP BY Clause
The GROUP BY clause is used with aggregate functions to group rows that have the same values in specified columns into summary rows.
SELECT Category, COUNT(*) AS NumberOfProducts
FROM Products
GROUP BY Category;GROUP BY, any column in the SELECT list that is not an aggregate function must be included in the GROUP BY clause.
Subqueries
A subquery (or inner query) is a query nested inside another SQL query. Subqueries can be used in the WHERE, FROM, or SELECT clauses.
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate > '2023-12-01');Common Table Expressions (CTEs)
CTEs provide a way to define a temporary, named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE).
WITH RecentOrders AS (
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate > '2023-11-01'
)
SELECT ro.OrderID, c.CustomerName
FROM RecentOrders ro
JOIN Customers c ON ro.CustomerID = c.CustomerID;WHERE clause is fundamental to effective data querying. Practice these concepts with your own data to build proficiency.