Start writing SQL queries — Part II

Essential for day-to-day work of a data analyst

In the article “Start writing SQL queries” , you learned about the world of data and the basics of working with SQL, which is an essential language for a data analyst to extract or aggregate data. You used comparison operators, AND operator, OR operator and understood the importance of adding parentheses to conditions in WHRE clause. It’s time to explore other elements of SQL. In this part, we will continue with the topic of operators.

• Let’s start with the IN operator. The IN operator is shorthand for many OR conditions. It accepts a list of values separated by commas and placed in parentheses. Allows you to retrieve records that satisfy the condition that at least one value in the list must be equal to the operand.

Example of IN operator

The IN operator can also contain another SELECT statement inside the parentheses (Subquery), this is a more advanced topic which will be discussed in future articles.

• The BETWEEN operator allows you to define a range to which the given value belongs. The range is inclusive: end values and begin values are included.

Example of BETWEEN operator

• The LIKE operator checks whether the specified string matches a defined pattern. This operator is commonly used with two special characters: % and _. The % wildcard represents any number of any character. The _ wildcard represents exactly one occurrence of any character. Here’s a practical application:

Example of LIKE operator

• IS NULL operator

Let’s start by specifying the NULL value. SQL NULL means that we do not have a value for the field. If the table column allows NULL to be used, it means that we can insert a new record or update an existing one without adding a value. Whether or not the selected column accepts NULL values is determined by the table definition. This function can be useful when we do not know what the value will be and we need a temporary placeholder. An example can be the customer’s address — maybe the customer has just created an online account and doesn’t want to give his address. In such case, we can replace the field value with NULL. When customer makes an order and submits address, we can update this field.

To be clear, NULL is not the same as empty text written as ‘’ (two apostrophes next to each other) or zero.

The standard comparison operators (=, >, <, <>, !=, >=, <=) will not work for NULL values. Because NULL value specifies no value, we cannot explicitly determine if e.g. NULL > 3. In this case we need IS NULL operator. The IS NULL condition allows you to filter records for which the selected field has no value.

Example of IS NULL operator

• The NOT operator can be added to a WHERE clause to negate the condition that is placed after it. Examples of the use of the NOT in combination with already learned operators, such as IN, LIKE, BETWEEN and IS NULL are given below.

NOT IN

Example of NOT IN operator

NOT BETWEEN

Example of NOT BETWEEN operator

NOT LIKE

Example of NOT LIKE operator

IS NOT NULL

Example of IS NOT NULL operator

Congratulations, you are now on your next step towards mastering the basics of writing SQL queries! You are now able to extract data from a table, filter table output using comparison operators and other advanced operators such as AND, OR, IN, BETWEEN, LIKE, IS NULL, and filter using their negation. You can be proud of yourself. I recommend you to read the next part of this article, which will contain information about arithmetic operators, aggregate functions, and the part of SQL syntax that you might not be familiar with GROUP BY.

Follow me on @just.analyze.this, where I also share my passion for data analytics.

Thank you and have a nice day!