SELECT Statement

The SELECT statement is used to query the database and retrieve a set of records that match specified criteria. It is the most fundamental statement for data retrieval in SQL.

Basic Syntax

The basic structure of a SELECT statement is:

SELECT column1, column2, ...
FROM table_name;

To select all columns from a table, you can use the asterisk (*):

SELECT *
FROM table_name;

Filtering Data with WHERE

The WHERE clause is used to filter records based on a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Conditions can involve comparison operators like =, <, >, <=, >=, <> (or !=), and logical operators like AND, OR, and NOT.

Example: Selecting specific columns with a filter

Retrieve the ProductName and UnitPrice for all products that cost more than $50:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50;

Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order. By default, it sorts in ascending order (ASC).

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example: Sorting results

Retrieve all customer names and sort them alphabetically:

SELECT CustomerName
FROM Customers
ORDER BY CustomerName ASC;

Retrieve product names and prices, sorted by price in descending order:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Limiting Results

Most SQL dialects provide a way to limit the number of rows returned by a query. The syntax varies:

SQL Server/MS Access: TOP N
MySQL/PostgreSQL: LIMIT N
Oracle: ROWNUM <= N (in the WHERE clause, or using FETCH FIRST N ROWS ONLY in newer versions)

Example: Limiting results (MySQL/PostgreSQL)

Get the top 5 most expensive products:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
LIMIT 5;

Distinct Values

The DISTINCT keyword is used to return only unique values in a specified column or set of columns.

SELECT DISTINCT column_name
FROM table_name;

Example: Finding unique values

Find all unique city names from the Customers table:

SELECT DISTINCT City
FROM Customers;

Common Clauses and Concepts

JOIN: Combines rows from two or more tables based on a related column.
Aggregate Functions: Functions like COUNT(), SUM(), AVG(), MIN(), MAX() that perform a calculation on a set of values.
GROUP BY: Groups rows that have the same values in specified columns into summary rows.
ORDER BY: Sorts the result set.
HAVING: Used to filter groups based on a specified condition (often used with GROUP BY).
Aliases: You can use AS to give a table or column a temporary name (alias) for readability. E.g., SELECT CustomerName AS Name FROM Customers;

SQL Documentation