SQL Reference Guide

this page provides an overview of how sql fits into unaric segment instead of having a single sql utility, the entire unaric segment interface is designed as a visual translation of sql unaric segment breaks down the components of a standard sql query and maps them to specific, drag and drop features across different steps of the selection wizard if you do want to write raw sql logic, you can use the apply any function (sql) custom value docid\ rjbxawtt2peobuskbgzdm this custom value provides a free form board to write or paste any complex sql operators supported in sfmc's select clause overview in unaric segment, the selection building process is divided into three main wizard steps for users who already know sql, their sql mental model maps directly to these steps selection criteria (input) this is where users configure their from , join , and where sql logic using drag and drop from (data sources) dragging data extensions or data views into the selected data sources area represents the database tables the query is selecting from join (relationships) dragging one data extension on top of another to establish "matching columns" maps directly to sql joins the platform's matching types represent standard sql joins (e g , with matching is an inner join, without matching is a left outer join exclusion, and with all is a full outer join) where (filters) dragging fields to the filters section acts as the sql where clause this includes translating subqueries using the in results / not in results filters target definition (output) this is where users map their select , distinct , case when , and limit/order by logic, and is also the only step where they can write actual sql syntax select (field mapping) choosing which fields will populate the target data extension determines the select statement distinct (prio deduplication) instead of a "dumb" distinct deduplication, users can set up advanced priority rules to clean duplicates based on unique fields case when / then (dynamic custom values) this feature allows users to build conditional logic that outputs different values based on specific field criteria limit and order by (sort & limit) this tool allows users to sort results randomly or by a specific field, and limit the rows by number or percentage writing actual sql syntax (apply any function) inside the custom values panel, users can create an apply any function (sql) custom value this provides a freeform board where sql savvy users can write or copy paste any sql operators and functions (like concat, dateadd, or getdate) applied in the sql select clause to transform fields preview & run view sql query once the selection is configured, sql users can click view sql query during the preview stage to see the exact, beautifully formatted sql query that unaric segment compiled in the background before they execute the final run previewing is a safe validation step when you run a preview, unaric segment writes the results solely to an empty, temporary data extension so your real target data extension remains unaffected running a selection executes the final query and permanently writes and populates the matching records into your real target data extension once you execute the run, the temporary data extension used for the preview is automatically deleted detailed comparison mapping selection criteria (inputs, joins & filters) this wizard step represents your core query components where you define what tables you are pulling from, how they relate, and how you want to filter your audience sql concept / statement unaric segment equivalent functionality & how it works from selected data sources drag and drop data extensions, system data views, or predefined data sets into the workspace inner join a with matching b returns only the records from (a) and (b) with a matching value in the matching field left join a with/without matching b returns all records from (a) with or without matching data from records in (b) right join b with/without matching a returns all records from (b) with or without matching data from records in (a) left outer join (exclusion) a without matching b returns the records in (a) that do not have a matching record in (b) (perfect for exclusion and suppression lists) right outer join b without matching a returns only the records in (b) that do not have a matching record in (a) full outer join a with all b returns all records from both (a) and (b), regardless of whether a matching record exists on the opposite side cross join (cartesian product) each from a with each from b generates a paired combination of every single row from (a) with every single row from (b) without requiring matching fields where filters section drag and drop fields to apply your filtering rules group filters using logical and and or statements by clicking the + icon having fx (formula) button or predefined relation filters used to apply filter conditions directly onto aggregated subquery data (such as count, average, sum, minimum, maximum) is null / is not null is empty / is not empty evaluates whether a field is completely blank or contains a valid value like '%value%' begins with, ends with, contains, does not contain evaluates text fields using partial string matches wildcards % and are supported manually or automatically appended by the filter exists / in (select ) in results / not in results evaluates if a value exists or does not exist in another data extension without executing a full join (preventing duplicate rows) operators (=, <>, <, >, <=, >=) equal to, not equal to, smaller than, greater than, smaller than or equal to, greater than or equal to standard evaluation operators applied to text, dates, numbers, or decimals target definition (output & structure) this step governs how your resulting data is structured and written into your output container (the target data extension) sql concept / statement unaric segment equivalent functionality & how it works select field mapping / automap choose which source fields populate your target data extension automap matches them automatically based on names and compatible field types union / union all append data extensions (tabs) click + add new source to create independent query tabs the records from all tabs are merged together into a single final target data extension distinct prio deduplication applies smart priority rules to handle and remove duplicate records based on a unique field rather than a "dumb" database distinct, you choose which specific duplicate record to keep limit and order by sort & limit sorts records (randomly or by a specific field) and limits the output to an exact record count or percentage insert / update data actions (overwrite, append, update) determines how results are saved overwrite replaces existing records, append adds them to the end, and update modifies matching records using a primary key custom values (specialized sql functions & calculations) custom values allow you to enrich your audience with calculated fields that do not exist in your source data extensions sql function custom value equivalent functionality & how it works case when / then / else dynamic custom value evaluates a set of conditions and outputs different values accordingly you can also use it to retain existing field values on non matching rows group by aggregation custom values (grouping) grouping is handled automatically when using aggregation custom values the system groups your calculated metrics (sum, avg, count, min, max) by your selected relationship key getdate() / getutcdate() timestamp custom value generates the current database system date and time when the selection runs, with the optional ability to convert the timezone datediff() date difference custom value calculates the difference between two date fields (or a date field and a timestamp) in days, weeks, months, or years dateadd() transform date custom value transforms an existing date field by adding or subtracting time units (+/ ) and/or converting it to a different timezone row number() row number custom value generates sequential, subsequent row numbers (ascending or descending) based on a specified field (often used to establish a link between unrelated tables) sum(), avg(), count(), min(), max() aggregation custom value calculates aggregated metrics over multiple records works in basic mode if predefined relations exist, or advanced mode where you define custom link keys and filters freeform sql / nested functions apply any function (sql) a syntax checked board allowing you to write or copy paste any sql operators and functions supported in sfmc's select clause sql cheat sheet this enables users who know sql to quickly connect with how different functions are represented in unaric segment overview sql function deselect segment function select available fields to be selected as target data extension fields from selected data extensions section where filters section union append data extension left join with/without matching inner join with matching right join with/without matching left join exclusion without matching right join exclusion without matching full outer join with all is null/is not null is empty/is not empty like begins with, ends with, contains, does not contain having fx function, predefined relations exist/in in results/not in results "=" , "<>", "<", ">", "<=", ">=" equal to, not equal to, smaller than, greater than, smaller than or equal to, greater than or equal to distinct prio deduplication limit and order by sort and limit case when/then dynamic custom value sum(), avg(), max(), min(), count() group by aggregations custom value datediff date difference custom value dateadd transform date custom value row number() row number custom value getdate() timestamp custom value other sql functions apply any function custom value