What is the SELECT Statement?
The SELECT statement is one of the most commonly used SQL
commands. It is used to retrieve data from one or more database tables.
With SELECT, you can choose specific columns, filter records, sort results,
calculate values, and combine data from multiple tables.
Basic SELECT Syntax
The basic SELECT statement specifies the columns to retrieve and the table from which the data should be retrieved.
WITH employees(id, name, department) AS (
VALUES
(1, 'Aarav', 'IT'),
(2, 'Meera', 'Finance'),
(3, 'Rohan', 'Sales')
)
SELECT name, department
FROM employees;Selecting a Single Column
You can retrieve data from a single column by specifying its name after
the SELECT keyword.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'Finance', 72000),
('Rohan', 'Sales', 58000)
)
SELECT name
FROM employees;Selecting Multiple Columns
Multiple columns can be selected by separating their names with commas.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'Finance', 72000),
('Rohan', 'Sales', 58000)
)
SELECT name, department, salary
FROM employees;Selecting All Columns
The asterisk (*) is a wildcard that represents all columns
in the selected table.
WITH products(id, name, category, price) AS (
VALUES
(1, 'Keyboard', 'Computer', 800),
(2, 'Mouse', 'Computer', 400),
(3, 'Monitor', 'Display', 1500)
)
SELECT *
FROM products;SELECT with WHERE
The WHERE clause filters records so that only rows matching
a specified condition are returned.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'Finance', 72000),
('Rohan', 'Sales', 58000),
('Anika', 'HR', 62000)
)
SELECT name, salary
FROM employees
WHERE salary > 60000;SELECT with ORDER BY
The ORDER BY clause sorts the returned records in ascending
or descending order.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'Finance', 72000),
('Rohan', 'Sales', 58000),
('Anika', 'HR', 62000)
)
SELECT name, salary
FROM employees
ORDER BY salary DESC;SELECT DISTINCT
The DISTINCT keyword removes duplicate values and returns
only unique combinations of the selected columns.
WITH employees(name, department) AS (
VALUES
('Aarav', 'IT'),
('Meera', 'Finance'),
('Rohan', 'IT'),
('Anika', 'HR'),
('Vikram', 'Finance')
)
SELECT DISTINCT department
FROM employees;SELECT with Calculations
SELECT can perform calculations on numeric columns. The calculated result can also be given a meaningful alias.
WITH products(name, price, quantity) AS (
VALUES
('Keyboard', 800, 2),
('Mouse', 400, 3),
('Monitor', 1500, 1)
)
SELECT name,
price,
quantity,
price * quantity AS total_cost
FROM products;SELECT with Column Aliases
The AS keyword can give a selected column or expression a
temporary name in the result.
WITH employees(name, salary) AS (
VALUES
('Aarav', 65000),
('Meera', 72000),
('Rohan', 58000)
)
SELECT name AS employee_name,
salary AS annual_salary
FROM employees;SELECT with LIMIT
In SQLite, the LIMIT clause restricts the number of rows
returned by a query.
WITH products(name, price) AS (
VALUES
('Keyboard', 800),
('Mouse', 400),
('Monitor', 1500),
('Webcam', 1000),
('Speaker', 2200)
)
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;SELECT with IN
The IN operator allows SELECT to match a column against
multiple specified values.
WITH employees(name, department) AS (
VALUES
('Aarav', 'IT'),
('Meera', 'Finance'),
('Rohan', 'Sales'),
('Anika', 'HR')
)
SELECT name, department
FROM employees
WHERE department IN ('IT', 'Finance');SELECT with BETWEEN
The BETWEEN operator selects rows where a value falls within
an inclusive range.
WITH products(name, price) AS (
VALUES
('Mouse', 400),
('Keyboard', 800),
('Monitor', 1500),
('Speaker', 2500)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 1600;SELECT with LIKE
The LIKE operator searches text values using patterns.
The % character represents zero or more characters.
WITH employees(name) AS (
VALUES
('Aarav'),
('Anika'),
('Rohan'),
('Meera')
)
SELECT name
FROM employees
WHERE name LIKE 'A%';SELECT with NULL Values
Use IS NULL to select rows where a column contains a NULL
value. Do not use = NULL for this purpose.
WITH employees(name, manager) AS (
VALUES
('Aarav', 'Ravi'),
('Meera', NULL),
('Rohan', 'Priya')
)
SELECT name, manager
FROM employees
WHERE manager IS NULL;SELECT with Aggregate Functions
SELECT can be combined with aggregate functions such as
COUNT(), SUM(), and AVG().
WITH sales(amount) AS (
VALUES
(1200),
(800),
(1500),
(700)
)
SELECT COUNT(*) AS total_orders,
SUM(amount) AS total_sales,
AVG(amount) AS average_sale
FROM sales;SELECT with GROUP BY
The GROUP BY clause groups rows with the same values,
often together with aggregate functions.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'IT', 72000),
('Rohan', 'HR', 58000),
('Anika', 'HR', 62000)
)
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;SELECT with HAVING
The HAVING clause filters grouped results after
GROUP BY has been applied.
WITH employees(name, department) AS (
VALUES
('Aarav', 'IT'),
('Meera', 'IT'),
('Rohan', 'IT'),
('Anika', 'HR'),
('Vikram', 'HR')
)
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;SELECT from Multiple Tables
SELECT can retrieve related information from multiple tables using a
JOIN.
WITH employees(id, name, department_id) AS (
VALUES
(1, 'Aarav', 10),
(2, 'Meera', 20),
(3, 'Rohan', 10)
),
departments(id, department_name) AS (
VALUES
(10, 'IT'),
(20, 'Finance')
)
SELECT e.name,
d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.id;Selecting a Constant Value
SELECT can also return literal values or expressions without reading from a table.
SELECT 'Hello, SQL!' AS message,
10 + 5 AS result;Common SELECT Clauses
| Clause | Purpose | Example |
|---|---|---|
| SELECT | Choose columns or expressions | SELECT name |
| FROM | Specify the data source | FROM employees |
| WHERE | Filter rows | WHERE salary > 60000 |
| GROUP BY | Group rows | GROUP BY department |
| HAVING | Filter grouped results | HAVING COUNT(*) > 2 |
| ORDER BY | Sort results | ORDER BY salary DESC |
| LIMIT | Restrict rows in SQLite | LIMIT 5 |
Order of a SELECT Query
A SELECT query can contain several clauses. The following example shows a common structure used in SQLite.
WITH employees(name, department, salary) AS (
VALUES
('Aarav', 'IT', 65000),
('Meera', 'IT', 72000),
('Rohan', 'HR', 58000),
('Anika', 'HR', 62000)
)
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 0
ORDER BY average_salary DESC
LIMIT 10;Advantages of SELECT
- Retrieves only the data you need.
- Can filter and sort query results.
- Can remove duplicate values using DISTINCT.
- Can perform calculations and aggregations.
- Can retrieve data from multiple related tables.
- Forms the foundation of most SQL data-retrieval queries.
Best Practices
- Specify only the columns you need instead of using
SELECT *when possible. - Use meaningful aliases for calculated or renamed columns.
- Use WHERE to filter rows before returning unnecessary data.
- Use ORDER BY when the order of results matters.
- Use LIMIT when you only need a specific number of rows in SQLite.
- Use DISTINCT only when duplicate results actually need to be removed.
- Use table aliases to make multi-table queries easier to read.
The SELECT statement is the foundation of SQL data retrieval. Mastering SELECT will help you retrieve, filter, sort, calculate, group, and analyze information stored in relational databases.
🧪 Test Your SQL Code
Edit the SQL code on the left and click “Run Code” to see the result on the right.
Click “Run Code” to see the result here.