The used SELECT statements have a different number of columns error resolution

Source: Internet
Author: User
Tags mysql version sql error

Yesterday, a SQL exception error message was encountered: Java.sql.SQLException:The used SELECT statements have a different number of Columns,sql I wrote one at random, It's probably like this:

SELECT
    s.s1 as N1,
    s.s2 as N2,
    Y.y2 as N3,
    S.s3 as N4
from
    S
INNER JOIN Y on s.s1 = y.y1
W Here
    s.s4 = ' student '
UNION all
    SELECT
        s.s1 as N1,
        s.s2 as N2,
        z.z2 as N3,
        S.s3 as N4
  from
        S
    INNER JOIN Z on s.s1 = z.z1
    WHERE
        s.s4 = ' teacher ' ORDER by
        N1 DESC;

Where the 4 column types selected by union All are the same, and the number of columns is 4, but in the query column, y-table y2 and Z-table z2 the conditions are different, but are linked to the S table, do not know why, local running up no problem, Breakpoint debugging go in and get the SQL out of here. and the SQL to test the database is no problem, but in the background when it will report the above SQL error, and then went to check the online newspaper this error, all said is the number of columns inconsistent or there is a certain column type is not the same, I looked at the Basically no problem ah.

Finally, to inquire about the difference between Union and union all, the following is excerpted from the Internet:

The Union and UNION ALL keywords combine two result sets into one, but both are different in terms of usage and efficiency.
1. Treatment of repeated results: Union will filter out duplicate records after the table is connected, and union all will not remove duplicate records.
2, the sort of processing: Union will be sorted in the order of the fields; Union all simply merges the two results and returns.
In terms of efficiency, union All is much faster than union, so if you can confirm that the merged two result sets do not contain duplicate data and do not need to be sorted, then use UNION ALL.

The functionality I want to implement can contain duplicate data, so I use UNION all, but I also need to sort out the results of the query, but the following is the process of sorting, and UNION all simply merges the two results and returns. So I wonder if that's the problem that was caused by N1 DESC, so I changed the SQL, and then I changed the following:

SELECT * FROM (select
    s.s1 as N1,
    s.s2 as N2,
    Y.y2 as N3,
    S.s3 as N4 to INNER
JOIN Y on S. S1 = y.y1
WHERE
    s.s4 = ' student '
UNION all
    SELECT
        s.s1 as N1,
        s.s2 as N2,
        z.z2 as N3,
  s.s3 as N4
    from
        S
    INNER JOIN Z on s.s1 = z.z1
    WHERE
        s.s4 = ' teacher ') T
        ORDER by t.n1 DESC;

The data identified by union all as a temporary table T, and then sorted by the t.n1 to find out, finally no problem, but the efficiency is not very good estimate.

It is not clear whether the MySQL version or sensitive issues caused, the above content is for reference only, thank you.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.