What is 'SELECT' query in an SQL database?

Ever wondered how data from huge databases—like customer records or employee details—gets fetched so quickly? That’s where the SELECT query shines in SQL! The SELECT statement in SQL retrieves data from one or more tables. It helps users extract specific information by defining the columns they want and applying conditions to filter or organize the data. The SELECT statement generates reports, analyzes trends, and displays information to users in applications or dashboards.

What is an SQL SELECT statement?

The SELECT statement is used in SQL to retrieve data from one or more tables in a database. It allows us to specify the columns we want to display and apply conditions to filter the results.

Syntax

Here’s the basic syntax of the SELECT statement:

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

  • column1, column2: The columns you want to retrieve.

  • table_name: The table where the data resides.

  • condition(Optional): Specifies which rows to fetch based on criteria.

Example for the SELECT statement

Let’s start with a simple example:

-- This retrieves the FirstName and LastName columns from the Employees table
SELECT FirstName, LastName  
FROM Employees;

This retrieves the FirstName and LastName columns from the Employees table.

Retrieve all data with SELECT

To fetch all columns from a table, use the * wildcardA wildcard in SQL is a special character or symbol (like * or %) used to represent one or more characters in queries for flexible data retrieval.:

-- This retrieves every column and row in the Employees table
SELECT * 
FROM Employees;

This retrieves every column and row in the Employees table.

SELECT with WHERE clause

The WHERE clause filters rows based on a condition:

-- Step 1: Create the database
CREATE DATABASE CompanyDB;
-- Step 2: Use the database
USE CompanyDB;
-- Step 3: Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);
-- Step 4: Insert sample data into the Employees table
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES 
    ('Alice', 'Smith', 'HR', 60000.00),
    ('Bob', 'Johnson', 'IT', 75000.00),
    ('Carol', 'Williams', 'HR', 65000.00),
    ('David', 'Brown', 'Finance', 80000.00),
    ('Eve', 'Jones', 'IT', 70000.00);
-- Step 5: Execute the given query
SELECT FirstName, LastName  
FROM Employees  
WHERE Department = 'HR';
-- Exercise for learners:
-- Uncomment the following query and modify it to retrieve employees 
-- from the 'IT' department instead of 'HR'.
-- SELECT FirstName, LastName  
-- FROM Employees  
-- WHERE Department = 'IT';

This retrieves only employees in the HR department.

Try it: Uncomment the query at lines 33–35 and modify it to retrieve employees -- from the 'IT'.

While the WHERE clause filters individual rows, sometimes we need to group rows and analyze them—enter the GROUP BY clause.

SELECT with GROUP BY clause

The GROUP BY clause groups rows with the same values in specified columns:

-- This groups employees by department and counts how many employees are in each department
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

This groups employees by department and counts how many employees are in each department.

  • Comparisons in WHERE: NULL cannot be compared using standard operators (e.g., = or !=); instead, use IS NULL or IS NOT NULL to handle NULL values explicitly.

  • Grouping in GROUP BY: NULL values are treated as a separate group, meaning all rows with NULL in the grouped column are aggregated.

SELECT with HAVING clause

Use the HAVING clause to filter groups created by the GROUP BY clause:

-- This retrieves only departments with more than 1 employee
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;

This retrieves only departments with more than 1 employees.

SELECT with ORDER BY clause

The ORDER BY clause sorts the result:

-- This retrieves employees and sorts them by salary in descending order
SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

This retrieves employees and sorts them by salary in descending order.

Key takeaways

Focus on writing efficient queries by:

  • Retrieving only necessary columns.

  • Filtering rows using WHERE.

  • Using ORDER BY to present data in a readable format.

Ready to learn SQL and become a database expert? Join our Learn SQL course and follow the path to becoming a Database Professional with SQL! Unlock your potential and gain the skills employers seek. Start your journey today!

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How do I write a SQL SELECT query?

To write a SELECT query, start with the SELECT keyword, followed by the column names you want to retrieve. Then, use the FROM keyword to specify the table. For example:

SELECT column1, column2 FROM table_name;

You can also use conditions, sorting, and grouping with clauses like WHERE, ORDER BY, and GROUP BY.

What is the SELECT query and action query?

A SELECT query is a read-only operation used to fetch data from a database. It does not modify data but retrieves it based on specified criteria. An action query, on the other hand, modifies data in the database. Examples include INSERT, UPDATE, DELETE, and CREATE.

Which query is used to select the database?

To select or switch between databases, use the USE statement, not the SELECT query. For example:

USE database_name;

What is a query in SQL?

A query in SQL is a request made to the database to perform specific operations, like retrieving, inserting, updating, or deleting data. The most common query is the SELECT query, which fetches data from tables. Queries allow users to interact with the database and extract useful information.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)