Writing SQL Queries
SQL Tables
For information about databases and how to get connected, see the Database Connections section.
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.
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: --
-- 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 */
/* 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.
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:
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.
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.
SELECT Name FROM Customers
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.
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.
SELECT * FROM Customers WHERE State = 'CA'
Only return users over a specified age.
SELECT * FROM Users WHERE Age > 25
Select all things from a table where the date column has a value within a certain range.
SELECT * FROM events WHERE date_column BETWEEN '2023-01-01' AND '2024-01-01'
Additional examples can be found on the SQL Where Clauses 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.
INSERT INTO myTable (column1, column2)
VALUES ('Value1', 'Value2')
Insert Examples
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.
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.
UPDATE myTable SET myColumn = 'myValue'
Update Examples
UPDATE Customers SET Name = 'Inductive Automation'
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.
DELETE FROM myTable WHERE myColumn = value
Delete Examples
DELETE FROM Customers WHERE Name = 'Inductive Automation'
DELETE FROM Customers WHERE id < 538