---
title: "Writing SQL Queries"
---

# Writing SQL Queries

> :::info
> For information about databases and how to get connected, see the [Database Connections](platform\database-connections\database-connections.md) section.
> :::

## SQL Tables

The foundation of every database system is a table. Every database consists of one or more tables, which store the database’s data/information. Each table is identified by a name (for example `Customers` or `Orders`), and consists of column definitions and rows of data.

The database table columns have their own unique names and have pre-defined data types. Table columns can have various attributes defining the column functionality (such as the primary key, index, default value, and so on).

While table columns describe the data types, the table rows contain the actual data for the columns.

![" "](1-sql-table.png)

Current common databases are almost all Relational Databases. This means that their tables can relate to each other by including an ID in one table that matches the key of another. These are called foreign keys.

### Primary Key
A primary key is a way to uniquely identify each row in a table. While it is possible to create a database table without a primary key, it is highly recommended to configure one for each table. A primary key is comprised of either a single column, or set of columns. When multiple columns are specified as a primary key, this is known as a **composite primary key**. No two distinct rows in a table can have the same value (or combination of values) in those columns.

While Primary Keys can be configured in several ways, they *typically* meet the following criteria:
- **Integer Data Type**: The data type of the key column is typically an integer, and not a varchar. The primary key is only an identifier to a specific row in a table, so an integer data type can easily be used. Some databases support a UID or a UUID (Universally Unique IDentifier) that looks like a character string, but is something specially made for primary keys.
- **Automatically Incrementing**: The value of the primary key increments as rows are added. The key is usually configured to automatically increment in the database so that external applications (such as Ignition) don't have to figure out the next available value when inserting a new row. 
- **Statically Defined**: Any row that is inserted must fill in these value(s) without creating duplicates. Configuring the primary key as automatically incrementing means that the database will **automatically** handle this criteria.
- **Non-NULL**: NULL (empty) values should not be present in the primary key. This column (or columns) will usually not allow NULL values.

### Index
Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Indexes are extremely important when querying large sets of data. You should create an index for the set of columns you use commonly in a `WHERE` clause. For example, you should add an index on the timestamp column of a historical table when querying the table by a start and end date. Ignition does this automatically when it creates tables for Tag History or Transaction Groups.

### Foreign Key
A Foreign Key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key in the referenced table. For example, you might have a list of suppliers with an integer ID column. Then your invoices would use the supplier's ID instead of the name. These linked tables can save a lot of space because you don't have to include everything about the supplier in each invoice.

#### Example
Supplier (**SupplierNumber**, Name, Address, Type) 
Invoices (InvoiceNumber, **SupplierNumber**, Text, Cost)

### Null Value

NULL is a special marker used in SQL to indicate that a data value does not exist in the database. This way it is clear that there is no data, instead of guessing if a value of 0 is correct or just missing data. By definition, NULL is not equal to anything, even other NULL values. Every database has a function to identify if a value is NULL, usually called isNULL() or something similar.

### Comments
Comments can be added to any SQL query just like with scripting. Single line comments are done with two dashes and a space: `--` 

```sql title="SQL - Single Line Comment"
-- This is a single line comment in a SQL query.
SELECT * FROM my_table
```

You can also do multi line comments by wrapping text within a forward slash and an asterisk: `/* text */`

```sql title="SQL - Multi Line Comment"
/* A multi line comment
can span multiple lines. The
comment will stop when it is closed
with another asterisk and forward slash.*/
```

## SQL Queries
SQL queries (or statements) are used to create, maintain, and view relational databases like MySQL, SQLServer, Oracle, etc. They follow a specific format and use just key words to determine the structure of the query. Unlike most coding languages, SQL does not rely on newlines or start/end markers for it's format, each query is a single line of code. You will often see SQL queries split over several lines, but that is just to make them easier to read.

:::info
You might notice a lot of CAPITALIZED words in SQL queries. While these key words are not case sensitive, it is still common practice for people to capitalize them in a query. Things like SELECT, FROM, WHERE (and a few others) are almost always capitalized because they have a special meaning. Try not to have table or column names that use any of these special words. You will see this capitalization format in examples throughout this user manual and other online references.
:::

## Select Command
The SELECT statement is used to select data from a database. The result is returned as a data set, called the result set. This is true even if there is only one value returned. The syntax for a SELECT statement is as follows:

```sql title="SQL - The Select Command"
SELECT myColumn FROM myTable
```

### Select Examples

The "*" character can be used to specify all columns from a database table. While this is the easiest way to retrieve results from a table, this is not the recommended approach. 

```sql title="SQL - Selecting All Columns"
SELECT * FROM Customers
```

The recommended approach is to instead specify only the columns that are required for a query. There are several reasons for this, but performance would be the main one: less columns in a statement means less work for the database, and the resulting data set in Ignition will use less memory.

```sql title="SQL - Selecting a Single Column"
SELECT Name FROM Customers
```

```sql title="SQL - Selecting Multiple Columns"
SELECT Name, Address FROM Customers
```

## Where Clause
The WHERE clause is used in conjunction with other commands to extract only those records that fulfill a specified criterion. The WHERE clause usually goes near the end of the query followed by a condition that the values must meet to be returned.

```sql title="Pseudocode - Where Clause"
SELECT myColumn FROM myTable WHERE condition
```

The WHERE clause can use various operators for its condition, with the basic operators being:

| Operator | Description               |
| :--------: | ------------------------- |
| `=`  | Equal to.                 |
| `<>`       | Not equal to.             |
| `>`       | Greater than.             |
| `<`        | Less than.                |
| `>=`      | Greater than or equal to. |
| `<=`       | Less than or equal to.    |

### Where Examples
Only return customers from CA.

```sql title="SQL - Simple Where Clause"
SELECT * FROM Customers WHERE State = 'CA'
```

Only return users over a specified age.

```sql title="SQL - Select Users over 25"
SELECT * FROM Users WHERE Age > 25
```

Select all things from a table where the date column has a value within a certain range. 

```sql title="SQL - Select Column Between Two Dates"
SELECT * FROM events WHERE date_column BETWEEN '2023-01-01' AND '2024-01-01'
```

Additional examples can be found on the [SQL Where Clauses](platform\sql-in-ignition\writing-sql-queries\sql-where-clauses\sql-where-clauses.md) page.

## Insert Into Command
The INSERT INTO statement is used to insert a new row in a table. If any columns have default values or are auto-incrementing, they can be omitted from the INSERT query. 

```sql title="Pseudocode - The Insert Command"
INSERT INTO myTable (column1, column2)
VALUES ('Value1', 'Value2')
```

### Insert Examples

```sql title="SQL - Insert Values into Columns"
INSERT INTO Customers (Name, Address, City, State, Zip, Country, Phone)
VALUES ('Inductive Automation', '90 Blue Ravine', 'Folsom', 'CA', '95630', United States, '1-800-266-7798')
```

If inserting a value into every column of the table, the columns do not need to be listed on the INSERT INTO statement. The values just need to be listed in the same order as the columns in the table. The table in the query below has four columns: id, first name, last name, and title.

```sql title="SQL - Inserting to all Columns"
INSERT INTO Users
VALUES (5628, 'Bob', 'Smith', 'Project Manager')
```

## Update Command
The UPDATE statement is used to update existing records in a table. If a WHERE clause is not used, **all rows in the table will be updated**. As a result, the UPDATE statement should be used in conjunction with a WHERE clause in most cases. Many official management tools like SQLServer's Management studio will not allow UPDATE commands without a WHERE clause.

```sql title="Pseudocode - The Update Command"
UPDATE myTable SET myColumn = 'myValue'
```

### Update Examples

```sql title="SQL - Updating All Rows in a Single Column"
UPDATE Customers SET Name = 'Inductive Automation'
```

```sql title="SQL - Updating a Single Column for a Single Row"
UPDATE Customers SET Address = '2110 21st Street' WHERE ID = 1
```

## Delete Command
The DELETE statement is used to delete records in a table. **NEVER** run a delete command without a WHERE clause. It will delete **ALL** records from that table. Many official management tools like SQLServer's Management studio will not allow DELETE commands without a WHERE clause.

```sql title="Pseudocode - The Delete Command"
DELETE FROM myTable WHERE myColumn = value
```

### Delete Examples

```sql title="SQL - Deleting Based on Column Value"
DELETE FROM Customers WHERE Name = 'Inductive Automation'
```

```sql title="SQL - Deleting Rows Based on an ID Column"
DELETE FROM Customers WHERE id < 538
```