SELECT
Mar 03, 2023 • 5 minutes to readRetrieves rows from one or more tables and enables the selection of one or many records (rows) or fields (columns).
Here are some common use cases for the SELECT block:
- Retrieving data from a table to send personalized messages: You can use the SELECT block to retrieve data from a table or view and use it in other action blocks. For example, you can use the SELECT block to retrieve customer information from a table and use it to send personalized emails or SMS messages.
- Filtering data: You can use the SELECT block to filter data based on specific criteria, such as a date range or a specific value in a field. This can be useful for sending targeted messages or updating specific records in a table.
- Aggregating data: You can use the SELECT block to aggregate data from multiple tables or views, such as calculating the total sales for a specific product or region. This can be useful for generating reports or performing complex calculations.
- Combining data: You can use the SELECT block to combine data from multiple tables or views into a single result set. This can be useful for generating reports that combine data from different sources or for creating a new table that combines data from multiple tables.
Options
SELECT can be combined with the following options by clicking the gear icon in the action block:
SELECT DISTINCT
Omits records that contain duplicate data in the selected fields. It can be used to return only unique values.
The above trigger logic selects unique items from the table with orders to generate a lookup table with these unique items.
SELECT TOP
Specifies the number of records to be returned.
In the example above, the system sends an email to the top 10 users who were created first based on their creation date.
If you do not include the ORDER BY clause when using TOP, the query will return an arbitrary set of records.
SELECT FROM
Limits the records returned only to the ones specified in the logic. FROM option allows to specify the table (source) from which the data is taken.
SELECT JOIN
JOIN enriches SELECT to retrieve rows and columns from different tables.
JOIN
JOIN combines fields from one or more tables using a value common to each. The following types are available: INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.
JOIN defines the way two tables are related by specifying:
- which columns from tables should be used for joining,
- a comparison operator upon which join should happen. Learn more.
There are four logical types of JOIN that specify which tables and records should be used for joining: INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.
INNER JOIN
Returns records that have matching values in both tables.
In this scenario, the email is sent to all employees who are assigned to the project.
LEFT JOIN
Returns all records from the left table, and only the matched records from the right table.
The above trigger action sends an email with a report that shows all users and their sales (if available). Left join ensures that all users are included in the report, even if they do not have sales information available.
RIGHT JOIN
Returns all records from the right table, and the matched records from the left table.
FULL JOIN
Returns all records when there is a match in either left or right table.
The above trigger sends an email with a table report that displays all projects and their assigned employees, as well as employees who are not assigned to any project. The report also includes projects that do not have any assigned employees.
SELECT WHERE
Limits records using specific criteria.
WHERE is used with SELECT to filter records by specific conditions. Learn more.
In this example, WHERE filters the records from the Customer table to extract only those with the surname “Smith”.
SELECT GROUP BY
Arranges similar data into groups.
You can combine SELECT with GROUP BY to group all rows that have identical values in a specified column or combination of columns into a single row.
If aggregate functions are used in the SELECT block along with non-aggregated fields, it is required to include all non-aggregated fields in the GROUP BY clause.
In this example, the trigger logic counts the number of orders for each order status. The Status field is a non-aggregated field so it must be included in the GROUP BY clause.
SELECT HAVING
Specifies a search condition of aggregate values. It acts like WHERE but can be applied only to grouped records. HAVING requires the GROUP BY modifier in the clause.
HAVING is specified by the following aggregate functions:
- COUNT – returns the number of rows of each group.
- COUNT DISTINCT – removes the repetitive appearance of the same data to return only the unique values.
- SUM – returns the sum or total of each group.
- MAX – returns the maximum value of each group.
- MIN – returns the minimum value of each group.
- AVG – returns the average and mean of each group.
It can be completed upon one of the comparison operators. Learn more.
In this example, SELECT – GROUP BY – HAVING lists the number of customers in each country to include countries with more than 5 customers.
SELECT ORDER BY
Sorts the results in ascending or descending order by the specified fields.
SELECT ORDER BY can operate upon multiple columns. Sequence of columns do matter. ASC and DESC defines whether a records in each column should be ordered from A-Z or Z-A.
In this example, SELECT – ORDER BY sorts the records from the CRM_Contact table by the first name in ascending order and the country in descending order.
