Operators in SQL for Data Analysis 📊

Operators are essential for data wrangling and filtering.

If you are just getting started with SQL and want to get familiar with the basic syntax then refer my previous blog first :

Introduction to SQL for Data Analysis 📈

Already familiar? Let’s get started!

Operators are an integral part in the data wrangling process and are used in almost any query you would write as a Data Analyst. Operators can be classified to two categories , comparison and arithmetic.

Comparison Operators — Numerical Data

Comparison Operators — Non numerical Data

All of the comparisons still work however they are done on the ASCII value (integer value equivalent of characters) and not the domain value.

Example : January comes before February but since F comes before J in the alphabet January > February :P

Note : While working with values always put them inside SINGLE quotes like ‘January’.

Arithmetic Operators

SELECT Colname1 + Colname2 AS combinedname
FROM Tablename

The above code will add the values in Colname1 and Colname2 and print it under the heading combinedname for each row . Here combinedname is the derived column.

The operators and columns can also be chained together like col1+col2*col3 and for specifying order in which operators are to be processed use round brackets like (col1 + col2)*col3.

Logical Operators

To supplement the WHERE clause and perform data filtering with more than one condition at a time, logical operators are used.

LIKE

• Allows you to match similar values, instead of exact values.
• Values in columns can be compared to a given pattern.
• % represents a single character or string of characters.
• _ represents a single character only.
• LIKE can be be substituted with ILIKE if the results should not be case sensitive.
• Usage:

SELECT *   
FROM Tablename  
WHERE colname LIKE 'pattern%'

IN

• Allows you to specify a list of values you'd like to include.
• IN can be used for both numerical and non numerical values.
• Usage:

SELECT *   
FROM Tablename 
WHERE colname IN ('val1','val2','val3'....)

BETWEEN

• Allows you to select only values within a certain range.
• Usage:

SELECT *   
FROM Tablename 
WHERE colname BETWEEN val1 AND val2

• Returns all the rows for which the value of colname lies between val1 and val2 inclusive.
• Note : val1 and val2 can be numerical or dates.

IS NULL

• Allows you to select rows that contain no data in a given column.
• Empty cells are considered to be NULL in SQL.
• IS NULL checks if the specified column of each row is null or not and returns the row if it is.
• Usage :

SELECT *   
FROM Tablename 
WHERE colname IS NULL

• Note : WHERE colname = NULL will not work as you can’t perform arithmetic on null values.
• The negation of IS NULL is NOT NULL and is used to select rows where the specified column is . . . . . . not null (duh!).

AND

• Allows you to select only rows that satisfy all conditions.
• Conditions can comprise other comparison and arithmetic operators. Example : col1 + col2 < 6
• Usage:

SELECT *   
FROM tutorial.billboard_top_100_year_end  
WHERE cond1 AND cond2

OR

• Allows you to select rows that satisfy either of two conditions.
• Selects the row if any of the conditions evaluate to true.
• Usage:

SELECT *   
FROM tutorial.billboard_top_100_year_end  
WHERE cond1 OR cond2

NOT

• Allows you to select rows that do not match a certain condition.
• Selects the row only if the condition evaluates to false.
• Usage:

SELECT *   
FROM tutorial.billboard_top_100_year_end  
WHERE NOT cond

That’s all for this blog!

In the next part we’ll discuss about the aggregate functions kile SUM, COUNT, AVG etc that supplement the GROUP BY clause. Buckle up!

Link to the next part :

Aggregate Functions in SQL for Data Analytics 📉

Interested in daily data analysis content? Follow me on Twitter and Medium.