Data Query Techniques in MySQL Using SELECT Statement Efficiently

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.