Concatenation and Aliases
CONCAT(), perhaps the most useful of the text functions, deserves its own discussion, along with its frequent SQL companion, the alias. The CONCAT() function accomplishes concatenation, which is a fancy word that refers to appending multiple values together. The syntax for concatenation requires you to place, within parentheses, the various values you want assembled, in order and separated by commas:
While you canand normally willapply CONCAT() to columns, you can also incorporate strings, entered within single quotation marks. To format a person's name as Surname, First from two columns, you would use
CONCAT(last_name, `, `, first_name)
Because concatenation is used to create a new value, you'll want a new way to refer to the returned result. This is where the SQL concept of aliases comes in. An alias is merely a symbolic renaming. It works using the term AS:
SELECT CONCAT(last_name, ', ', first_name) AS name FROM users
The result of this query would be that all users in the table would have their name formatted as you might want it displayed, and the returned column would be called name.
You can, in your queries, make an alias of any column or table. That general syntax is:
SELECT column AS alias_name FROM table AS tbl_alias
To use concatenation and aliases:
1. | Display all of the client address information as one value ( Figure 6.5).
SELECT client_name, CONCAT(client_street, ', ', client_city, ', ', client_state, ' ',
 client_zip) AS address FROM clients;
This first use of the CONCAT() function assembles all of the address information into one neat column, renamed address (see the figure). If you wanted, you could add WHERE client_street IS NOT NULL and client_city IS NOT NULL and client_state IS NOT NULL to the query to rule out incomplete addresses. (Or you could just add one of those three clauses.)
| | | 2. | Select every expense, along with its description and category ( Figure 6.6).
SELECT expense_amount, expense_date,
CONCAT(expense_category, ': ', expense_description) FROM expenses,
expense_categories WHERE
expenses.expense_category_id = expense_categories.expense_ category_id;
In this query, I have performed a join so that I can display both expense and expense category information at the same time. The concatenation takes place over two columns from two different tables.
If you look at the column names in the image, you'll see the result of using functions without aliases.
| | | 3. | Show the three most-expensive invoices, along with the client name and identification number ( Figure 6.7).
SELECT invoices.*,
CONCAT(client_name, ' - ', clients.client_id) AS client_info
FROM invoices LEFT JOIN clients
USING (client_id)
ORDER BY invoice_amount DESC
LIMIT 3\G
To perform this query, I use a left join, order the results by the invoice_amount, and limit the results to just three records. The CONCAT() function is applied to the client's name and ID.
So that the results are easier to peruse, I use the \G modifier to terminate the query (this is a feature of mysql that I discuss in Chapter 5, "Basic SQL").
| | | 4. | Simplify the query from Step 2 using aliases for table names ( Figure 6.8).
SELECT expense_amount, expense_date,
CONCAT(expense_category, ': ', expense_description) FROM
expenses AS e,
expense_categories AS e_c
WHERE e.expense_category_id = e_c.expense_category_id;
The query itself is the same as it was in Step 2 except that I have simplified typing it by using aliases for the table names. In wordy queries and joins, this is a nice shorthand to use.
|
 Tips
CONCAT() has a corollary function called CONCAT_WS(),
which stands for with separator. The syntax is: CONCAT_WS(separator, column1, column2, ...). The separator will be inserted between each of the columns listed. A nice feature of this function is that it skips any NULL values. An alias can be up to 255 characters long and is always case-sensitive. The AS term used to create an alias is optional. You could write a query more simply as SELECT column alias_name FROM table
|
Js editor
Website development