Apr 3, 2012

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists


If we will execute following sql query in sql severs

SELECT 1 Col1 , 2 Co2
UNION
SELECT 5

Or

SELECT 1 Col1 , 2 Co2
EXCEPT
SELECT 5

We will get error nessage :

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Cause: It is necessary that all SELECT statement must have equals number of column if they are combined by either UNION, INTERSECT or EXCEPT operator. In above example first SELECT statement, selects two columns file second SELECT statement selects only one column.

Solution:

Correct ways to write of above select statements are:

SELECT 1 Col1 , 2 Co2
UNION
SELECT 5,NULL

Or

SELECT 1 Col1 , 2 Co2
EXCEPT
SELECT 5,NULL

3 comments:

  1. Hi! I´m getting that trouble, but I´ve the same fields on both queries(I´m using two queries and the EXCEPT clause)... It works fine on MS SQL Management Studio but I´m getting that problem on an Hibernate SQL Query.
    Do you know if is there another problem?
    Best regards, Hernán

    ReplyDelete
  2. SELECT Person.first_name, Person.last_name , Driver.first_name, Driver.last_name
    From Person
    INNER JOIN Driver
    ON Person.first_name = Driver.first_name
    UNION
    SELECT first_name, last_name From Driver WHERE Gender ='Male'

    im getting this error message "Msg 205, Level 16, State 1, Line 1
    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

    ReplyDelete
    Replies
    1. Issue is fisrt select statement is selecting for fields
      1. Person.first_name
      2. Person.last_name
      3. Driver.first_name
      4. Driver.last_name

      While second select statement is selecting only two fields:
      1. first_name
      2. last_name

      both select statement must have equal numbers of fields.

      Delete