MySQL IFNULL and CONCAT — MySQL

Problem

Concatenate name with department using IFNULL for missing dept.

Hints

CONCAT, IFNULL, LEFT JOIN

Your practice code

Ready — edit the code above and click Run.

Solution

-- MySQL schema
CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), dept_id INT, salary DECIMAL(10,2));
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO employees (name, dept_id, salary) VALUES ('Ali',1,90000),('Sara',2,120000);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales');
SELECT CONCAT(e.name, ' - ', IFNULL(d.name, 'Unknown')) AS label
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Try solving on your own first, then reveal the official answer.

Explanation

IFNULL and CONCAT are common MySQL interview functions.