The SQL SELECT statement is the most commonly used command in Structured Query Language (SQL). It allows you to retrieve data from one or more tables in a database. Whether you are a beginner or an experienced developer, mastering the SELECT statement is the first step toward working with databases effectively.
In this guide, we will cover the syntax, examples, and different ways to use SELECT.
What is the SQL SELECT Statement?
The SELECT statement is used to query data from a database. It lets you specify:
- Which columns you want to display.
- Which rows to filter.
- How to sort the results.
- How to combine data from multiple tables.
In simple terms, the SELECT statement acts like a “data viewer” for your database.
Basic Syntax of SELECT
SELECT column1, column2, ...
FROM table_name;
Explanation:
SELECT→ Defines the columns you want to retrieve.column1, column2→ The names of the columns you want to display.FROM table_name→ The table from which to retrieve the data.
Example – Selecting All Columns
If you want to select all the columns from a table, use an asterisk *:
SELECT * FROM Employees;
This will display all columns and rows from the Employees table.
Example – Selecting Specific Columns
To select only specific columns:
SELECT FirstName, LastName, Department
FROM Employees;
This will display only the FirstName, LastName, and Department columns.
Filtering Results with WHERE Clause
The WHERE clause is used with SELECT to filter rows based on a condition.
Example:
SELECT FirstName, LastName
FROM Employees
WHERE Department = 'IT';
This will return only employees who work in the IT department.
Using Comparison Operators in WHERE
=→ Equal to!=or<>→ Not equal to>→ Greater than<→ Less than>=→ Greater than or equal to<=→ Less than or equal to
Example:
SELECT * FROM Products
WHERE Price > 100;
Filtering with Multiple Conditions
You can combine conditions using AND / OR.
Example:
SELECT * FROM Employees
WHERE Department = 'HR' AND Salary > 50000;
Sorting Results with ORDER BY
The ORDER BY clause sorts the results in ascending (ASC) or descending (DESC) order.
Example:
SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;
This will show the highest-paid employees first.
Removing Duplicates with DISTINCT
If you want to display only unique values, use DISTINCT.
Example:
SELECT DISTINCT Department
FROM Employees;
This will list each department name only once.
Limiting the Number of Rows
Sometimes you don’t need all the results.
You can limit them using:
- LIMIT (MySQL, PostgreSQL, SQLite)
- TOP (SQL Server)
- FETCH FIRST (Oracle, DB2)
Example in MySQL:
SELECT * FROM Employees
LIMIT 5;
This will return only the first 5 rows.
Example in SQL Server:
SELECT TOP 5 * FROM Employees;
Using Aliases for Better Readability
You can rename columns or tables using AS.
Example:
SELECT FirstName AS Name, Salary AS Income
FROM Employees;
This will display columns as Name and Income.
Using SELECT with Functions
SQL has built-in functions to perform calculations and formatting.
Example – Aggregate Functions:
COUNT()– Counts rows.SUM()– Adds values.AVG()– Finds the average.MAX()– Finds the largest value.MIN()– Finds the smallest value.
SELECT COUNT(*) AS TotalEmployees
FROM Employees;
Grouping Data with GROUP BY
GROUP BY groups rows with the same value and allows aggregate calculations.
Example:
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;
This will show the number of employees in each department.
Filtering Grouped Data with HAVING
While WHERE filters before grouping, HAVING filters after grouping.
Example:
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;
This will display only departments with more than 5 employees.
Selecting from Multiple Tables (JOIN)
The SELECT statement can retrieve data from more than one table using JOIN.
Example:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
This combines data from Orders and Customers tables where the CustomerID matches.
Types of Joins:
- INNER JOIN – Returns rows that match in both tables.
- LEFT JOIN – Returns all rows from the left table, and matching rows from the right table.
- RIGHT JOIN – Returns all rows from the right table, and matching rows from the left table.
- FULL JOIN – Returns all rows from both tables, with NULL for missing matches.
Subqueries in SELECT
A subquery is a SELECT statement inside another SELECT.
Example:
SELECT FirstName, LastName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
This will return employees earning above the average salary.
Selecting Calculated Columns
You can perform calculations directly in SELECT.
Example:
sqlCopyEditSELECT FirstName, LastName, Salary, Salary * 0.10 AS Bonus
FROM Employees;
This will calculate a 10% bonus for each employee.
Best Practices for SELECT Statements
- Specify Columns Instead of
*– Improves performance and reduces unnecessary data retrieval. - Use Aliases for Readability – Makes query output easier to understand.
- Filter Data Early – Use
WHEREandLIMITto reduce result size. - Indexing – Ensure indexed columns are used in filtering for faster results.
- Avoid Complex Joins on Large Tables Without Indexes – Can slow down queries significantly.
Example – Complete SELECT Query
SELECT e.FirstName, e.LastName, e.Department, e.Salary, d.Location
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > 50000
ORDER BY e.Salary DESC
LIMIT 10;
What this query does:
- Selects employee details and department location.
- Joins the Employees table with Departments table.
- Filters for salaries above 50,000.
- Sorts in descending order by salary.
- Limits the result to the top 10 highest-paid employees.
Conclusion
The SQL SELECT statement is the foundation of querying databases. With it, you can:
- Retrieve specific data.
- Filter, sort, and group results.
- Combine data from multiple tables.
- Perform calculations and aggregate analysis.
By learning the variations of the SELECT statement, you can extract exactly the information you need from your database in an efficient way.