CONCAT vs CONCAT_WS

quera.sql

# https://quera.org/college/8939/chapter/32512/lesson/108911/

-- Create a sample table
CREATE TABLE sample_table
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name  VARCHAR(50)
);

-- Insert sample data
INSERT INTO sample_table (first_name, last_name)
VALUES ('John', 'Doe'),
       ('Jack', NULL),
       ('Bob', 'Johnson');

select *
from sample_table;

-- Test CONCAT
SELECT id,
       CONCAT(first_name, ' ', last_name) AS full_name_concat
FROM sample_table;

-- Test CONCAT_WS
SELECT id,
       CONCAT_WS(' ', first_name, last_name) AS full_name_concat_ws
FROM sample_table;

CONCAT output:

ID	Full Name
1	John Doe
2
3	Bob Johnson

CONCAT_WS output:

ID	Full Name
1	John Doe
2	Jack
3	Bob Johnson