MySQL is a popular and widely used relational database management system (RDBMS) in various sectors. Designed to store, process, and manage data from small to large scales, MySQL is a top choice in developing web applications, information systems, and e-commerce platforms. One of the most important basic commands is the SELECT Statement, which is used to retrieve or display data from one or more tables in a database.
What is a SELECT Statement?
SELECT Statement is a basic SQL command used to retrieve data from one or more tables in a MySQL database. With this command, users can display data as needed, whether it’s selecting a specific column or retrieving the entire table contents.
The main purpose of the SELECT Statement is to provide convenience for users to retrieve information from the database according to specific needs, whether it is displaying a single column, multiple columns, or entire data from a table. In addition, the SELECT Statement can also be combined with other commands such as WHERE, ORDER BY, and LIMIT to perform filtering, sorting, or limit the number of results displayed.
How to Use SELECT Statement to Capture Single Column Data
SELECT Statement in SQL is a command that is used to retrieve data from a specific column in a table. The basic syntax for using a SELECT statement is as follows:
SELECT column_name FROM table_name;In this syntax, SELECT is the function of selecting the data you want to display, column_name is the column whose data you want to retrieve, FROM indicates the source of the data, and table_name is the table where the column is located.
For example, we can retrieve data from a column named prod_name in table products. Using the following SELECT Statement, we can display all the data from the prod_name columns:
SELECT prod_name FROM products;The result of this command is all the values contained in the prod_name column of the products table. For example, if the products table has data like this:
| prod_name |
|---------------|
| 0.5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |How to Use SELECT Statement to Capture Multiple Column Data
To retrieve data from multiple columns in one table, the SELECT Statement allows us to name multiple columns at once. This way, we can display multiple columns from the same table in a single query without having to run the SELECT command repeatedly.
When we want to take multiple columns, we just need to add the names of the columns we want to display after the keyword SELECT, separated by a comma (,). The name of the table that contains that data is still specified after the keyword FROM.
The syntax for taking multiple columns is as follows:
SELECT column_name1, column_name2, column_name3 FROM table_name;
- SELECT: to select the data you want to retrieve.
- column_name1, column_name2, column_name3: the column you want to display, separated by a comma.
- FROM: to indicate the table from which the data was retrieved.
For example, here is a query to retrieve data from the prod_id, prod_name, and prod_price columns in the products table:
SELECT prod_id, prod_name, prod_price FROM products;If the products table contains data like this:
| prod_id | prod_name | prod_price |
|---------|---------------|------------|
| ANV01 | 0.5 ton anvil | 5.99 |
| ANV02 | 1 ton anvil | 9.99 |
| ANV03 | 2 ton anvil | 14.99 |
| OL1 | Oil can | 8.99 |Then the results of this query will display data from the prod_id, prod_name, and prod_price columns at the same time so that we can see the product ID, product name, and price in one view.
Retrieve All Columns with SELECT Statement
When we want to display all the columns of a table, we can use the SELECT Statement with the wildcard symbol *. The * wildcard allows us to retrieve all the columns without having to mention them individually.
However, while this is practical, the use of * wildcards needs to be done with caution, especially if the table has many columns or if only a few columns are needed. Retrieving all columns for no apparent reason can slow down query performance, especially if the data retrieved is very large. Therefore, it is best to use the * wildcard only if all the data is necessary.
To retrieve all the columns from the products table, we can use the following query:
SELECT * FROM products;If the products table contains data like this:
| prod_id | prod_name | prod_price | prod_vendor | stock_qty |
|---------|---------------|------------|-------------|-----------|
| ANV01 | 0.5 ton anvil | 5.99 | Vendor A | 20 |
| ANV02 | 1 ton anvil | 9.99 | Vendor A | 15 |
| ANV03 | 2 ton anvil | 14.99 | Vendor B | 30 |
| OL1 | Oil can | 8.99 | Vendor B | 50 |Then the result of this command will display all the columns (prod_id, prod_name, prod_price, prod_vendor, stock_qty) of the products table.
Important Notes
Using wildcard* makes it easier to retrieve all columns in a single query, but it can reduce performance if the retrieved table has many columns or very large data. So, if we only need a few specific columns, it’s better to mention the names of those columns directly to keep the query efficient.
Limiting Query Results with LIMIT
In SQL, the LIMIT command serves to limit the amount of data displayed from the query results. LIMIT is especially useful when we only want to retrieve a certain amount of data from a table, such as displaying a sample or just the first few pieces of data from a large query result.
LIMIT is used by mentioning the desired amount of data after the keyword. When LIMIT is followed by a single number, MySQL will retrieve several rows of data ranging from the first row to the specified number.
The following example shows how to use LIMIT to retrieve the first 5 data from the prod_name column in the products table:
SELECT prod_name FROM products LIMIT 5;Then the result of this query will only show the first 5 rows:
| prod_name |
|---------------|
| 0.5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |Use of LIMIT and OFFSET in Data Capture
We can limit the number of rows retrieved and also determine which rows the data is retrieved from by using OFFSET. In MySQL, OFFSET is written in conjunction with LIMIT to determine the starting point of data retrieval. Examples:
SELECT prod_name FROM products LIMIT 5 OFFSET 5;This query will retrieve 5 rows of data, starting from the 6th row.
There are also other ways to write LIMIT by OFFSET using two numbers after LIMIT, such as:
SELECT prod_name FROM products LIMIT 5, 5;This writing has the same meaning, which is to take 5 lines starting from the 6th row, where the first number (5) is OFFSET and the second number (5) is the amount of data to be taken.
Benefits of LIMIT and OFFSET
- LIMIT without OFFSET makes it easier for us to retrieve some initial data from the query results.
- The combination of LIMIT and OFFSET is particularly useful for in-app data settings such as page views (*pagination*), where each page requires the same amount of data but starts from different positions.
Capturing Unique Data with DISTINCT
In SQL, DISTINCT commands are used in the SELECT Statement to get data without duplication. If there is the same data in a column and we just want to display different values, DISTINCT makes sure each value appears once in the query results.
DISTINCT is placed after the SELECT keyword and before the name of the column you want to retrieve. This command tells MySQL to display only the unique data from that column and delete rows that have the same value.
The syntax for using DISTINCT is as follows:
SELECT DISTINCT column_name FROM table_name;- DISTINCT: instructs SQL to display unique values in the specified columns.
- column_name: The name of the column that you want to uniquely retrieve data from.
- FROM: indicates the table from which the data originated.
For example, we want to get unique data from the vend_id columns in the products table. Without using DISTINCT, if there are multiple products from the same vendor, then each vendor ID will appear multiple times in the query results. With DISTINCT, we can make sure the results only show each vendor ID once.
Examples are as follows:
SELECT DISTINCT vend_id FROM products;If the products table has data like this:
| vend_id |
|---------|
| 1001 |
| 1001 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1004 |The results of the query will only show the unique vendor ID, so it will look like this:
| vend_id |
|---------|
| 1001 |
| 1002 |
| 1003 |
| 1004 |Important Notes
- DISTINCT applies to the combination of columns selected in the query. If more than one column is used with DISTINCT, then SQL will ensure that the combination of columns is unique.
- DISTINCT only removes duplicates from the specified columns and does not affect other columns in the table.
Using Qualified Column Names
Using fully qualified column names (in the table.column format) in SQL has several important benefits, especially when retrieving data from tables that may have columns with the same name.
Here are some of the advantages of using fully qualified column names:
- With the table.column format, you indicate which table the column is coming from. This is especially useful when working with multiple tables in a single query, such as when doing JOIN. This clarity reduces confusion about which column is being referenced.
- If there are two or more tables that have columns with the same name, using fully qualified column names can prevent conflicts. For example, if you have products and orders tables that each have prod_id columns, you can use products.prod_id and orders.prod_id to differentiate between the two.
- By including table names in column references, SQL code becomes easier to understand and maintain by other developers or by themselves in the future. This helps in ongoing documentation and development.
Conclusion
Learning SELECT statements in MySQL is essential for managing data properly and securely. With the ability to retrieve specific data, flexibility in selecting columns, maintaining clarity, and improving performance, you can improve the efficiency and quality of your projects. Following best practices such as using complete column names and consistent formatting is also beneficial for documentation and troubleshooting in SQL code.