Skip to content
The Chat Archive
qwen3-235b-a22b-no-thinking· Alibaba (Qwen) LMArena Human Preference 140K

In SQL, what function do I use to return the first non-null column per row?

  1. User

    In SQL, what function do I use to return the first non-null column per row?

  2. Assistant

    In SQL, the function you use to return the first non-null column per row is:

    COALESCE


    🔹 Syntax:

    COALESCE(value1, value2, ..., valueN)
    

    🔹 Description:

    • COALESCE returns the first non-null expression among its arguments, evaluated from left to right.
    • It's commonly used to substitute default values for NULLs or to pick the first available (non-null) value from multiple columns.

    🔹 Example:

    Suppose you have a table like this:

    idphone_homephone_workphone_mobile
    1NULL'555-1234'NULL
    2'555-5678'NULLNULL
    3NULLNULL'555-9876'

    You can get the first available phone number with:

    SELECT id,
           COALESCE(phone_home, phone_work, phone_mobile) AS contact_number
    FROM contacts;
    

    ✅ Result:

    idcontact_number
    1'555-1234'
    2'555-5678'
    3'555-9876'

    📝 Notes:

    • COALESCE is part of the SQL standard and works in most RDBMS (PostgreSQL, MySQL, SQL Server, Oracle, etc.).
    • All arguments must be of compatible data types.

    Let me know if you want an alternative like ISNULL, IFNULL, or NVL — those are vendor-specific and only take two arguments.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.