Filter Data using DRUID Custom Query

The SELECT statement is used to select data from a database table.

SELECT field_1, field_2, field_3...
FROM entity

SELECT FirstName, LastName, Age
FROM Account

A column alias provides a temporary name for a field in your result set, making it easier to read. For example, when concatenating fields together, you might alias the result.

column_name AS alias_name

Where:

• column_name is the original name of the entity field that you wish to alias.
• AS is optional. Whether you specify the AS keyword or not has no impact on the alias.
• alias_name is the temporary name to assign to the entity field.

SELECT ClientId, FirstName + LastName AS Name
FROM Clients
WHERE FirstName = 'John'

In this example, we've aliased the second field (i.e.: FirstName and LastName concatenated) as Name. As a result, Name will display as the heading for the second field / column when the result set is returned.

SELECT ClientId, FirstName + LastName AS "Client Name"
FROM Clients
WHERE FirstName = 'John'

In this example, we've aliased the second column (i.e.: FirstName and LastName concatenated) as "Client Name". Since there are spaces in this alias_name, we enclosed "Client Name" in quotes.

Creates a temporary name for entities / tables. Table aliases are used to shorten your SQL to make it easier to read or when you are performing a self join (i.e.: listing the same entity / table more than once in the FROM clause).

table_name AS alias_name

Where:

• table_name is the original name of the entity that you wish to alias.
• AS is optional. Whether you specify the AS keyword or not has no impact on the alias.
• alias_name is the temporary name to assign to the entity.

In the examples below, we omit AS when aliasing a table name.

When creating table aliases, it is not necessary to create aliases for all of the entities listed in the FROM clause. You can choose to create aliases on any or all of the entities.

SELECT p.ProductName, Inventory.Quantity
FROM Products p
INNER JOIN Inventory
ON p.ProductId = Inventory.ProductId
ORDER BY p.ProductName ASC, Inventory.Quantity DESC

The query retrieves the product names and their corresponding quantities from [[Products]] and [[Inventory]] entities. It matches these entities based on the ProductId field. The results are sorted first by ProductName in ascending order and then by Quantity in descending order within each product name.

In this example, we've created an alias for the Products entity called p.Within this SQL statement, we referred to the Products entity as p.

You can use table aliases in conjunction with the GROUP BY clause to aggregate data effectively.

Select t1.Id Id, t1.Name Name,  
count(*) TestCasesCount, t1.CreatedOn
, t3.Id [Bot.Id]
, t3.Name [Bot.Name]
FROM KBTestPlan t1 
LEFT JOIN KBTestPlanXTestCase t2
on t1.Id = t2.TestPlanId
LEFT JOIN KBCollection t3
on t1.BotId = t3.Id
GROUP BY t1.Id, t1.Name, t1.CreatedOn, t3.Id, t3.Name
Order By t1.CreatedOn Desc.

In this example, the query selects data from the KBTestPlan, KBTestPlanXTestCase, and KBCollection tables, using aliases (t1, t2, t3) to avoid repeating the full table names.

The GROUP BY clause groups aggregated result by specific columns — t1.Id, t1.Name, t1.CreatedOn, Bot.Id, and Bot.Name — while counting the test cases associated with each test plan.

The WHERE clause is used to filter records in a query, ensuring that only those meeting specific conditions are retrieved.

SELECT field_1, field_2, field_3...
FROM entity
WHERE condition

The following query retrieves test case details from the [[KBTestCase]] entity, filtering records based on the bot ID. It converts the @BotId variable into a unique identifier type before applying the filter. The results are sorted in descending order by the CreatedOn field.

select t1.Id Id, t1.Name Name, t1.ExpectedDataSource1, t1.ExpectedURL1, t1.ExpectedFlowName, t1.ExpectedBot1Name, t1.CreatedOn
from KBTestCase t1
where T1.BotId = CONVERT(uniqueidentifier, '@BotId') -- Filter for the current bot
order by T1.CreatedOn DESC;

When using this query, we set the BotId variable to [[ChatUser]].BotId in the mapping table.

The following operators can be used in the WHERE clause:

SELECT field_1, field_2, ...
FROM entity
WHERE field_n LIKE pattern

SELECT 
    s.BookName, 
    s.Price, 
    s.QuantitySold, 
    (s.Price * s.QuantitySold) AS TotalSales
FROM Sales s
WHERE s.BookName LIKE 'A%'

SELECT FirstName, LastName
FROM Account
WHERE Age > 30 AND City = 'New York'

SELECT field_1, field_1, ...
FROM entity
WHERE NOT condition;

SELECT s.FirstName, s.LastName, s.Country
FROM Customers s
WHERE NOT Country = 'Romania'

SELECT s.FirstName, s.LastName, s.Country
FROM Customers s
WHERE Country NOT IN ('Romania', 'Germany', 'Spain')

SELECT 
    field_1, 
    field_2, 
    field_N
FROM 
    entity
WHERE 
    field_N IS NULL

SELECT 
    s.CustomerName, 
    s.ContactName, 
    s.Address
FROM 
    Customers s
WHERE 
    s.Address IS NULL

SELECT 
    field_1, 
    field_2, 
    field_N
FROM 
    entity
WHERE 
    field_N IS NOT NULL

SELECT 
    s.CustomerName, 
    s.ContactName, 
    s.Address
FROM 
    Customers s
WHERE 
    s.Address IS NOT NULL

Sorts the result set of a query by one or more fields / aliases in ascending or descending order.

SELECT field_1, field_2, ...
FROM entity
ORDER BY field_1, field_2, ... ASC|DESC

Example:

This query will return a list of products, showing the Name, ID, and Price for each product in the Products entity, sorted alphabetically by the ProductName. If there are multiple products with the same name, they will appear in the same order they were stored in the database.

SELECT s.ProductName Name, s.ProductID ID, s.Price Price
FROM Products s
ORDER BY s.ProductName

To sort the records in descending order, use the DESC keyword.

SELECT s.ProductName Name, s.ProductID ID, s.Price Price
FROM Products s
ORDER BY s.ProductName DESC

When multiple fields are specified, the query first sorts the records by the first field. If there are ties (i.e., records with the same value in the first filed), it then sorts those records based on the second field, and so on.

The following query selects all customers from the Customers entity, sorted by the Country and the CustomerName fields. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName.

SELECT 
    s.CustomerID, 
    s.CustomerName, 
    s.ContactName, 
    s.Country, 
    s.City, 
    s.Phone
FROM Customers s
ORDER BY s.Country, s.CustomerName

Groups records that have the same values in specified fields. It is often used in conjunction with aggregate functions to perform calculations on each group.

SELECT field(s)
FROM entity
WHERE condition
GROUP BY field(s)
ORDER BY field(s)

The following query gives the count of customers grouped by both Country and City.

SELECT Country, City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY Country, City

When joining data from two tables (entities), you can group results using either aliased or original column names. Grouping by original column names, introduced in druid 8.10, aligns with standard SQL conventions.

The following query groups the results using the original column names (b.CreatedOn, s.Name, s.Color).

SELECT    COUNT(*) Counter, 
   TRUNC(month, b.CreatedOn) CreatedOn, 
   s.Name Status.Name, s.Color Status.Color
FROM Book b 
LEFT JOIN BookStatus s ON b.StatusId = s.Id 
GROUP BY b.CreatedOn, s.Name, s.Color

The following query groups the results using the aliased column names (CreatedOn, Status.Name, Status.Color).

SELECT    COUNT(*) Counter, 
   TRUNC(month, b.CreatedOn) CreatedOn, 
   s.Name Status.Name, s.Color Status.Color
FROM Book b 
LEFT JOIN BookStatus s ON b.StatusId = s.Id 
GROUP BY CreatedOn, Status.Name, Status.Color

Groups records after they have been aggregated, unlike the WHERE clause which filters individual records before aggregation. It's specifically designed to work with aggregate functions (like COUNT, SUM, AVG, MIN, and MAX) and allows you to filter groups based on the results of those aggregations.

SELECT field(s)
FROM entity
HAVING condition

The following query will return a list of departments from the Sales entity where the total sales for each department exceed 100,000. It will display the department name and the corresponding total sales for departments that meet this criterion.

SELECT DepartmentName AS Name, SUM(Amount) AS "Total Sales"
FROM Sales
GROUP BY DepartmentName
HAVING SUM(Amount) > 100000

Returns the smallest value of the selected field.

SELECT MIN(field)
FROM entity

SELECT MIN(Price) AS SmallestPrice, CategoryID 
FROM Products
GROUP BY CategoryID

Returns the highest value of the selected field.

SELECT MAX(field)
FROM entity

SELECT s.OrderID ID, 
       s.OrderName Name, 
       MAX(s.Amount) AS MaxAmount, 
       s.OrderDate
FROM Orders s
WHERE s.OrderDate >= '2024-01-01'
GROUP BY s.OrderID, s.OrderName, s.OrderDate

Returns the average value in a set.

SELECT AVG(field)
FROM entity

The following query returns only the customers whose average order amount exceeds $5000 for orders placed on or after January 1, 2024.

SELECT s.CustomerID, 
       AVG(s.Amount) AS AverageOrderAmount
FROM Orders s
WHERE s.OrderDate >= '2024-01-01'
GROUP BY s.CustomerID
HAVING AVG(s.Amount) > 5000

Returns the number of records in a dataset.

SELECT COUNT(*)
FROM entity

The following query retrieves the number of products in each category from the Products entity.

SELECT COUNT(*) AS 'Products Number', CategoryName Name
FROM Products
GROUP BY CategoryName

Counts the number of unique (non-duplicate) values in a specified field. This is useful when you want to determine how many distinct entries exist in a dataset for a particular field.

SELECT COUNT(DISTINCT field)  
FROM entity

For example, it can be used to count the number of different prices, product categories, or any other field where duplicates may exist but are not needed in the count.

The following query returns how many distinct customers have purchased distinct products during the year 2024.

SELECT COUNT(DISTINCT CustomerID) AS 'Unique Customers', 
       COUNT(DISTINCT ProductID) AS 'Unique Products'
FROM Sales
WHERE SaleDate >= '2024-01-01' AND SaleDate <= '2024-12-31'

Selects records that have matching values in both the specified parent entity and the child entity.

SELECT entity_field(s)
FROM parent_entity
INNER JOIN child_entity
ON parent_entity.entity_field = child_entity.entity_field;

SELECT Suppliers.SupplierId, Suppliers.SupplierName, Orders.OrderDate
FROM Suppliers
INNER JOIN Orders
ON Suppliers.SupplierId = Orders.SupplierId;

The query returns all records from the Suppliers and Orders entities where there is a matching SupplierId value in both the Suppliers and Orders entities. If one of the records is missing the SupplierId in one of the two entities, the record will be omitted from the result.

Returns all records from the parent, and the matching records from the child entity. The result is 0 records from the parent entity, if there is no match.

SELECT field_name
FROM parent_entity
LEFT JOIN child_entity
ON parent_entity1.field_name = child_entity.field_name;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName

The query retrieves customer names and their corresponding order IDs. It returns all customers, including those who do not have any orders. Customers without orders will have a NULL value in the OrderID column. The results are sorted alphabetically by customer name.

Druid does not support RIGHT JOIN. If you wanted to use a RIGHT JOIN (e.g., fetching all records from table B and the matching records from table A), you can rewrite it with a LEFT JOIN by swapping the tables.

Let’s say you want to join the Orders table and the Customers table, where you want to make sure all the Orders appear in your results, even if they don’t have matching customers.

SELECT Orders.OrderID, Customers.CustomerName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.OrderID

Where:

• LEFT JOIN ensures that all records from the Customers table are returned.
• By swapping the order of the tables, Customers becomes the left table and Orders becomes the right table. This ensures that you get all rows from Orders, just like a RIGHT JOIN would do in other SQL systems.

Even though Druid doesn’t support RIGHT JOIN, this approach using LEFT JOIN with the tables reversed gives you the same result.

Combines the results of two or more SELECT queries into a single result set. By default, UNION eliminates duplicate rows, ensuring that the result contains only unique records.

Example:

• Let's assume you have two entities: [[Sales]] and [[Revenue], both containing CustomerId and PurchaseAmount fields.
• You want to combine the data from the [[Sales]] and [[Revenue]] entities, but only include distinct records (unique combinations of CustomerId and PurchaseAmount).

SELECT s.CustomerId ID, s.PurchaseAmount Amount
FROM Sales s
UNION
SELECT t.CustomerId ID, t.PurchaseAmount Amount
FROM Revenue t

Combines the results of two or more SELECT queries into a single result set, including duplicates. Unlike UNION, which removes duplicates, UNION ALL retains all records from the combined queries, even if they are identical.

Example:

• Let's assume you have two entities: [[Sales]] and [[Revenue]], both containing CustomerId and PurchaseAmount fields.
• You want to combine the data from the [[Sales]] and [[Revenue]] entities, and include all records, even duplicates.

SELECT s.CustomerId ID, s.PurchaseAmount Amount
FROM Sales s
UNION
SELECT t.CustomerId ID, t.PurchaseAmount Amount
FROM Revenue t

The result will include all records, even if there are duplicate records between the [[Sales]] and [[Revenue]] entities.

Returns only the rows that are common between two SELECT queries. In other words, it returns the intersection of the result sets — only the records that exist in both queries.

INTERSECT removes duplicates by default, so if a record appears multiple times in both queries, it will only appear once in the result.

Example:

• Let’s say you have two Druid entities: [[Customers]] and [[Subscribers]], each with a CustomerId and Email field.
• You want to find the customers who are both customers and have subscribed to your newsletter.

SELECT CustomerId, Email
FROM Customers
INTERSECT
SELECT CustomerId, Email
FROM Subscribers

The result contains only the records that are common in both entities [[Customers]] and [[Subscribers]].

Returns the records from the first SELECT query that do not exist in the second SELECT query. Essentially, it retrieves the difference between the two result sets, excluding any duplicate rows by default.

Example:

• Let’s say you have two Druid entities: [[Employees]] and [[Contractors]], each with EmployeeId and Name fields.
• You want to find all the employees who are not contractors.

SELECT EmployeeId, Name
FROM Employees
EXCEPT
SELECT EmployeeId, Name
FROM Contractors;

Druid supports basic arithmetic operators for mathematical calculations:

• +: Addition
• -: Subtraction
• *: Multiplication
• /: Division

This example multiplies Price by Quantity to calculate the total cost of an order:

SELECT Price * Quantity AS TotalCost
FROM Orders

Literals are fixed values used directly in statements. They can be of various data types, including strings, numbers and date and time.

This expression adds 10 to the Price field for each record, creating a new calculated field NewPrice.

SELECT Price + 10 AS NewPrice
FROM Products

Returns the leftmost part of a character string based on the specified number of characters.

LEFT(character_expression, integer_expression)

Where:

• character_expression is an entity field.
• integer_expression is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned.

SELECT LEFT(CustomerName, 3) AS ShortName
FROM Customers

Returns the number of characters in a specified string, excluding trailing spaces.

LEN(field)

This function is useful for validating input lengths, formatting data, or applying text-based conditions in queries.

SELECT CustomerName, LEN(CustomerName) AS NameLength  
FROM Customers  
WHERE LEN(CustomerName) > 5  
ORDER BY NameLength DESC

Removes leading spaces (or specified characters) from the beginning of a string. This function is useful for cleaning up user input, standardizing text, and improving data quality in queries.

LTRIM(field [, characters])

SELECT CustomerName, LTRIM(CustomerName) AS TrimmedName
FROM Customers

converts all uppercase letters in a string to lowercase. This function is useful for case-insensitive comparisons, normalizing text, and formatting data consistently.

LOWER(field)

SELECT CustomerName, LOWER(CustomerName) AS LowercaseName
FROM Customers

Replaces all occurrences of a specified substring within a string with another substring. This function is useful for standardizing text, cleaning data, and making bulk text modifications in queries.

REPLACE(field, string_pattern, string_replacement)

Where:

• field – The original field of type string where replacements occur.
• string_pattern – The substring to be found and replaced.
• string_replacement – The new substring that replaces string_pattern.

SELECT CompanyName, REPLACE(CompanyName, 'Inc.', 'LLC') AS UpdatedCompanyName
FROM Companies

Returns a specified number of characters from the end (right side) of a string. This function is useful for extracting suffixes, checking data patterns, and formatting outputs.

RIGHT(field, integer_expression)

SELECT CustomerName, CustomerPhone, RIGHT(CustomerPhone, 4) AS LastDigits
FROM Customers

Removes trailing spaces (or specified characters) from the end of a string. This function is useful for cleaning up user input, normalizing text, and ensuring consistent data formatting.

RTRIM(field [, characters])

Where:

• field – A field of type string value to be trimmed.
• characters (Optional) – A set of characters to remove from the end of the string. If not specified, only space characters (CHAR(32)) are removed.

SELECT CustomerName, RTRIM(CustomerName) AS TrimmedName
FROM Customers

Extracts a portion of a string based on the given starting point and length of the desired substring.

SUBSTRING(field, start, length)

Where:

• field – A field from which the substring will be extracted.
• start – An integer that specifies the starting position for the substring. The position starts at 1, meaning the first character is at position 1. If start is less than 1, the substring will begin at the first character.
• length – A positive integer that specifies how many characters to extract from the starting position. If the sum of start and length exceeds the available characters, the entire string from start will be returned. A negative length will generate an error.

SELECT CustomerName, SUBSTRING(CustomerName, 1, 5) AS First5Chars
FROM Customers

Converts all lowercase letters in a string to uppercase. This function is useful for standardizing text, ensuring consistent capitalization, and comparing strings in a case-insensitive manner.

UPPER(field)

SELECT CustomerName, UPPER(CustomerName) AS Name
FROM Customers

Handles missing data (NULL values) in a dataset by providing an alternative (default) value. This can be useful when displaying data in reports or queries where missing information should be replaced with a more meaningful or user-friendly substitute.

SELECT COALESCE(field(s), 'default value')
FROM entity

The following query ensures that for each client, the client name is displayed, and if a phone number or address is missing, default values are substituted.

SELECT s.ClientName AS Name,
       COALESCE(s.ClientPhone, 'No phone number') AS Phone,
       COALESCE(s.ClientAddress, 'No address provided') AS Address
FROM Account s

SELECT s.ClientName AS Name,
       COALESCE(s.ClientPhone, s.ClientAddress 'No data') AS ContactInfo
FROM Account s

Converts the value of a field in a dataset to a specified data type. This function allows you to transform data into types like VARCHAR, INTEGER, or DECIMAL. It is useful when you need to ensure that the data is in the correct format for querying, calculations, or comparisons.

CONVERT(target_data_type, field)

SELECT s.ProductName Name, CONVERT(decimal, s.ProductPrice) AS Price
FROM Products s

Converts int, decimal, double, datetime and varchar to the specified date format.

Use the following syntax for CONVERT with three parameters:

convert(data_type, data_to_convert, FormatId)

SELECT CONVERT(VARCHAR, a.CreatedOn) AS CreatedOnFormatted
FROM Account a;

Skips a specified number of records before returning results. Used in combination with FETCH NEXT, limits the number of records returned after the offset.

SELECT e.Id, e.Name, e.Description, ef.EntityMetadataId, ef.LastModificationTime 
FROM EntityMetadata e 
JOIN EntityFieldMetadata ef ON ef.EntityMetadataId = e.Id 
ORDER BY ef.LastModificationTime DESC 
OFFSET 90 ROWS FETCH NEXT 15 ROWS ONLY

In this example:

• The query retrieves a specific subset of rows from a joined result of two tables, EntityMetadata (e) and EntityFieldMetadata (ef).
• The results are ordered by the LastModificationTime column from the EntityFieldMetadata table (ef) in descending order. This means that the most recently modified records will appear first in the result set.
• The OFFSET statement skips the first 90 rows of the ordered result set. It means that the retrieval starts from the 91st row onward.
• After skipping the first 90 rows, the FETCH statement limits the number of rows returned to the next 15 rows only, effectively giving rows 91 through 105 in the ordered list.

Limits the result set to a specified number of rows. However, without proper ordering, the results can appear in an arbitrary order. To ensure a predictable and meaningful result, it is always recommended to use the TOP clause in combination with ORDER BY.

SELECT TOP <number_of_rows> field
FROM entity
ORDER BY field

SELECT TOP 5 FirstName, LastName
FROM Contact
ORDER BY FirstName