Technology – The Difference Between Float Vs. Double Data Types

Advertisements

It would be incorrect to say that floating-point numbers should never be used as an SQL data type for arithmetic. I will stick to double-precision floating-point data types for SQL Server that are suitable for my requirements.

The double-precision floating-point data type is ideal for modeling weather systems or displaying trajectories but not for the type of calculations the average organization may use in the database. The biggest difference is in the accuracy when creating the database. You need to analyze the data types and fields to ensure no errors and insert the data values for maximum accuracy. If there is a large deviation, the data will not be processed during the calculation. If you detect incorrect use of the data type with double precision, you can switch to a suitable decimal or number type.

What are the differences between numeric, float, and decimal data types, and should they be used in which situations?

  • Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value
  • Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators

For example, suppose the data that the report has received is summarized at the end of the month or end of the year. In that case, the decimal data for calculation becomes integer data and is added to the summary table.

In SQL Server, the data type float _ n corresponds to the ISO standard with a value from n = 1 to 53. The floating-point data is approximated not by the data type’s value but by the range of what can be represented. Both float- and float-related numeric SQL types consist of a significant numeric value and an exponent, a signed integer that indicates the size of the numeric value.

And float-related numeric SQL data types are precise positive integers that define the number of significant digits and exponents of a base number. This type of data representation is called floating-point representation. A float is an approximate number, meaning that not all values can be displayed in the data type range because it is a rounded value.

You can’t blame people for using a data type called Money to store the money supply. In SQL Server, decimal, number, Money, and SmallMoney data types have a decimal place to store values. Precision means the total number of digits after the decimal point.

From a mathematical point of view, there is a natural tendency to use floats. People who use float spend their lives rounding up values and solving problems that shouldn’t exist. As I mentioned earlier, there are places where it makes sense to hover above the real, but these are for scientific calculations, not business calculations.

SmallMoney (2144783647, 4 bytes) We can use this data type for Money- or currency values. The double type can be used as a data type with real values for dealing with Money.

Type Description Memory bits Integer 0 1 null TinyInt allows integers 0 to 255 1 bytes TinyInt allows integers 32767 2 bytes Int allows integers 2147483647 4 bytes BigInt allows integers 9223372036854775807 8 bytes Decimal P is a precisely scaled number. The parameter p specifies the maximum total number of digits stored to the left or right of the decimal point. The data type low and upper range storage observations Real 340E 38 4 Bytes We can use float924 as an ISO synonym for real.

In MariaDB, the number of seconds has elapsed since the beginning of the 1970s (01-01) with a decimal accuracy of 6 digits (0 is the default). The same range of precision is the SQL Server type range (bytes) MariaDB type range size (bytes) Precision notes Date 0001 01-01-99.99 12: 31: 3 They cover the same range: Date 0.001-03-01 9.99912: 31 8: 0: 3 Round DateTime 0.01 0.1-02.9999 12: 31 8 0: 6 In MariaDB the value is near impossible to specify (see below). We can insert a value that requires fewer bits than that assigned to the null-bit pad on the left.

A binary string is a sequence of octets, not a character set, and the associated sorting is described by the binary data type descriptor. Decimal (p) is the exact numerical precision (p scale (n)) of a decimal number that is any number with a decimal point. A Boolean data type consists of different truth values (true, false, and boolean), and it supports unknown truth values, zeroes, and forbidden (not zero) constraints.

This syntax was deprecated in MySQL 8.0.17.7 and will be removed in future versions of MySQL: float (p) A floating-point number. MySQL uses the p-value to specify whether to use a float or a double due to the data type.

Creating data types in PostgreSQL is done with the create-type command. For example, the following commonly used data types are organized into categories with a brief description of the value range and memory size. The native data type is the text data type, the numeric data type, and the date/time Boolean data type.

To understand what floating-point SQL is and what numerical data types are, you need to study computer science a little. Floating-point arithmetic was developed when saving memory was a priority and was used as a versatile method for calculating large numbers. The SQL Prompt Code Analysis Rule (BP023) warns you when using Floating over Real data types. It introduces significant inaccuracies into the type of calculations that many companies do with their SQL Server data.

The difference between a float and a p is that a real float is binary (not decimal) and has an accuracy equal to or greater than the defined value.

The reason for this difference is that the SQL standard specifies a default from 0 to D. Still, the implementation is free to choose a default M. This means that an operation of this type will result in a result different from the result it would produce for MariaDB type if you use enough decimal places. It is important to remember that numerical SQL data types sacrifice precision ranges to approximate the names.

Technology – What Are The SQL Minus and Except Clauses

Advertisements

Structured Query Language (SQL) is the de-facto query language used in most database management systems (DBMS) such as Oracle and Microsoft SQL Server. This domain-specific language is used in programming to query and return the desired data from a database. We use SQL to write queries that declare what data to expect from a dataset without really indicating how to obtain it. We can also use SQL to update and delete information from a database.

Ideally, in a relational database management system, the database usually runs on the “back end” side of a server in its structured form. By itself, this data is hard to interpret. So, users often have programs on a client computer that help to manipulate that data using rows, columns, fields, and tables. These programs are designed to allow users to send SQL statements to the server. The server then processes these statements by filtering data from the enormous, complex databases and returns results to the user.

Each query begins with finding the data needed then refining it down into something that can be processed and understood easily. To do this, you must use an organized set of operations to get meaningful data from a dataset. This article will explore the Minus Vs except SQL clauses to help you write optimized queries that run fast across various DBMS.

SQL EXCEPT clause

The SQL EXCEPT clause is one of the most commonly used statements that work together with two SELECT statements to return unique rows from a dataset. The SQL EXCEPT combines two SELECT statements to return the row that is present in the first select statement and not in the second.

If you’ve noticed, most SQL clauses do the same thing represented in standard spoken language. For instance, exception literally means not included. SQL EXCEPT is also very similar to the same concept.

The EXCEPT statement returns the distinct row from the left input query that is not output by the right input query. I.e., Returns the resultant rows that appear in query_expression_1 and not in query_expression_2.

SQL EXCEPT Clause Example

Consider a simple situation where you have two tables, one with dog names and the other one with cat names.

Cats Data Set

+———+———–+

| CatId | CatName |

|———+———–|

| 1 | Boss |

| 2 | Scarlet |

| 3 | Fluffy |

| 4 | Fluffy |

+———+———–+

Dogs Data Set

+———+———–+

| DogId | DogName |

|———+———–|

| 1 | Yelp |

| 2 | Woof |

| 3 | Boss |

| 4 | Boss |

+———+———–+

Using the SQL Except statement, we can filter the dataset and return only the distinct rows from the left SELECT query that have not been returned by the SELECT query on the right side of the EXCEPT statement.

An example SQL syntax query would look like this:

SELECT CatName FROM Cats

EXCEPT

SELECT DogName FROM Dogs;

In a typical scenario, a client program will send this query to the “back-end” server. This statement is then processed and only returns the values available in the “cats” dataset that don’t appear in the “dogs” dataset. When two rows are similar, as is the case with “fluffy,” only one row is returned. This is because the SQL query only returns distinct rows.

Here’s the result of the above query:

+———–+

| CatName |

|—————|

| Fluffy |

| Scarlet |

+————– +

Common SQL Except Rules
  • You must have the same number of columns in both queries
  • The column order must be the same in all queries
  • The column data types must be compatible with each other. The data types really don’t have to be the same, but they MUST be comparable through implicit conversion.
  • The EXCEPT statement returns all records from the 1st SELECT statement not available in the second SELECT statement
  • The EXCEPT operator in the SQL server is similar to the MINUS operator in Oracle.
  • MySQL does not support SQL Except clause. The workaround is to use the LEFT JOIN clause when using MySQL.

SQL MINUS Clause

MINUS operator does the same thing as the EXCEPT clause. But unlike the EXCEPT clause, the MINUS operator is only supported by Limit number of databases:

Database NameMinusExcept
Amazon RedshiftNoYes
VQL (denodo)YesNo
ElasticsearchYesNo
MariaDBYesYes
IBM Db2NoYes
Microsoft SQL ServerNoYes
MongoDBNoNo
MySQLNoNo
OracleYesNo
PostgreSQL No Yes
SOQL (salesforce)NoNo
snowflakeYesYes
SQLiteNoYes

The MINUS operator compares two queries and only returns the rows present in the first dataset but are not output by the second set. The result usually contains the distinct rows available in the left Select statement that aren’t included in the results of the right select statement.

Here is a typical MINUS syntax:

SELECT column_list_1 FROM T1

MINUS

SELECT column_list_2 FROM T2;

Common Oracle Minus Operator Rules

For MINUS operator to work, the dataset must conform with rules similar to those of SQL EXCEPT clause:

  • The data type of corresponding columns must be similar (Either Numeric or Character)
  • The order and number of columns must be the same.
  • The column used for ordering can be defined by the column number.
  • Duplicates are automatically eliminated in the final result.

Conclusion

The Minus Vs Except SQL Clause comparison can be confusing for many people. These two clauses are synonymous with each other and have similar syntax and results. Both Minus and Except help users skim through datasets to identify unique rows available only in the first SELECT query and not returned by the second SELECT query.

SQL Server Length Function Equivalent

Advertisements

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

Netezza / PureData – Substring Function Example

Advertisements

The function Substring (SUBSTR) in Netezza PureData provides the capability to parse character type fields based on position within a character string.

Substring Functions Basic Syntax

SUBSTRING Function Syntax

SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)

SUBSTR Function Syntax

SUBSTR((<>,<< StartingPosition integer>>, <>)

Example Substring SQL

Netezza / PureData Substring Example

Substring SQL Used In Example

SELECT  LOCATIONTEXT

— From the Left Of the String

— Using SUBSTRING Function

,’==SUBSTRING From the Left==’ as Divider1

,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT

,’==SUBSTR From the Left==’ as Divider2

—Using SUBSTR Function

,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT

— From the right of the String

,’==SUBSTRING From the Right==’ as Divider3

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT

,’==SUBSTR From the right==’ as Divider4

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT

FROM BLOG.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’;

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Casting Numbers to Character Data Type

Advertisements

I noticed that someone has been searching for this example on my site, so, here is a quick example of how to cast number data to a character data type.  I ran this SQL example in Netezza and it worked fine.

Basic Casting Conversion Format

cast(<<FieldName>> as <<IntegerType_or_Alias>>) as <<FieldName>>

Example Casting Number to Character Data Type SQL

SELECT
—-Casting Integer to Character Data Type —————

SUBMITDATE_SRKY as  SUBMITDATE_SRKY_INTEGER
, cast(SUBMITDATE_SRKY as  char(10)) as Integer_to_CHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Nchar(10)) as Integer_to_NCHAR
, cast(SUBMITDATE_SRKY as NVarchar(10)) as Integer_to_NVARCHAR

—-Casting Double Precision Number to Character Data Type —————

, HOST_CPU_SECS as DOUBLE_PRECISION_NUMBER
, cast(HOST_CPU_SECS as  char(30)) as DOUBLE_PRECISION_to_CHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Nchar(30)) as DOUBLE_PRECISION_to_NCHAR
, cast(HOST_CPU_SECS as NVarchar(30)) as DOUBLE_PRECISION_to_NVARCHAR

—-Casting Numeric to Character Data Type —————

, TOTALRUNTIME  as NUMERIC_FIELD
, cast(TOTALRUNTIME as  char(30)) as NUMERIC_FIELD_to_CHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Nchar(30)) as NUMERIC_FIELD_to_NCHAR
, cast(TOTALRUNTIME as NVarchar(30)) as NUMERIC_FIELD_to_NVARCHAR
FROM NETEZZA_QUERY_FACT ;

 

 

Related References

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza stored procedures, NZPLSQL statements, and grammar, Variables, and constants, Data types, and aliases

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, SQL statement grammar, Explicit and implicit casting, Summary of Netezza casting

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions

Netezza / PureData – SQL Cast Conversion to Integers

Advertisements

From time to time, I have had to look up this information, especially, when working with old school SQL intensive ETL patterns where the transformations are being performed in SQL rather than within the ETL application.  So, rather than scrambling to find the differing integer casting range limits, bit type conversions, and alias for different integer types, and pieces it together, I thought I would consolidate it here for future reference.

There are some important rules in the summary of Netezza casting reference below, but here are a few, which I have found useful to remember:

  • It is safer to convert from a small to a larger integer type, otherwise, the input values may not fit
  • You may need to apply other data treatments to handle special characters, and decimals to prevent casting errors.

Basic Casting Conversion Format

cast(<<FieldName>> as <<IntegerType_or_Alias>>) as <<FieldName>>

Example Casting Integers SQL

Select

cast(127 as BYTEINT ) as  BYTEINT_8_Bit,

cast(127 as Int1) as  BYTEINT_ALIAS,

cast(32767 as SMALLINT ) as  SMALLINT_16_Bit,

cast(32767  as Int ) as SMALLINT_ALIAS,

cast(2147483647 as INTEGER ) as INTEGER_32_Bit,

cast(2147483647 as Int) as INTEGER_ALIAS_1,

cast(2147483647 as Int4 ) as INTEGER_ALIAS_2,

cast(9223372036854775807 as BIGINT ) as BIGINT_64_bit,

cast(9223372036854775807 as Int8 ) as BIGINT_ALIAS_1

From _v_dual;

Table Of Netezza Integer Types

Type

Alias Names

Bit Size

Value Range

BYTEINTINT18-bit –128 to 127
SMALLINTINT216-bit –32,768 to 32,767
INTEGERINT, INT432-bit –2,147,483,648 to 2,147,483,647
BIGINTINT864-bit-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, SQL statement grammar, Explicit and implicit casting, Summary of Netezza casting

Technology – What is the difference between left join and left outer join in SQL

Advertisements

If you’re confused between a LEFT JOIN and a LEFT outer JOIN in SQL, you’re not alone. There are plenty of examples that explain both. In this article, we’ll explore LEFT JOIN, which combines the records from table1 with any matched records in table2, and OUTER JOIN, which combines records from both tables.

RIGHT joins a set of records from table1

The RIGHT JOIN clause selects rows from table2 that match the criteria in the left-hand column. The RIGHT JOIN does not include NULL values in the left-hand column. Hence, the results from this query are the same as those from the LEFT JOIN. Here, the example of a RIGHT JOIN is provided to illustrate its use.

An example of a right outer join is when an employee’s name matches the name of a person. Using this outer join, we can return all employees from the left-hand table. In the same way, the right outer join returns all employees in the table2 whose name matches the employee’s name. If the relationship between an employee and a person is 0..1, then the right join will not return any non-matching rows. The same applies to an inner join.

The RIGHT JOIN is similar to the LEFT JOIN, except that it returns the inner rows of the right-hand table instead of the outer rows. In this case, the left-hand table contains the records of the ProductReviews and the CustomerReviews tables. However, the RIGHT OUTER JOIN includes all the rows in table1. This is a large result set, but it works.

RIGHT joins a set of records in table2 and a set of records in table1. As with the LEFT JOIN, the RIGHT JOIN includes non-matching rows in the left-hand table. It returns NULL values in rows when no match is found. As you can see, the RIGHT JOIN can be used to connect multiple tables. This query retrieves all records from table1 and matches them in table2 with records from the right-hand table.

When using the RIGHT JOIN, you must include the condition/restriction in the on-condition of the SQL statement. The condition is evaluated before the join. In contrast, the ON-JOIN is evaluated after the join. If the indicator is empty, the condition does not exist. By default, the indicator is empty. You can use either one of them to join data. You can create multiple conditions using the two RIGHT JOIN syntax.

In a LEFT JOIN, all the entries in the first table are read first. The right join, on the other hand, receives the entries in the second table first. The ON table specifies the fields that connect the two tables. The result set value will be NULL if there are no matching records in the right table. The LEFT JOIN and the RIGHT JOIN have their own advantages and disadvantages.

LEFT JOIN combines records from table1 with matched records from table2

LEFT JOIN is used to join two or more tables. For instance, if you have two tables and want to find the names of each employee in each, you can use the LEFT JOIN. The results will be the same – 603 rows for LEFT JOIN and 604 rows for RIGHT JOIN. The difference between these two join types is the type of data to be joined.

If the two tables have the same names, a LEFT JOIN can be used to match the values. The ON clause is usually used for columns that are the same. It takes a list of shared column names from both tables and compares them to find a match. The LEFT JOIN will then combine records from table1 with matched records from table2.

A LEFT JOIN also allows you to use a UNION clause. This clause will result in an ordered list of records. For example, a LEFT OUTER JOIN would combine records from table1 with matched records from table2. If the two tables are the same type, the LEFT OUTER JOIN is an appropriate choice.

LEFT JOIN combines records from two tables into a single row. Unlike RIGHT JOIN, this join type will return rows that match the columns of both tables. For example, if the comparison column of two tables contains the value “blue”, then this row would be suppressed from the output. With the inner JOIN, the overlapping area of two circles is displayed as one.

A LEFT JOIN retrieves all rows from table1 and table2 that match. If no matching records are found in either table, a NULL value is returned. The LELT JOIN, also known as left OUTER JOIN, is also a powerful way to combine records from two tables. You can use this query to search for common records in both tables.

Another SQL Join is the LEFT JOIN. This join uses two or more tables in an outer way, combining rows with mutual columns. In a simple example, two tables contain information on employee and department. For each employee, the employee and department information must be combined in the right table. The LEFT JOIN returns all rows from both tables, including NULL rows.

OUTER JOIN combines records from both tables

The OUTER JOIN is a type of relational query that combines records in both tables. It can be used when the data in both tables has similar fields. For example, a database that contains both records for Jane Smith and records for the rest of the population may use an outer join to find out how many of each of those people are wearing the same color pants and shirt. It works in the same way as an inner join, but it ignores the order of the tables.

An outer join involves using a common column in both tables to link them together. For example, an employee’s ID could be a foreign key in an employee salary table, and a primary key in the employee details table. This common key can link two entities. SQL provides two types of outer joins, or “join,” clauses. The Outer Join can be divided into Right Outer Join, Left Outer Join, and Full Outer Join.

In the right outer join, the data in the right table matches the data in the left table, returning all employees with the same name and nationality. Similarly, the LEFT OUTER JOIN works similarly to the RIGHT OUTER JOIN. It selects data from the right table and matches it against the rows in the left table. The right outer join returns a result set that includes all the rows in the right table. NULL values will be listed in the left table.

OUTER JOIN combines records in both tables. Its name means “joining records from both tables.” Using this method, a single query will return all records from both tables. This method is used to find duplicate records. It is also useful in combining data from multiple tables to find the most popular records. Its benefits far outweigh the disadvantages. So, if you have an important dataset, an Outer JOIN will help you find it.

An inner join (also known as a simple join) only returns rows that satisfy a join condition. By contrast, an outer join (or a super-join) will return all rows that satisfy a join condition, even if the conditions are not met. Its main difference lies in the way it handles false matches. The former will return the most matches. The latter is best for storing a list of employees without regard to a person’s employment status.

An outer join can be used to fill gaps in sparse data. This can include a partitioned outer join (QOJ), which uses the query-partition clause to generate an inner table with rows that have no data. Ultimately, filling data gaps is important in these situations. However, it can complicate analytic computation, and miss out on data when queried directly.

 LEFT OUTER JOIN IN SQL