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

By

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, then place.
  • place is the region, or the country when the region is NULL.
  • 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.

Cheat sheets