The MySQL SELECT statement is essential for fetching data from tables. It retrieves information and stores it in a result table, often referred to as a result set. Widely used in MySQL, SELECT is a fundamental command for querying databases.
This article covers the basics of SELECT syntax and explores its advanced features with practical examples, helping you understand how to effectively retrieve and work with data in MySQL databases.
SELECT Statement in MySQL
In MySQL, the SELECT statement is used to fetch data from tables. It allows you to specify which columns of data you want to retrieve, and optionally, you can apply filters to only get the data that meets specific conditions.
This statement is essential for interacting with databases, enabling you to retrieve and display information as needed for various purposes such as reporting, analysis, or application functionalities.
The MySQL SELECT statement uses various clauses like WHERE, GROUP BY, ORDER BY, etc. We can also use aggregate functions like SUM, COUNT, etc with SELECT statements to summarize data.
Syntax:
The Basic Syntax of the MySQL SELECT Statement is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC | DESC]
LIMIT number;
Explanation:
- SELECT: Specifies the columns or expressions that you want to retrieve from the database table. You can use
* to select all columns.
- FROM: Indicates the table from which you want to fetch the data.
- WHERE: Optional clause that allows you to specify conditions to filter the rows retrieved from the table. Only rows that satisfy the condition will be included in the result set.
- ORDER BY: Optional clause used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more columns.
- LIMIT: Optional clause that restricts the number of rows returned by the query. It is useful when you only want to retrieve a certain number of rows from the result set.
Here, column1, column2, ... are the columns you want to retrieve. If you want to retrieve data from all columns/fields, you can use the following
Syntax:
SELECT * FROM table_name
Demo MySQL Database
For this tutorial on MySQL SELECT statement, we will use the following MySQL table.
| employee_id | first_name | last_name | salary |
|---|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Robert | Johnson | 75000 |
To quickly create this table on your local MySQL Workbench, enter the following MySQL query:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2) );
INSERT INTO employees VALUES
(1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Robert', 'Johnson', 75000);
MySQL SELECT Statement Examples
Let's explore some examples to learn how to write SELECT statement queries.
Example 1: Selecting Specific Columns
Retrieve only first_name and last_name columns
SELECT first_name, last_name
FROM employees;
This example retrieves the first_name and last_name columns from the employees table.
Output:
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Jane | Smith |
| Robert | Johnson |
+------------+-----------+
Explanation:
- The query retrieves the first_name and last_name columns from the employees table.
- The result set displays the names of all employees in the table.
Example 2: Selecting All Columns
This query will retrieve the entire employee table.
SELECT * from employees;
Output:
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Robert | Johnson | 75000 |
+-------------+------------+-----------+--------+
Example 3: Performing Arithmetic Operations
SELECT 32*32
Output:
+-------+
| 32*32 |
+-------+
| 1024 |
+-------+
We can also use SELECT statements to perform, basic mathematical operations.
MySQL SELECT DISTINCT Statement
MySQL SELECT DISTINCT Statement is used to retrieve only distinct data from a field/column.
It is very used to remove duplicates from the results.
Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name
Conclusion
The MySQL SELECT statement is crucial for fetching specific data from tables, whether retrieving entire rows or selected columns. It supports filtering with WHERE, sorting with ORDER BY, and limiting results with LIMIT. Additionally, SELECT can perform calculations and handle distinct values with SELECT DISTINCT, making it versatile for various database querying needs.
Similar Reads
Nested Select Statement in MySQL
The relational databases, the ability to retrieve and manipulate data with precision is a cornerstone of effective database management. MySQL, a popular relational database management system, provides a powerful tool called the Nested SELECT statement that empowers developers to perform complex and
5 min read
MySQL INSERT INTO SELECT Statement
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. INSERT INTO SELECT statement i
5 min read
SQL SELECT IN Statement
The IN operator in SQL is used to compare a column's value against a set of values. It returns TRUE if the column's value matches any of the values in the specified list, and FALSE if there is no match.In this article, we will learn how IN operator works and provide practical examples to help you be
3 min read
SQL SELECT INTO Statement
The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new
5 min read
SQL CASE Statement
The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing
4 min read
PL/SQL CASE Statement
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. The PL/SQL CASE statement is a powerfu
4 min read
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani
4 min read
MySQL DELETE Statement
In DBMS, CRUD operations (Create, Read, Update, Delete) are essential for effective data management. The Delete operation is crucial for removing data from a database. This guide covers the MySQL DELETE statement, exploring its syntax and providing examples. Understanding how DELETE works helps ensu
6 min read
MySQL UPDATE Statement
MySQL is a popular relational database management system used in applications ranging from small projects to large enterprises. The UPDATE statement in MySQL is essential for modifying existing data in a table. It's commonly used to correct errors, update values, and make other necessary changes. Th
6 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
5 min read