Hi, in this video we're going to extend our understanding of SQL by talking about how we can use operators to create more complex conditions or data values in our queries. Operators are words or symbols that we use in our code to define some sort of condition among data elements. Most of these are probably familiar to you in concept but here we'll talk about how to use them in our SQL. Specifically we'll talk about three types of operators, comparison operators, arithmetic operators, and logical operators. Here's a summary of the operators we'll cover in each category. If you're just listening, don't worry we'll list each of them as we go forward. The first type of operators we'll talk about are comparison operators. Comparison operators help to find whether a condition between two fields or functions of fields is true or false. Here are the comparison operators available in SQL. This shall look pretty familiar. We have equal to, less than, greater than, less than or equal to, and greater than or equal to. We also have the equivalent operators less than or greater than and not equal to where not is represented by an exclamation point. Similarly, we have not greater than and not less than also using the exclamation point. We touched on this with some of our related videos where we talked about where and having statement. In fact that's primarily where these type of operators are used, to establish some criteria by which rows or aggregate rows are filtered from a data set. To use comparison operators we place them between two fields, functions and fields or fixed values. Here's a couple of examples using where and having statements. Here, we have a where clause, which is true when field A is less than or equal to field B. And a having clause, which is true when the sum of field C is not equal to 100. The second type of operators are arithmetic operators, namely plus for addition, minus for subtraction, star for multiplication and forward slash for division. Most SQL engines also support the modulus operator, represented as a percent sign which returns the remainder of one value divided by another value. We can use arithmetic operators in a couple of different ways. We can use them in conjunction with comparison operators in where and having statements to construct more complex conditions like these. Again, we have a where clause which is true when FIELD_A + FIELD_B is equal to FIELD_C. And the having clause which is true when sum of FIELD_D minus 100 is greater than the sum of FIELD_E divided by 2. Note that I can use arithmetic operations on either side or both sides of the comparison operator. Note also that the calculations are done in normal arithmetic order with division and multiplication operations taking precedence over addition and subtraction. For more complex operations I can use parentheses just like I would in common math equations to achieve the right order of operations like this. Here I use parenthesis to ensure that the addition operation happens before the division operation. The other place I can use arithmetic operations is in my select statement. I can create calculation within rows of a data set to construct new columns based on other columns on that row like these. Here, I add FIELD_A to FIELD_B in each row and put it in a new field called FIELD_N. Be careful not to confuse this type of operation with aggregation functions like the sum function, which are designed to aggregate data across rows when a group by command is used. Simple arithmetic operators worked within one row of data across columns. However, I can use arithmetic operations with aggregation functions as well like this. A select statement is an aggregation intended to be used by the group by statement. Here fields C and D are first aggregated using the sum function and then those sums are added together to produce field N in the aggregated output data set. The third type of operators we'll cover are logical operator. There are actually quite a few logical operators available in SQL including some that are unique to specific SQL engines. Just about all of them are primarily used in where or having clauses as we are trying to define specific conditions for row or aggregate faltering. Here we will discuss a few of the most common logical operators that you're likely to use in day-to-day query writing. We'll start with two familiar logical operators called Boolean operators, and and or. We generally use these operators in a where or have in clause where we want to include more than one condition in the clause. When we use and, it means that all conditions in our statement need to be true. When we use or, it means that at least one of the conditions in our statement needs to be true. I can represent the situation visual when using a Venn diagram. In the diagram, the shaded portion represents the condition under which some overall statement is true. In SQL code, it might look something like this. In the where clause, the overall statement is true only when both conditions are true. Field A is less than 10 and field B is greater than 100. And the having clause, the overall statement is true when either of the conditions are true. The sum of field C is equal to 50 or the minimum of field D is greater or equal to 5. Like arithmetic operators, I can construct much more complex conditions using a combination of and operators, or operators and parenthetical expressions to achieve the outcome I want. Again, a little creativity can go a long way in writing SQL. The next logical operator we'll discuss is the in operator. The in operator allows us to set up a condition to determine whether a field value or a expression is contained within a specific list of possible values. You can think of the in operator as short hand for a long list of or conditions I might include in a where or having clause. For example, I could write this, where FIELD_A equals AAA or FIELD_A equals BBB or FIELD_A equals CCC. Or I could write this where FIELD_A in AAA, BBB, CCC. As the list of possible values gets large, using the in operator makes my code a lot simpler. It's also useful when my list of possible values is defined by a sub-query. We discuss this case in a separate video. Another time saving operator is the between operator, which allows us to set up a condition where a field or expression is between two other values or expressions. Again, this is really just a shorthand for a compound condition using comparison operators and the and operator. For example, I could write this, WHERE FIELD_A is greater than or equal to 10 and FIELD_A is less than or equal to 100. Or I could write this, WHERE FIELD_A between 10 and 100. The like operator is a powerful operator that has a lot of flexibility. But its syntax is also a bit more involved. The like operator basically searches for a specific set of characters in a string or text field and returns a true result if there is a match. The tricky part about using like is that I usually need to incorporate one or more wild card characters that indicate where in the string I expect the pattern to occur. The two most common wild card characters we use are the following. The percent symbol means any string of zero or more characters. The underscore character means any single character. It's probably easiest to illustrate how they work with a few examples. This condition will be true for any string that starts with abc, regardless of how long that string is. This condition will only be true if the string starts with abc and is four characters long. This condition will be true if the sub-string abc appears anywhere in the string. You get the idea. This last case is probably those common when where using the like operator. But there are a number of different ways I can use wildcards including some that we won't cover here, to get the desired result. One special condition will add to our discussion of logical operators is the IS NULL condition. This is a special condition that is true when a field or expression is a null which you may recall is a special database value that indicates the absence of data. The syntax is pretty simple. For example, I use IS NULL in a WHERE clause like this, WHERE FIELD_A IS NULL. In analytics we use this condition quite a bit to look for whole scenario of data where the field route row do not have the data we need. The last logical operator we will talk about is the NOT operator. NOT basically reverses the logical meaning of other logical operators. Technically, I can use it with pretty any operator. But there are only a few cases where it really makes sense. The fist is in conjunction with the AND operator. Remember our AND and OR in Venn diagrams? Well, there's a third case of the diagram you might be interested in, namely this one. The shaded part of the diagram represents the case where one condition is true or the other condition is true, but not both conditions. Here's an example of a query that uses NOT with an and operator, to achieve this type of situation. So here we're looking for any case except those where FIELD_A is less that ten and FIELD_B is greater than 100. The other common cases where we use the NOT operator is with the LIKE and IS NULL operators. These cases are a little more straight forward so we'll just illustrate it using a couple of examples. This condition is true whenever FIELD_A does not contain the sub string abc. Similarly, this condition is true when the value of FIELD_A is anything except a database null. So we've covered quite a lot up to this point. Again there are number of logical operators that are available in SQL but the ones we've covered are by far the most common. Let's quickly recap, we covered three types of operators that can expand the usefulness of our SQL queries. We introduced comparison operators which helped determine whether our condition between two fields are expressions of true or false. We then talked about arithmetic operators which we can use a comparative expressions or to transform values and select statements. Finally, we presented a set of common logical operators which helped us represent a variety of different conditions and or where or having statements. Hopefully you've got an appreciation for how much power these operators add to our SQL queries and how they give us the flexibility to create some pretty complex condition. With the goal of the given exactly the day we need for our analysis.