Data Query Techniques in MySQL Using SELECT Statement Efficiently

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.