If you’re getting the error “ORA-01789: query block has incorrect number of result columns” in Oracle Database, then it’s probably because you’re trying to use an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the SELECT statements on either side of the operator return a different number of columns.

To fix this, simply ensure that both queries return the same number of columns.

Example of Error

Here’s an example of code that produces the error:

SELECT EmployeeName FROM Employees
UNION
SELECT CustomerId, CustomerName FROM Customers;

Result:

ORA-01789: query block has incorrect number of result columns

Here, I’m using the UNION operator to create a compound query. Unfortunately I’m getting an error, because I’ve included only one column in the first query, but two columns in the second.

Solution

The way to fix this problem is to make sure both queries return the same number of columns.

So we can either add a new column to the first SELECT statement:

SELECT EmployeeId, EmployeeName FROM Employees
UNION
SELECT CustomerId, CustomerName FROM Customers;

Result:

EMPLOYEEID	EMPLOYEENAME
1	Bart
1	Mia
2	Jan
2	Rohit
3	Ava
3	Peter
4	Ava
4	Rohit
5	Monish
6	Monish
7	Monish

Or we could remove one of the columns from the second query:

SELECT EmployeeName FROM Employees
UNION
SELECT CustomerName FROM Customers;

Result:

EMPLOYEENAME
Ava
Bart
Jan
Mia
Monish
Peter
Rohit

Be mindful that you can get vastly different results depending on the option you use. In fact, we got different results in our example above.

This is because the UNION operator returns distinct rows unless it’s appended with the ALL keyword. When we included the “ID” columns, this made some rows distinct when they wouldn’t have been had we only returned the “name” columns. Then when we excluded the “ID” columns, we got the unique values from the “name” columns.

If you actually want duplicate values returned, you can use the ALL keyword.

Therefore, we can modify our last example as follows:

SELECT EmployeeName FROM Employees
UNION ALL
SELECT CustomerName FROM Customers;

Result:

EMPLOYEENAME
Bart
Jan
Ava
Rohit
Monish
Monish
Monish
Mia
Rohit
Peter
Ava
Monish
Monish