# VECTOR\_SUB

The `VECTOR_SUB` function subtracts the second vector from the first vector and returns a vector which is the result of that subtraction.

## Syntax

```sql
VECTOR_SUB(vector_expression, vector_expression)

```

## Arguments

* `vector_expression`: An expression that evaluates to a vector. Vectors can be stored in SingleStore using the native `VECTOR` type ([Vector Type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md)) or the `BLOB` type ([Binary String Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/binary-string-types.md)). SingleStore recommends using the `VECTOR` type when possible.
* JSON strings are allowed as `vector_expression`s when the other argument is of type `VECTOR`.

## Return Type

If one of the two arguments is of type `VECTOR`, the function will return a `VECTOR` of the same type as that argument.

If both arguments are `BLOB`s, then the return type will be by default a `BLOB` that contains a vector encoded as 32-bit floating point numbers (`F32`). Alternatively if a suffix is used, the return type will be a `BLOB` that contains a vector encoded using the type of the suffix.

See [Using Suffixes for Other Element Types with BLOBs](https://docs.singlestore.com/#UUID-317e1af0-af41-98cb-f6a1-31cedf998e40.md) for information on using suffixes with vectors encoded as `BLOB`s.

## Remarks

* If both arguments are of type `VECTOR`, those arguments must have the same element types and the same number of elements. 
* If one argument is a `VECTOR`, the other argument (which may be a JSON string or a `BLOB`) will be converted to the type of the `VECTOR` argument.

  * It will cause an error if the JSON string has a different number of elements than the `VECTOR` argument.
  * It will cause an error if the length of the `BLOB` is such that the `BLOB` cannot be converted to the type of the `VECTOR`. Note that there is no type checking in this conversion, so ensure that the `BLOB`s were encoded with the same type as the `VECTOR` argument.
* If both arguments are `BLOB`s, both arguments will be treated as vectors with 32-bit floating-point numbers. It will cause an error if the arguments are different lengths.

* The default format for vector element storage and processing is a 32-bit floating-point number (`F32`). When the inputs are `BLOB`s, the function assumes the inputs are vectors encoded as 32-bit floating-point numbers.
* When a suffix is used, the function will interpret the inputs as vectors encoded as specified by the suffix. See [Using Suffixes for Other Element Types with BLOBs](https://docs.singlestore.com/#UUID-317e1af0-af41-98cb-f6a1-31cedf998e40.md) for more information.

## Output Format for Examples

Vectors can be output in JSON or binary format. Use JSON format for examples and for output readability. For production, use the default binary for efficiency.

Use the following command to output vectors in JSON format.

```sql
SET vector_type_project_format = JSON;
```

Use the following command to set the output format back to binary.

```sql
SET vector_type_project_format = BINARY;
```

## Using VECTOR\_SUB with the VECTOR Type

The examples below show three different uses of `VECTOR_SUB` with varying types of arguments.

## Example 1 - Vector Type

The example below shows `VECTOR_SUB` when both arguments are stored as `VECTOR`s.

First create a table of vectors of length 4 using the `VECTOR` type and insert data into that table.

```sql
CREATE TABLE vectors (id int, vec VECTOR(4) not null);

INSERT INTO vectors VALUES (1, '[0.45, 0.55, 0.495, 0.5]');
INSERT INTO vectors VALUES (2, '[0.1, 0.8, 0.2, 0.555]');
INSERT INTO vectors VALUES (3, '[-0.5, -0.03, -0.1, 0.86]');
INSERT INTO vectors VALUES (4, '[0.5, 0.3, 0.807, 0.1]');
```

> **📝 Note**: The default element type for the VECTOR type is 32-bit floating point (`F32`). The VECTOR type supports other element types, including integer types, as described in the [Vector Type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md) documentation.

The following SQL subtracts the vector `'[0.1,0.1,0.1,0.1]'` from the vectors in the `vectors` table.

```sql
SET vector_type_project_format = JSON;  /* to make vector output readable */

SET @qv = '[0.1,0.1,0.1,0.1]':>VECTOR(4);

/* run both SET statements before this query */
SELECT id, VECTOR_SUB(vec, @qv)
FROM vectors
ORDER BY id;

```

```output

+------+-----------------------------------------------------+
| id   | VECTOR_SUB(vec, @qv)                               |
+------+-----------------------------------------------------+
|    1 | [0.349999994,0.450000018,0.395000011,0.400000006]   |
|    2 | [0,0.699999988,0.100000001,0.455000013]             |
|    3 | [-0.600000024,-0.129999995,-0.200000003,0.75999999] |
|    4 | [0.400000006,0.200000018,0.706999958,0]             |
+------+-----------------------------------------------------+
```

## Example 2 - Vector Type and BLOB Type

Inputs to `VECTOR_SUB` can also be `BLOB`s. If one input is of type `VECTOR` and the other of type `BLOB`, `VECTOR_SUB` will automatically convert the `BLOB` to the type of the `VECTOR` argument.

The SQL below shows an example where `VECTOR_SUB` is used with a vector that is encoded as a `BLOB` using `JSON_ARRAY_PACK`. `VECTOR_SUB` will automatically convert this `BLOB` to the type of `vec`, the left-hand argument.

```sql
SET vector_type_project_format = JSON;  /* to make vector output readable */

SET @qv = JSON_ARRAY_PACK('[0.1,0.1,0.1,0.1]');

SELECT id, VECTOR_SUB(vec, @qv)
FROM vectors
ORDER BY id;


```

```output

+------+-----------------------------------------------------+
| id   | VECTOR_SUB(vec, @qv)                               |
+------+-----------------------------------------------------+
|    1 | [0.349999994,0.450000018,0.395000011,0.400000006]   |
|    2 | [0,0.699999988,0.100000001,0.455000013]             |
|    3 | [-0.600000024,-0.129999995,-0.200000003,0.75999999] |
|    4 | [0.400000006,0.200000018,0.706999958,0]             |
+------+-----------------------------------------------------+
```

> **❗ Important**: It is important to ensure that the vector encoded as a `BLOB` has the same element type and length as the vector(s) stored as `VECTOR`s. In this case, the default 32-bit floating point element type was used for both the `BLOB` encoding and the `VECTOR` type.

The results from Example 1 and Example 2 are the same. The difference between the queries is whether the query vector is a `VECTOR` or a `BLOB`.

## Example 3 - JSON String Argument and I16 Element Type

This example shows how to use `VECTOR_SUB` with a `VECTOR` with an elementType of 16-bit integer (I16) and how to use a JSON string as input to `VECTOR_SUB`.

First create a table with a `VECTOR` attribute of length 3 and element type `I16` and insert data into that table.

```sql
CREATE TABLE vectors_i16(id INT, vec VECTOR(3, I16));

INSERT INTO vectors_i16 VALUES(1, '[1, 2, 3]');
INSERT INTO vectors_i16 VALUES(2, '[4, 5, 6]');
INSERT INTO vectors_i16 VALUES(3, '[1, 4, 8]');
```

The following SQL subtracts the vector `'[1,2,3]'` from each of the vectors in the `vectors_i16` table.

In this example, the JSON string `'[1,2,3]'` is used as input to the `VECTOR_SUB` function. `VECTOR_SUB` will detect that the second argument, `vec`, is of type `VECTOR` of length 3 and with element type 16-bit integer and will automatically convert the JSON string to a `VECTOR` of that type.

```sql
SET vector_type_project_format = JSON;  /* to make vector output readable */

SELECT id, vec, VECTOR_SUB(vec, '[1,2,3]')
FROM vectors_i16
ORDER BY id;


```

```output

+------+---------+----------------------------+
| id   | vec     | VECTOR_SUB(vec, '[1,2,3]') |
+------+---------+----------------------------+
|    1 | [1,2,3] | [0,0,0]                    |
|    2 | [4,5,6] | [3,3,3]                    |
|    3 | [1,4,8] | [0,2,5]                    |
+------+---------+----------------------------+
```

## Using VECTOR\_SUB with Vectors as BLOBs

The following examples and descriptions show the use of `VECTOR_ADD` with arguments that are both vectors stored as `BLOB`s.

## Example 1 - BLOB Arguments

Create a table with a column of type `BLOB` to store the vectors. The second column in this table, with column name `vec` and type `BLOB`, will store the vectors. This example demonstrates storing vector data using `BLOB`s, hence the column of type `BLOB` named `vec`.

Then use the `JSON_ARRAY_PACK` built-in function to easily insert properly formatted vectors.

```sql
CREATE TABLE vectors_b (id int, vec BLOB not null);

INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]'));
INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));
```

The following query subtracts the vector `'[0.1,0.1,0.1,0.1]'` from the vectors in the `vectors_b` table and uses `JSON_ARRAY_UNPACK` to see the results in JSON format.

```sql

SET @qv = JSON_ARRAY_PACK('[0.1,0.1,0.1,0.1]');

SELECT id, JSON_ARRAY_UNPACK(VECTOR_SUB(vec, @qv)) AS vector_sub
FROM vectors_b
ORDER BY id;


```

```output

+------+---------------------------------------------------+
| id   | vector_sub                                        |
+------+---------------------------------------------------+
|    1 | [0,0.699999988,0.100000001,0.455000013]           |
|    2 | [0.349999994,0.450000018,0.395000011,0.400000006] |
+------+---------------------------------------------------+
```

`JSON_ARRAY_UNPACK` was used here to output the vectors in readable format because `VECTOR_SUB` returns a `BLOB` when its inputs are `BLOB`s.

The following query subtracts the vector `'[0.1,0.1,0.1,0.1]'` from the vectors in the `vectors_b` table and uses `HEX` to view the results in hexadecimal format.

```sql
SET @qv = JSON_ARRAY_PACK('[0.1,0.1,0.1,0.1]');

SELECT id, HEX(VECTOR_SUB(vec, @qv)) as vector_sub
FROM vectors_b
ORDER BY id;


```

```output

+------+----------------------------------+
| id   | vector_sub                       |
+------+----------------------------------+
|    1 | 000000003333333FCDCCCC3DC3F5E83E |
|    2 | 3333B33E6766E63E713DCA3ECDCCCC3E |
+------+----------------------------------+
```

## Using Suffixes for Other Element Types with BLOBs

The default element type for vector storage and processing is 32-bit floating point (`F32`). However, other element types are supported.

You can specify the datatype of the vector elements to be used in the operation by adding a suffix to the function. All operations are done using the specified datatype. Omitting the suffix from the function is equivalent to suffixing it with `_F32`.

When using a suffix, the return type will be the type specified by the suffix.

> **📝 Note**: Vector functions with suffixes do not work with the `VECTOR` type. However, vector functions without suffixes are overloaded to work on `VECTOR` values of any element type.

The following table lists the suffixes and their data type.

| Suffix | Data Type                                           |
| ------ | --------------------------------------------------- |
| `_I8`  | 8-bit signed integer                                |
| `_I16` | 16-bit signed integer                               |
| `_I32` | 32-bit signed integer                               |
| `_I64` | 64-bit signed integer                               |
| `_F32` | 32-bit floating-point number (IEEE standard format) |
| `_F64` | 64-bit floating-point number (IEEE standard format) |

## Example 2 - BLOBs with 16-bit Integers

Below is an example of using `JSON_ARRAY_PACK` and `VECTOR_SUB` with 16-bit signed integers.

First create a table of vectors stored as 16-bit integers. Note the use of the `_I16` suffix on `JSON_ARRAY_PACK`.

```sql
CREATE TABLE vectors_b_i (id int, vec BLOB not null);

INSERT INTO vectors_b_i VALUES (1, JSON_ARRAY_PACK_I16('[1, 3, 2, 5]'));
INSERT INTO vectors_b_i VALUES(2, JSON_ARRAY_PACK_I16('[23, 4, 1, 8]'));
```

The following query subtracts the vector `'[2,2,2,2]'` from the vectors in the `vectors_b_i`.

```sql
SET @qv = JSON_ARRAY_PACK_I16('[2, 2, 2, 2]');

SELECT JSON_ARRAY_UNPACK_I16(VECTOR_SUB_I16(vec, @qv)) AS vector_sub
FROM vectors_b_i;


```

```output

+-------------+
| vector_sub  |
+-------------+
| [21,2,-1,6] |
| [-1,1,0,3]  |
+-------------+

```

The result is an array of 16-bit integers as indicated by the `_I16` suffix.

> **📝 Note**: Be sure that the suffixes you use to pack the vector data match the suffixes you use to unpack the data and the suffixes you use on functions to process that data.

## Formatting Binary Vector Data for BLOBs

When using the `BLOB` type for vector operations, vector data can be formatted using `JSON_ARRAY_PACK`. If your vector data is already in a packed binary format, you can load that data into the `BLOB`s. The data must be encoded as a `BLOB` containing packed numbers in little-endian byte order. Vectors stored as `BLOB`s can be of any length; however, the input blob length must be divisible by the size of the packed vector elements .

## Related Topics

* [Vector Type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md)
* [JSON\_ARRAY\_PACK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/json-array-pack.md)
* [JSON\_ARRAY\_UNPACK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/json-array-unpack.md)
* [Binary String Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/binary-string-types.md)

***

Modified at: July 30, 2024

Source: [/db/v9.1/reference/sql-reference/vector-functions/vector-sub/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-sub/)

(An index of the documentation is available at /llms.txt)