Data Query Techniques in MySQL Using SELECT Statement Efficiently

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.