MariaDB Crash Course - Ben Forta [14]
Retrieving All Columns
In addition to being able to specify desired columns (one or more, as seen previously), SELECT statements can also request all columns without having to list them individually. This is done using the asterisk (*) wildcard character in lieu of actual column names, as follows:
Input
SELECT *
FROM products;
Analysis
When a wildcard (*) is specified, all the columns in the table are returned. The columns are in the order in which the columns appear in the table definition. However, this cannot be relied on because changes to table schemas (adding and removing columns, for example) could cause ordering changes.
* * *
Caution: Using Wildcards
As a rule, you are better off not using the * wildcard unless you really do need every column in the table. Even though use of wildcards might save you the time and effort needed to list the desired columns explicitly, retrieving unnecessary columns usually slows down the performance of your retrieval and your application.
* * *
* * *
Tip: Retrieving Unknown Columns
There is one big advantage to using wildcards. As you do not explicitly specify column names (because the asterisk retrieves every column), it is possible to retrieve columns whose names are unknown.
* * *
Retrieving Distinct Rows
As you have seen, SELECT returns all matched rows. But what if you do not want every occurrence of every value? For example, suppose you want the vendor ID of all vendors with products in your products table:
Input
SELECT vesnd_id
FROM products;
Output
+---------+
| vend_id |
+---------+
| 1001 |
| 1001 |
| 1001 |
| 1002 |
| 1002 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1003 |
| 1005 |
| 1005 |
+---------+
The SELECT statement returned 14 rows (even though only four vendors are in that list) because 14 products are listed in the products table. So how could you retrieve a list of distinct values?
The solution is to use the DISTINCT keyword, which, as its name implies, instructs MariaDB to return only distinct values.
Input
SELECT DISTINCT vend_id
FROM products;
Analysis
SELECT DISTINCT vend_id tells MariaDB to return only distinct (unique) vend_id rows, and so only four rows are returned, as seen in the following output. If used, the DISTINCT keyword must be placed directly in front of the column names.
Output
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
* * *
Caution: Can’t Be Partially DISTINCT
The DISTINCT keyword applies to all columns, not just the one it precedes. If you were to specify SELECT DISTINCT vend_id, prod_price, all rows would be retrieved unless both of the specified columns were distinct.
* * *
Limiting Results
SELECT statements return all matched rows, possibly every row in the specified table. To return just the first row or rows, use the LIMIT clause. Here is an example:
Input
SELECT prod_name
FROM products
LIMIT 5;
Analysis
The previous statement uses the SELECT statement to retrieve a single column. LIMIT 5 instructs MariaDB to return no more than five rows. The output from this statement is shown in the following:
Output
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Oil can |
| Fuses |
+----------------+
To get the next five rows, specify both where to start and the number of rows to retrieve, like this:
Input
SELECT prod_name
FROM products
LIMIT 5,5;
Analysis
LIMIT 5,5 instructs MariaDB to return five rows starting from row 5. The first number is where to start, and the second is the number of rows to retrieve. The output from this statement is shown in the following:
Output
+----------------+
| prod_name |
+----------------+
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
| Bird seed |
| Carrots |
+----------------+
So, LIMIT with one value specified always starts from the first row, and the specified number is the number of rows to return. LIMIT with two values specified can start from wherever that first value tells