Common SQL Functions
How to use MySQL's CONCAT() to join text from several columns and COALESCE() to replace NULL with a default value.
Apprentice · SQL · MySQL · Updated
Each database engine defines several commonly used SQL functions. Many of these functions are specific to each database engine, but some are consistent between databases.
More MySQL Functions teaches the other functions you'll use most, grouped by the job they do.
CONCAT()
The CONCAT() function is used to combine text from multiple columns into a single string.
SELECT CONCAT(title_of_courtesy, ' '
, first_name, ' '
, last_name) AS full_name
FROM employees;
COALESCE()
The COALESCE() function allows you to provide a default value for a column that may contain NULL.
In the following example, GNPOld may contain NULL values.
SELECT Name
, GNP
, COALESCE(GNPOld, 0) AS GNPOld
, GNP - COALESCE(GNPOld, 0) AS ChangeInGNP
FROM country;
Do it: give every customer a place on the label
The shipping team prints a label for each customer, with a place to sort it by: the customer's region, such as OR for Oregon. Some customers have no region (it's NULL). For them, the label should show the country instead.
The customers table (the Tables tab shows the SQL that makes it) has these rows:
| company_name | city | region | country |
|---|---|---|---|
| Alfreds Futterkiste | Berlin | NULL | Germany |
| Around the Horn | London | NULL | UK |
| Bottom-Dollar Markets | Tsawassen | BC | Canada |
| Great Lakes Food Market | Eugene | OR | USA |
| Island Trading | Cowes | Isle of Wight | UK |
Your query should give exactly these rows, in this order:
| company_name | place |
|---|---|
| Alfreds Futterkiste | Germany |
| Around the Horn | UK |
| Bottom-Dollar Markets | BC |
| Great Lakes Food Market | OR |
| Island Trading | Isle of Wight |
Your job: write one SELECT query that gives each customer's place.
- Show two columns:
company_name, thenplace. placeis theregion, or thecountrywhen theregionisNULL.- Use
COALESCE, which works in every database. - Sort the rows by
company_name, A to Z.
Practiced live on the lesson page: write the SQL right there and check it.