Data & Analytics

|

September 9, 2020

Most Useful SQL Operators, Functions, and Clauses

Blurred close-up of SQL code with slight focus on highlighting a function

In our SQL 101 and Advanced SQL articles, we looked at how basic SQL queries work. In them, we saw special keywords (SELECT, INSERT, UPDATE, etc.) known as clauses which allow us to perform different tasks. In this article we’re going to cover two more important aspects of SQL queries: Functions and Operators.

Functions are a very common concept in programming that you might hear being used quite often. Functions make redundant, complicated tasks simple and easy to execute. If something takes 100 lines of code to implement, a function can repeat the purpose of that code segment with only one line. They clean up your code and make things efficient. The task a function performs only needs to be written out once. From then on, you simply reference the function to execute the same task. 

SQL has an arsenal of functions that make your life easier. In some versions of SQL, you can even make your own functions. In SQL, self-made functions are known as procedures.

Operators essentially allow us to make our queries work on certain conditions. In normal programming, operators include mathematical arithmetic as well. In SQL, that arithmetic is rarely encountered and instead makes use of conditions mostly. The most commonly encountered conditional operators are called AND and OR. They allow us to add more constraints to our queries.

When we combine clauses, functions, and operators together, we can produce incredibly powerful queries. Let’s now examine the most useful SQL Functions, Clauses, and Operators you should know.

NOTE: SQLite can be used for the below examples.

1. AND/OR

In the previous articles, we discussed the power of the WHERE clause and how it allows us to produce results based on specific conditions. In those examples, we had only one WHERE clause in every query. 

With the  AND and OR operators, we can have an infinite number of conditions added to every query. These operators work just like they do in the English language. When we use the word ‘and’, we mean to include everything. ‘Or’ means one or the other. The conditions can be made more complex with the use of parentheses.

When an AND operator is used, the results produced from the query make sure that all conditions are satisfied. If we are to add 2 different WHERE clauses separated by the AND operator, the results produced will satisfy both conditions. If one of them is false for a certain row, it won’t show up in the results. All of them need to be true.

SQLite dashboard featuring code "SELECT * FROM Receipts WHERE discountitems=2 AND storename='Nike'"


In the following query, the store name doesn’t exist and no results were given


SQLite dashboard featuring code "SELECT * FROM Receipts WHERE discountitems=2 AND storename='asdf'"

In the case of the OR operator, the results produced from the query make sure that at least one of the conditions is true. If we had, again, 2 different WHERE clauses separated with the OR operator, the results produced will satisfy at least one of the conditions. Both the conditions can be true as well. 


Dashboard results from previous function, showing 12 items for Nike


Here, both conditions are false and nothing shows.


Previous function discountitems value changed to "100"


2. AS

AS is a clause that allows our results to bear different names. When we normally select columns in our queries, they show exactly as they exist in the database. With AS, we can assign aliases to columns to give more clarity. A good use case of this clause is to explain the meaning of the results to someone who isn’t familiar with your database structure. In this case, we’ll change the ‘Item’ column to ‘Item_count’.


SQLite dashboard featuring function "SELECT items AS item_count,storename FROM Reciepts" with columns of Item_count and Storename below
Using the AS clause has no effect on the database itself, it simply allows you to display a different column name in your data, report, dashboard, etc.


3. CASE-WHEN

CASE-WHEN is a clause which works based on a set of conditions. Through this clause, we can assign different results to each row that is produced in the report. The results are declared in a text format and are used to convey some information about the specific row. The syntax looks like:

SELECT *

CASE WHEN condition-A THEN ‘X’

WHEN condition-B THEN ‘Y’

ELSE  ‘Z’

END AS column name

FROM table name;


CASE-WHEN is SQL’s version of an IF-THEN statement. If condition A is true, then show X. If condition B is true, then show Y. If neither is true, then show Z. The placeholders for ‘WHEN condition’ are essentially WHERE clauses with a different name. Every row will be inspected and each condition will be in an interactive manner. Whenever a true condition is found, the other cases will never get checked. That means if condition-A is satisfied, condition-B will never get checked. Likewise, if condition-B is true, that means condition-A was false for this row. If none of the conditions are true, the text after ELSE will be inserted in the new column.

SQLite dashboard with function following the format above but specifically for reciept, store, and discount use-case

4. BETWEEN

BETWEEN is an operator that selects values based on the range given. The data types that can be included in a range are numbers, dates, and text. It’s a simple way to test whether certain values belong in a given range (inclusive of starting and ending values) and produce results accordingly. The syntax looks like:

SELECT + column1 name + column2 name + … + FROM table name + WHERE + column name + BETWEEN + value1 + AND + value2;


The following examples show queries being run for the above-mentioned data types.

SQLite program with the code "SELECT storename, totalprice FROM Receipts WHERE totalprice BETWEEN 500 AND 900" with table of Storename and Totalprice underneath


SQLite program with the code "SELECT dateissued, totalprice FROM Receipts WHERE dateissued BETWEEN [date 1] AND [date 2]" with table of Dateissued and Totalprice underneath


SQLite program with the code "SELECT storename FROM Receipts WHERE storename BETWEEN 'Adidas' AND 'Nike'" with table of Storename underneath


An operator called NOT can also be inserted with BETWEEN so that everything outside the specified range is selected. Here is an example of the NOT operator being added into the query.

SQLite program with the code "SELECT storename, totalprice FROM Receipts WHERE totalprice NOT BETWEEN 500 AND 900" with table of Storename and Totalprice underneath

5. ORDER BY

The ORDER BY clause assists in organizing our reports either in ascending or descending order. When the order is not specified, ascending is chosen by default. We can order our data on the basis of numbers, dates, and text. The syntax looks like:

SELECT + column1 name + column2 name + … + FROM table name + ORDER BY + column name ASC| DESC;


The ASC keyword is for ascending and DESC is for descending. Example query:

SQLite program with the code "SELECT * FROM Receipts ORDER BY totalprice" with table of Receipts in increasing order of Totalprice underneath


In this query, we use the DESC keyword.

SQLite program with the code "SELECT * FROM Receipts ORDER BY totalprice" with table of Receipts in decreasing order of Totalprice underneath


We can also order our results based on several columns. This is useful if rows can have the same values for one column, but still need to be prioritized.

SQLite program with the code "SELECT * FROM Receipts ORDER BY totalprice, storename" with table of Receipts in first increasing order of Totalprice, then A-Z order of Storename underneath


The next query shows multiple columns being ordered with DESC.

SQLite program with the code "SELECT * FROM Receipts ORDER BY totalprice DESC, storename DESC" with table of Receipts in first decreasing order of Totalprice, then Z-A order of Storename underneath


We can also sort individual columns with ASC or DESC in one query.

SQLite program with the code "SELECT * FROM Receipts ORDER BY totalprice ASC, storename DESC" with table of Receipts in first increasing order of Totalprice, then Z-A order of Storename underneath

6. MAX/MIN

MAX and MIN are functions that return us the maximum and minimum values of the specified columns respectively.

These functions can be used with numbers, dates, and text. The concept of the maximum value from text is complicated, so we won’t cover it. If you’d like, you can read about it here. The syntax looks like:

SELECT + MAX/MIN(column name) + FROM + table name;


Now, here's a sample query with MAX on a numeric column:

SQLite program with the code "SELECT max(totalprice) FROM Receipts" with table of maximum totalprice

Example with a date data type:

SQLite program with the code "SELECT max(dateissued) FROM Receipts" with table of latest dateissued


Example with MIN on a numeric value:

SQLite program with the code "SELECT min(totalprice) FROM Receipts" with table of mininum totalprice

Query with MIN applied to a date column:

SQLite program with the code "SELECT min(dateissued) FROM Receipts" with table of oldest dateissued

7. LIMIT

When dealing with a large number of rows, you run the risk of slowing your system if your reports are too big. In the worst case, you can freeze it too. To cater to that, the LIMIT clause restricts the number of rows you want to be displayed. The syntax looks like:

SELECT + column1 name + column2 name + ... + FROM table name + LIMIT + number;


The ‘number’ placeholder will be replaced by a numeric value. The number you choose will determine the number of rows that will be displayed. If you choose 3, only the first 3 rows will be displayed.

SQLite program with the code "SELECT storename, totalprice FROM Receipts LIMIT 3" with of 3-row table of storename and totalprice underneath

You can also choose to skip any number of rows before displaying the chosen amount. To do that, simply add ‘, number’ to the above query. We skip the first row and select the next 3 here.

SQLite program with the code "SELECT storename, totalprice FROM Receipts LIMIT 1,3" with of 3-row table of storename and totalprice underneath (skipping what would have been the first row)

8. COUNT

COUNT is a function that returns the number of rows in a table based on the specified conditions. If no conditions are given, a count of all the rows will be provided. The syntax looks like:

SELECT + COUNT(*) + FROM + table name;


The above query will return the count of all the rows in the table with no conditions:

SQLite program with the code "SELECT COUNT (*) FROM Reciepts" with of a table of the amount of reciepts underneath

In order to know the count of unique, non-empty values in a column, include DISTINCT in the query: SELECT COUNT(DISTINCT column name) FROM table name. 

SQLite program with the code "SELECT COUNT (DISTINCT storename) FROM Reciepts" with a table of the amount of unique storenames underneath

9. HAVING

Before we can understand the HAVING clause, we need to understand the GROUP BY clause. To recap on its usage, visit the [SQL 101 article] (link). When we’ve successfully made groups of similar values, we can use the HAVING clause to filter them. We used the WHERE clause to filter rows and we use the HAVING clause to filter groups made with GROUP BY. An important point to remember is the HAVING clause cannot be used without GROUP BY. The syntax looks like:

SELECT + COUNT(column1 name) + column1 name + FROM + table name + GROUP BY + column1 name + HAVING condition;


SQLite program with the code "SELECT storename, COUNT(storename) FROM Reciepts GROUP BY storename HAVING storename='Bata'" with a table of storename and count of storename for Bata underneath

10. SUM/AVG

SUM and AVG provide the sum and average of columns, respectively. They can either be used independently or with the GROUP BY clause to give more meaningful insights. SUM and AVG only work with columns of numbers, not text. The syntax looks like:

SELECT + SUM/AVG (column name) + FROM + table name;


This basic syntax will net you the sum or average of a column:


SQLite program with the code "SELECT SUM(totalprice) FROM Receipts" with a sum of totalprice underneath


SQLite program with the code "SELECT AVG(totalprice) FROM Receipts" with an average of totalprice underneath


To include GROUP BY into the query, use the following syntax: SELECT + SUM/AVG (column1 name) + column2 name + … + FROM + table name + GROUP BY + column2 name:

SQLite program with the code "SELECT storename, SUM(totalprice) FROM Receipts GROUP BY storename" on top of a table of storename and their respective totalprice sum


SQLite program with the code "SELECT storename, AVG(totalprice) FROM Receipts GROUP BY storename" on top of a table of storename and their respective totalprice average


Further exploration

We’ve just barely scratched the surface of all the tools available in SQL. Once you practice the above, you’ll be able to produce more complex queries very easily. There are also many optional constraints that you can add to these clauses. Covering all of them would take a book.

If you’re interested in learning in more detail about SQLite, check out the following links:

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.