Category: Relational

How to Remove a SQL Mode from sql_mode in MySQL

Posted on by Ian

MySQL can operate in different SQL modes. These can be set at server startup or at runtime.

DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements. Users can also set their own SQL mode at the session level via the @@sql_mode system variable.

By default, the sql_mode system variable contains a list of SQL modes that are applied.

As a user, we can modify our sql_mode system variable as required at runtime. This article demonstrates how we can remove a SQL mode from our sql_mode system variable.

Continue reading →

How to Remove NOT NULL from a Column in MySQL

Posted on by Ian

NOT NULL constraints are an important tool for maintaining data integrity in SQL databases. However, we may occasionally find that a NOT NULL constraint gets in the way of a legitimate operation that we want to perform, and it prevents us from entering data. In such cases, we may need to remove the NOT NULL constraint in order to proceed.

To remove a NOT NULL constraint in MySQL, we use the ALTER TABLE statement to redefine the relevant column without the NOT NULL constraint.

Continue reading →

MySQL ANY_VALUE() Function Explained

Posted on by Ian

In MySQL, ANY_VALUE() is an aggregate function that allows us to include nonaggregated columns in the SELECT list when using the GROUP BY clause.

The ANY_VALUE() function is commonly used to resolve issues that arise when a query includes columns that are not part of the GROUP BY clause or aggregate functions. It can be useful in scenarios where we want to include descriptive columns in a grouped result set without affecting the grouping behaviour.

Continue reading →

Fix Error 6358 “…is not a valid style number when converting to XML” in SQL Server

Posted on by Ian

If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.

It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.

Continue reading →

Understanding the VAR_SAMP() Function in MySQL

Posted on by Ian

In MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.

If there are no matching rows, or if the expression is NULL, VAR_SAMP() returns NULL.

VAR_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.

The VAR_SAMP() function can also be used as a window function.

Continue reading →

How VARIANCE() Works in MySQL

Posted on by Ian

In MySQL, the VARIANCE() function returns the population standard variance of an expression.

If there are no matching rows, or if the expression is NULL, the function returns NULL.

The VARIANCE() function is a synonym for the standard SQL VAR_POP() function. In other words, they both do the same thing, but VARIANCE() is not standard SQL. Therefore, if you need to use standard SQL, use VAR_POP() instead.

Continue reading →