How to select multiple columns based on a condition in SQL?

ERROR 1241 (21000): Operand should contain 1 column(s).

This error often appears when you try to make a SQL CASE expression return multiple columns at once. If you're asking "how to select multiple columns based on a condition in sql?", the first thing to understand is the difference between filtering rows and changing which column values appear in your results. Both are possible, but they require different SQL techniques.

Quick answer: To select multiple columns based on a condition, list the required columns in SELECT and filter matching rows using WHERE. If individual column values should change depending on a condition, use separate CASE expressions for each output column. If the actual number or names of returned columns must change, use separate queries or safely constructed dynamic SQL.

Here's how to implement each approach in MySQL without running into confusing syntax errors.

1. Create a sample table to understand conditional column selection

We'll use an employee table throughout this tutorial.

Imagine you're building an employee management dashboard. Your database stores employee names, departments, salaries, and employment status.

You want to answer questions such as:

• Show the names and salaries of active sales employees.
• Display different salary columns depending on the department.
• Return salary totals for active and inactive employees.
• Change the columns displayed when someone selects a different report.

Start by creating a temporary table.

Open MySQL Workbench, phpMyAdmin, or your preferred MySQL client and execute:

DROP TEMPORARY TABLE IF EXISTS demo_employees;

CREATE TEMPORARY TABLE demo_employees (
    employee_id INT PRIMARY KEY,
    full_name VARCHAR(80) NOT NULL,
    department VARCHAR(30) NOT NULL,
    annual_salary DECIMAL(10,2) NOT NULL,
    status VARCHAR(12) NOT NULL
);

Now insert some sample records:

INSERT INTO demo_employees
    (employee_id, full_name, department, annual_salary, status)
VALUES
    (1, 'Ana', 'Sales', 54000.00, 'active'),
    (2, 'Ben', 'Support', 47000.00, 'active'),
    (3, 'Cara', 'Sales', 62000.00, 'inactive'),
    (4, 'Deepak', 'Engineering', 78000.00, 'active'),
    (5, 'Ema', 'Sales', 51000.00, 'active'),
    (6, 'Farah', 'Support', 50000.00, 'inactive');

Confirm that the records exist:

SELECT * FROM demo_employees
ORDER BY employee_id;

You should see six employees across three departments.

We're using a temporary table so the exercise doesn't modify an existing application table. Run all the SQL examples using the same database connection, because MySQL temporary tables disappear when their connection closes.

If you're testing through PHP and the connection fails before your query runs, check the mysqli_connect host parameter explained. Connection problems and SQL syntax problems need different fixes.

2. Select multiple columns using a WHERE condition

This is the simplest solution, and it's usually all you need.

Suppose your dashboard should show only active employees from the Sales department.

You need three output columns:

• Employee ID
• Employee name
• Annual salary

The condition belongs in WHERE, not inside the column list.

SELECT
    employee_id,
    full_name,
    annual_salary
FROM demo_employees
WHERE department = 'Sales'
  AND status = 'active'
ORDER BY employee_id;

Expected result:

employee_id	full_name	annual_salary
1	Ana	54000.00
5	Ema	51000.00

Notice what SQL does here.

SELECT decides which columns appear in the result.

WHERE decides which rows qualify.

These are independent parts of the query. You can return five columns while filtering by a sixth column, even if that sixth column isn't displayed.

For example:

SELECT
    employee_id,
    full_name,
    annual_salary
FROM demo_employees
WHERE status = 'active';

The query filters using status, but doesn't include status in the returned columns.

That's perfectly valid.

Select columns using multiple conditions

You can combine conditions with AND, OR, or IN.

For employees belonging to either Sales or Support:

SELECT
    employee_id,
    full_name,
    department
FROM demo_employees
WHERE department IN ('Sales', 'Support')
  AND status = 'active'
ORDER BY employee_id;

Expected employee IDs: 1, 2, and 5.

You could write the same department condition using OR, but IN is easier to read when you're checking one column against several values.

One mistake I frequently recommend checking for is missing parentheses when mixing AND and OR.

This query may return unexpected results:

SELECT
    employee_id,
    full_name,
    department,
    status
FROM demo_employees
WHERE department = 'Sales'
   OR department = 'Support'
  AND status = 'active';

SQL evaluates AND before OR.

So inactive Sales employees can still appear.

If you want active employees from either department, write:

SELECT
    employee_id,
    full_name,
    department,
    status
FROM demo_employees
WHERE (department = 'Sales'
       OR department = 'Support')
  AND status = 'active';

Better still, use the earlier IN example.

3. How to select multiple columns based on a condition in SQL?

Sometimes filtering rows isn't the real requirement.

You want to return every employee, but show different values depending on their department.

For example:

• Sales employees should have their salary in sales_salary.
• Support employees should have their salary in support_salary.
• Everyone else should have NULL in those columns.

This is where CASE becomes useful.

Use a separate CASE expression for each column

SELECT
    employee_id,
    full_name,

    CASE
        WHEN department = 'Sales'
        THEN annual_salary
        ELSE NULL
    END AS sales_salary,

    CASE
        WHEN department = 'Support'
        THEN annual_salary
        ELSE NULL
    END AS support_salary

FROM demo_employees
ORDER BY employee_id;

Expected result:

employee_id	full_name	sales_salary	support_salary
1	Ana	54000.00	NULL
2	Ben	NULL	47000.00
3	Cara	62000.00	NULL
4	Deepak	NULL	NULL
5	Ema	51000.00	NULL
6	Farah	NULL	50000.00

All six employees appear because there's no WHERE condition.

However, each calculated column displays a salary only when its condition matches.

There's an important detail here: Cara and Farah are inactive, but their salaries still appear because these conditions only check the department.

If inactive employees shouldn't have a salary displayed, extend each condition.

SELECT
    employee_id,
    full_name,

    CASE
        WHEN department = 'Sales'
         AND status = 'active'
        THEN annual_salary
        ELSE NULL
    END AS sales_salary,

    CASE
        WHEN department = 'Support'
         AND status = 'active'
        THEN annual_salary
        ELSE NULL
    END AS support_salary

FROM demo_employees
ORDER BY employee_id;

Now inactive employees receive NULL in both salary columns.

Keep in mind that conditional display isn't a security control. Database permissions and application authorization must still protect sensitive salary data.

Why one CASE cannot return two columns

Here's a common incorrect attempt:

SELECT
    employee_id,

    CASE
        WHEN department = 'Sales'
        THEN (full_name, annual_salary)
        ELSE (department, status)
    END AS details

FROM demo_employees;

This produces an error similar to:

ERROR 1241 (21000): Operand should contain 1 column(s)

The problem isn't the condition.

It's the attempt to return two values from a single CASE expression.

A CASE expression produces one result value. It doesn't expand into multiple output columns.

The correct approach is to use separate expressions:

SELECT
    employee_id,

    CASE
        WHEN department = 'Sales'
        THEN full_name
        ELSE department
    END AS display_label,

    CASE
        WHEN department = 'Sales'
        THEN annual_salary
        ELSE NULL
    END AS display_salary

FROM demo_employees;

Each CASE produces one column.

You can also use MySQL's IF() function for simpler conditions:

SELECT
    employee_id,
    full_name,
    IF(
        department = 'Sales',
        annual_salary,
        NULL
    ) AS sales_salary
FROM demo_employees;

IF() works for a basic true-or-false decision.

I prefer CASE when several conditions are involved because it's easier to extend and is also standard SQL.

The MySQL documentation for CASE expressions explains the supported syntax.

4. Select multiple columns with different WHERE conditions

Another situation is filtering rows based on different combinations of column values.

Suppose the business rule is:

Return active Sales employees, plus Support employees whose annual salary is at least 50000.

The query is:

SELECT
    employee_id,
    full_name,
    department,
    annual_salary,
    status
FROM demo_employees
WHERE
    (department = 'Sales'
     AND status = 'active')

    OR

    (department = 'Support'
     AND annual_salary >= 50000)

ORDER BY employee_id;

Expected matching employees:

employee_id	full_name	department
1	Ana	Sales
5	Ema	Sales
6	Farah	Support

Farah appears even though her status is inactive.

Why?

Because the Support condition checks the salary, not the employment status.

This is intentional for the example, but it's exactly the kind of mistake that can cause incorrect reports in a real application.

If both groups must be active, apply the active condition to the entire expression:

SELECT
    employee_id,
    full_name,
    department,
    annual_salary
FROM demo_employees
WHERE status = 'active'
  AND (
        department = 'Sales'
        OR (
            department = 'Support'
            AND annual_salary >= 50000
        )
      )
ORDER BY employee_id;

Now Farah is excluded.

Don't confuse WHERE with CASE

The distinction is simple.

Use WHERE when a condition should remove entire rows.

Use CASE when a condition should change a value in the returned row.

You can combine both:

SELECT
    employee_id,
    full_name,

    CASE
        WHEN annual_salary >= 60000
        THEN 'High salary'
        ELSE 'Standard salary'
    END AS salary_category

FROM demo_employees
WHERE status = 'active'
ORDER BY employee_id;

Only active employees appear, and each employee receives a calculated salary category.

This pattern is useful for administrative dashboards and filtered reports.

5. Select conditional totals across multiple columns

Sometimes you don't need individual employee records.

You want a report containing several calculated columns based on conditions.

For example, show each department with:

• Total number of employees.
• Number of active employees.
• Number of inactive employees.
• Combined salary of active employees.

You can build this using conditional aggregation.

SELECT
    department,

    COUNT(*) AS total_employees,

    SUM(
        CASE
            WHEN status = 'active'
            THEN 1
            ELSE 0
        END
    ) AS active_employees,

    SUM(
        CASE
            WHEN status = 'inactive'
            THEN 1
            ELSE 0
        END
    ) AS inactive_employees,

    SUM(
        CASE
            WHEN status = 'active'
            THEN annual_salary
            ELSE 0
        END
    ) AS active_salary_total

FROM demo_employees
GROUP BY department
ORDER BY department;

Expected result:

department	total_employees	active_employees	inactive_employees	active_salary_total
Engineering	1	1	0	78000.00
Sales	3	2	1	105000.00
Support	2	1	1	47000.00

Here's how this works.

GROUP BY department creates one result row for each department.

COUNT(*) counts all employees in that department.

Each SUM(CASE ...) evaluates a condition for individual rows and adds the matching values.

For the active employee count:

SUM(
    CASE
        WHEN status = 'active'
        THEN 1
        ELSE 0
    END
)

A matching employee contributes 1.

A non-matching employee contributes 0.

For the salary total, the same approach adds salary amounts instead.

Why WHERE can break conditional totals

Don't add:

WHERE status = 'active'

to the aggregation query if you still need inactive employee counts.

That condition removes inactive rows before the grouping and aggregation happen.

Your inactive count would always be zero.

Keep both types of records available and let the individual CASE expressions handle the conditions.

There's another small detail.

Using ELSE 0 makes the total zero when a department has no matching employees. Without ELSE, the expression returns NULL for non-matching records, and SUM() may return NULL if every input is NULL.

That difference matters when your result feeds a chart or financial report.

6. Select different column names dynamically in MySQL

What if your requirement is more specific?

You want the SQL query itself to return different columns depending on a selected report.

For example:

Directory report:

SELECT
    employee_id,
    full_name,
    department
FROM demo_employees;

Payroll report:

SELECT
    employee_id,
    full_name,
    annual_salary
FROM demo_employees;

These queries have different output structures.

A normal CASE expression cannot switch the entire SELECT column list.

You need separate SQL statements or dynamic SQL.

Build a safe dynamic SELECT statement

MySQL supports prepared statements using PREPARE and EXECUTE.

For this example, we'll allow only predefined column lists.

SET @view = 'payroll';

SET @columns = CASE
    WHEN @view = 'payroll'
        THEN 'employee_id, full_name, annual_salary'

    WHEN @view = 'directory'
        THEN 'employee_id, full_name, department'

    ELSE 'employee_id, full_name'
END;

SET @sql = CONCAT(
    'SELECT ',
    @columns,
    ' FROM demo_employees',
    ' WHERE status = ?',
    ' ORDER BY employee_id'
);

SET @status = 'active';

PREPARE employee_stmt FROM @sql;

EXECUTE employee_stmt USING @status;

DEALLOCATE PREPARE employee_stmt;

With @view = 'payroll', the result contains employee IDs, names, and salaries.

Change the variable to:

SET @view = 'directory';

Then rerun the remaining statements.

You'll receive employee IDs, names, and departments instead.

This works because the complete SQL statement is built before MySQL prepares it.

The column list is selected from fixed, trusted alternatives.

Never concatenate an unchecked user-supplied column name directly into SQL.

Prepared statement placeholders such as ? work for values, not SQL identifiers or entire column lists.

If users can choose columns, map their choices to a list of permitted identifiers first.

For most applications, I would keep a small set of predefined queries rather than construct complicated SQL strings. It's easier to review and much harder to misuse.

7. Apply conditional column selection in Laravel

If you're using Laravel, the Query Builder makes ordinary column selection straightforward.

Suppose your actual database contains a permanent employees table with the columns used in our example.

To get active Sales employees:

use Illuminate\Support\Facades\DB;

$employees = DB::table('employees')
    ->select(
        'employee_id',
        'full_name',
        'annual_salary'
    )
    ->where('department', 'Sales')
    ->where('status', 'active')
    ->orderBy('employee_id')
    ->get();

This is the Laravel equivalent of our second step.

Choose different columns for different reports

You can select the column list in PHP before building the query.

use Illuminate\Support\Facades\DB;

$reportType = 'payroll';

$allowedColumns = [
    'directory' => [
        'employee_id',
        'full_name',
        'department',
    ],
    'payroll' => [
        'employee_id',
        'full_name',
        'annual_salary',
    ],
];

$columns = $allowedColumns[$reportType]
    ?? $allowedColumns['directory'];

$employees = DB::table('employees')
    ->select($columns)
    ->where('status', 'active')
    ->orderBy('employee_id')
    ->get();

The important part is the $allowedColumns mapping.

Your application chooses a known column list rather than accepting arbitrary SQL identifiers from a request.

For calculated columns, Laravel can also use raw SQL expressions with bound values.

use Illuminate\Support\Facades\DB;

$department = 'Sales';

$employees = DB::table('employees')
    ->select('employee_id', 'full_name')
    ->selectRaw(
        'CASE
            WHEN department = ?
            THEN annual_salary
            ELSE NULL
         END AS selected_salary',
        [$department]
    )
    ->orderBy('employee_id')
    ->get();

Here, Laravel binds the department value safely.

Don't interpolate untrusted values directly into selectRaw().

And remember that the earlier demo_employees table was temporary. The Laravel examples assume you've created a normal application table with the corresponding fields.

What if the columns come from different tables?

That's a different problem.

Suppose names are stored in employees, but salaries are stored in employee_salaries.

You'll need a JOIN to combine those records.

For a related example, see how to JOIN a second table in CodeIgniter Query Builder.

The principle is the same in Laravel or plain MySQL: a join combines related tables, while WHERE and CASE handle the conditions.

Verify the results before using the query in production

Before adding your final SQL to a controller or report, check the row counts and the actual result values.

Start with:

SELECT
    department,
    status,
    COUNT(*) AS employee_count
FROM demo_employees
GROUP BY department, status
ORDER BY department, status;

This tells you how many records exist for each department and status combination.

Now test the basic filter:

SELECT
    COUNT(*) AS matching_employees
FROM demo_employees
WHERE department = 'Sales'
  AND status = 'active';

Expected result:

matching_employees
------------------
2

Next, check a conditional calculation:

SELECT
    SUM(
        CASE
            WHEN department = 'Sales'
             AND status = 'active'
            THEN 1
            ELSE 0
        END
    ) AS active_sales_count
FROM demo_employees;

Expected result:

active_sales_count
------------------
2

Both methods should agree.

If they don't, inspect the conditions rather than immediately changing the SQL structure.

I'd also check these issues before blaming CASE:

• NULL comparisons: Use IS NULL and IS NOT NULL, not = NULL.
• Unexpected duplicate rows: Look for joins that match multiple records.
• Incorrect data types: Confirm that the values being compared use compatible types.
• Missing columns: Run SHOW COLUMNS FROM demo_employees; to inspect the table.
• Incorrect column aliases: An alias defined in SELECT generally cannot be referenced directly in the same query's WHERE clause.

If a query runs but returns zero records, temporarily remove the WHERE clause and add conditions back one at a time.

That's usually more useful than rewriting the whole query.

If the query is slow on a large production table, use:

EXPLAIN
SELECT
    employee_id,
    full_name,
    annual_salary
FROM demo_employees
WHERE department = 'Sales'
  AND status = 'active';

EXPLAIN shows how MySQL plans to read the table.

For a real, persistent table, check whether indexes support your frequently used filters. Don't automatically add an index for every column, because indexes also cost storage and make writes more expensive.

For a second set of eyes on a production query, you can also check HeadsNTail services.

FAQ

Can I select multiple columns using one CASE statement in SQL?

Not using a single CASE expression in a normal SELECT list. Each CASE expression produces one result value, so you'll need a separate expression for every conditional output column. If you want to change the actual set of output columns, use separate queries or dynamic SQL.

How do I select multiple columns with multiple WHERE conditions?

List the columns after SELECT, then combine your filters using AND, OR, or IN. Use parentheses to make the intended logic clear when mixing AND and OR. Remember that WHERE filters rows, not individual column values.

What's the difference between CASE and IF in MySQL?

Both allow conditional values to be returned in a query. IF() takes a condition and two possible results, while CASE can express multiple conditions more clearly. CASE is standard SQL, making it a better choice when portability matters.

Can SQL dynamically select column names based on user input?

Yes, but the column list must be decided before the SQL statement is prepared and executed. You can choose between predefined queries in your application or construct a statement using validated identifiers. Parameter placeholders cannot replace column names.

Can I use WHERE and CASE together in the same SELECT query?

Yes. WHERE removes rows that don't match your filter, while CASE calculates conditional values for rows that remain. Using both is common in reporting queries where you need to filter employees and calculate different output values based on their departments or statuses.

Next, apply these patterns to a small copy of your real data. Once you understand the difference between filtering rows, calculating conditional values, and switching column lists, the question "how to select multiple columns based on a condition in sql?" becomes much easier to solve.