SQL or Structured Query Language is a language designed to manage and retrieve information from relational database management systems. One of its main characteristics is the handling of algebra and relational calculus to perform queries in order to retrieve, easily, information from databases, as well as make changes to them.
In the case of using Salesforce Marketing Cloud SQL Query and as we mentioned in a previous article: How to take advantage of Salesforce Marketing Cloud SQL Queries, we will only use SELECT to retrieve information, not update or delete records.
The Marketing Cloud and Contact Builder Data Extensions use a relational database. You also use it in Data Views and your own Data Extensions as long as you establish and define relationships of this type.
In short, you need the best way to access all this information and be able to exploit it with the most interesting segmentations. And not only for shipments, but also to make personalized or mixed reports with your own data.
SQL basics
Meaning of each element...
-
SELECT: With this we indicate that we want to make a selection, there are other types in SQL, but in SQL QUERY of Marketing Cloud only SELECT can be used.
-
DISTINCT: Indicates that we want to select only non-repeating distinct values. That is, if you do a SELECT from an ID, and it in the table has these values, 1,2,2,3,4,4,5, a SELECT DISTINCT will return 1,2,3,4,5
-
ALL: This is used to return all, as it is the default value, I have never seen it used (there will be some purists).
Field name
Now it is the turn of the fields that we want to recover, normally it is the list of fields separated by commas, for example, name, lastname1, email or use an asterisk * to recover all the fields.
If the query is in several tables at the same time (we'll see how to do this), we must specify the name of the table before the name of the field, or if we have used an alias for the table, the alias alternatively.
AS
We use AS to generate aliases, both for a table and a field. We can do something like SELECT CONCAT(p.firstname,' ',p.lastname1) AS theFullName FROM person AS p.
FROM: Indicates the table from which we want to retrieve the data. In the case of retrieving data from several tables, we will see in another article how to use the JOIN clause
WHERE: It is like a filter or condition of the data that is going to be returned. SELECT * FROM people WHERE name='Santiago' will return only the users with whom the name "Santiago" exactly matches.
Conditions of the where clause
All of the conditions we use in "where" will return either TRUE or FALSE, depending on whether the record matches it or not, and will display only TRUEs in the resulting table. All of the conditions we use in "which" will return either TRUE or FALSE, depending on whether the record matches it or not, and will display only TRUEs in the resulting table.
You can use operators such as: >, >=, <, <=, =, for different there are two ¡= or <> and sometimes, depending on the SQL engine, it supports both or only one of them.
Another option that we have to verify is that when the field is NULL, it says IS NULL; otherwise it would say IS NOT NULL. Sometimes there is confusion with this concept, NULL is completely empty, if we introduce a "" in that field, it will no longer be null, or simply if we delete the field without specifying that it become NULL. For this reason, it is often better to put the condition WHERE field1='' OR field1 IS NULL
LIKE: Before we have used the = to search for "Santiago", but if we want to get both "Santiagos" and any other name that begins with "Sa", Sandra, Sara, etc. The condition would be LIKE 'Sa%'. That is, in "like" we can use wildcards, the percentage % accepts any string of characters and if we use an underscore it is a single character. For example, if we use this search %a_a% will search for any word like: squid, cara, Aramaic, sara, etc. That is, any word that has one character "a" and another "a", whether or not it then has more characters before the first "a" or after the last "a".
BETWEEN…. AND: to search for a range of values Price BETWEEN 1 AND 50
IN(..,.., …): so you don't have to do many ORs, you can include IN, WHERE colors IN('red','green','blue')
OR, AND and NOT: You can use logical operators such as OR, NOT, and AND, and use parentheses to prioritize one criterion over another.
GROUP BY: Specifies the grouping that is given to the data. It is always used in combination with aggregate functions.
SELECT color, COUNT(id_shoe) FROM shoes GROUP BY color
It will return the number of shoes per color in our table.
HAVING: It is similar to WHERE but applies to the results returned by the query. It should always be applied together with GROUP BY.
ORDER BY: It is used to order the results, you can order them according to several fields. By default it is done in ascending order ASC and if not specified otherwise it will be so. If you want descending use DESC
Aggregation functions
When we use group, we usually need aggregation functions, there are different ones depending on the SQL of your database, as far as Marketing Cloud is concerned, you can find the following:
-
COUNT: returns the total number of rows selected by the query.
-
MIN: returns the minimum value of the field we specify.
-
MAX: returns the maximum value of the field we specify.
-
SUM: adds the values of the field that we specify. It can only be used in numeric columns.
-
AVG: returns the average value of the field we specify. It can only be used in numeric columns.
AND INNER?
We have not forgotten the cases in which we want to "cross" several tables, it is a topic that is worth a whole article and we will see it in a later article: The power of joining tables with SQL in SalesForce Marketing Cloud.
As a foretaste, given 2 tables, with a related field, for example, imagine that the field NIF exists in both, something like.
SELECT * FROM table1 AS t1 INNER JOIN table2 as t2 ON t1.NIF=t2.NIF will return all the matching records, that means they have the same number in both tables, and with the fields of both tables.
Which is an intersection of sets.
Conclusion
As an introduction to the SQL language, we have seen a large part in this article, especially what is the basic structure of an SQL SELECT statement, later we will see a very interesting part that is what we have advanced in this last part, doing queries on multiple tables at once.
If you liked this article, you might also be interested in:
