📖 MySQL Advanced Select Statement
Advanced SELECT statement modifiers expand the utility of SELECT statements, enabling more sophisticated query filters and operations. In this article, you'll learn how to use LIKE and REGEXP for pattern matching, IS NULL for handling missing data, AS for creating aliases, and various functions like CONCAT, ROUND, and DISTINCT to enhance your queries.
LIKE and REGEXP
Used to search strings with wildcards and special characters.
LIKE vs. REGEXP
While LIKE is simpler and effective for basic pattern matching, REGEXP provides more powerful and flexible options for complex patterns. Use LIKE for straightforward matches, such as finding values that start with a specific prefix. Use REGEXP when you need to match more complex patterns, such as multiple possibilities or specific positioning within the string.
LIKE wildcards
% (percent sign) designates any number of characters.
_ (underscore) designates a single character.
WHERE last_name like 'BURK%';
-- Returns all last names that start with BURK.
-- For example, last names of BURKS, BURKE, and BURKHART would all be returned.
WHERE product_description LIKE 'BURK_';
-- would only return last names that start with BURK and have one more letters.
-- In the example above, BURKS and BURKE would be returned, but not BURKHART.Regular Expressions
| Expression | Output |
|---|---|
|
|
Any city that starts with “SA” |
|
|
Any city that contains “SA” or “IA” |
|
|
Any type equal to “vendor” or “vender” |
|
|
Any city that contains characters “SA” |
|
|
Any city that ends with “SA” |
|
|
Any city that contains the word “San” |
IS NULL
In SQL, NULL represents a missing or unknown value. It’s important to understand that NULL is different from zero or an empty string; it signifies the absence of any value. Use IS NULL or IS NOT NULL to filter results based on whether a column has missing data. Properly handling NULL values is crucial for accurate data analysis and reporting.
SELECT * FROM orders WHERE shipping_terms IS NULL;
-- This might mean “show me orders that are not complete yet”.SELECT * FROM orders WHERE shipping_terms = 'None';
-- Might mean “show me orders where the customer will pick up”.AS Keyword
Creating an alias (new column title) using AS
Often a column within the TABLE is not named in a manner that’s useful for the person viewing the result set. Or, as a business rule, a company chooses not to display actual column names in result sets for security reasons. To add a descriptive name, called an alias, to a column within a result set use the AS keyword within the SELECT clause.
SELECT last_name AS Name from roster;If you wish to embed a space in the alias to create a user friendly alias put the alias text within quotes.
SELECT last_name AS 'Last Name' from roster;Coding Arithmetic Expressions
Calculated values, using math expressions to determine the value based on two or more column values, are rarely stored in the database itself. Instead a calculation is done when a query is executed. When using calculations, unless you specify an AS clause the column name is rendered as the expression in the result set.
SELECT last_name, graded_points - late points FROM roster;| last_name | graded_points – late_points |
|---|---|
|
Smith |
95 |
SELECT last_name AS NAME, graded_points – late points AS "Total Points" FROM roster;| NAME | Total Points |
|---|---|
|
Smith |
95 |
Order of Operations
MySQL evaluates math operators left to right based on the order of operations. Remember from your algebra class? first exponents, then multiplication and division, finally addition and subtraction. For example, assume a column named Amount has a value of 100.00. We want to add a $3 handling fee and then discount it 5%:
SELECT Amount, Amount + 3 * .95 AS Discount FROM Invoices;
-- would result in this because the multiplication is done before the addition.| Amount | Discount |
|---|---|
|
100.00 |
102.85 |
To execute as intended, we need to change the order of operations which we can do using parentheses. Any calculations inside of parentheses are executed before those outside of the parentheses.
SELECT Amount, (Amount + 3) * .95 AS Discount FROM Invoices;
-- would result in this.| Amount | Discount |
|---|---|
|
100.00 |
97.85 |
Rule of Thumb: When coding equations with multiple operators, always use parentheses to ensure it executes as you intended.
CONCAT function
Often data is stored in its smallest logical component. At times it is useful to concatenate multiple text values into a single value. MySQL uses the CONCAT function to accomplish this.
Consider towns. A database would store Springfield in a column named City. This in and of itself is not very useful. Springfield Illinois? Springfield Massachusetts? Springfield Missouri?
The unique identifier of the place Abraham Lincoln is buried is Springfield, Illinois not just Springfield. A query that would combine City and State into one string would be written this way.
SELECT last_name, CONCAT(City, State) AS 'TOWN' FROM BURIED;
-- would give us this| last_name | TOWN |
|---|---|
|
Lincoln |
SpringfieldIllinois |
Notice that the concatenated text string is not very readable. When using CONCAT it is necessary to add punctuation to the concatenate string.
SELECT last_name, CONCAT(City, ', ', State) AS 'TOWN' FROM BURIED;
-- would give us this| last_name | TOWN |
|---|---|
|
Lincoln |
Springfield, Illinois |
ROUND function
Often a decimal type column in a database has more precision than is required for a result set. For example a number such as 136.67878888888888 looks unnecessarily precise when it is displayed as currency. Use the round function on a decimal type value to ROUND the number and optionally set the number of decimal places to round to.
Suppose the number above is a value stored in a column called Owed in a Table called Invoices.
SELECT ROUND(Owed) FROM Invoices;
-- would result in "137".
-- Notice that if no precision parameter is set,
-- the function rounds to the nearest Integer (Whole Number).
SELECT ROUND(Owed, 1) FROM Invoices;
-- would result in "136.7"
SELECT ROUND(Owed, 2) FROM Invoices;
-- would result in "136.68"DISTINCT Keyword
Use the DISTINCT keyword in a SELECT statement directly after the SELECT clause to remove duplicate rows from your result set. This is particularly useful when you want to generate reports that only show unique values, such as a list of states where your company has business operations, ensuring that each state is only listed once.
For example if you wanted to only the display each state that has a company you do business with, and you only wished to display the state name once, you might use DISTINCT as a keyword.
SELECT DISTINCT(state_name) from ORDERS;Conclusion
These advanced SQL functions and keywords are essential tools for refining your queries and retrieving meaningful data from your database. As you work through your lab exercises, remember to experiment with these features to gain a deeper understanding of how they can be applied in real-world scenarios. Mastery of these concepts will enhance your ability to write efficient and effective SQL queries.