Category: MySQL

Fix “The used SELECT statements have a different number of columns” in MySQL

Posted on by Ian

If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.

When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.

To fix, make sure both queries select the same number of columns.

Continue reading →

Using VALUES as a Stand Alone Statement in MySQL

Posted on by Ian

MySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.

This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.

Continue reading →

5 Ways to Calculate the Difference Between Values in the Same Column (But Different Rows) in MySQL

Posted on by Ian

MySQL has a bunch of functions that enable us to get values from another row in the same column. This makes it easy for us to do stuff like calculate the difference between a value in the current row and one in another row, even if it’s in the same column.

Here are five options for calculating the difference between a value in the current row and a value in a different row but in the same column.

Continue reading →

5 Ways to Get a Value from a Different Row in the Same Column in MySQL

Posted on by Ian

MySQL includes some nonaggregate window functions that allow us to get a value from a specific row. We can use such functions to do things like, compare the value in the specified row with the value in the current row, even if both values are in the same column.

Below are five functions that we can use to do this.

Continue reading →

Calculate the Difference Between the Current Row and a Following Row in MySQL

Posted on by Ian

In MySQL, we can use the LEAD() function to get the value of a subsequent row. For example we can get a value from the next row, or the one after that, and so on.

This enables us to do things like compute the difference between a value in the current row and a value in a following row. We can do this even if both values are in the same column.

Continue reading →