SQL Quick Reference

This topic provides a quick overview of how you can use SQL to create new tables and fields in Sisense.

Basic SQL Syntax Guide

Select Statements

SELECT

SELECT column_name(s)
FROM table_name

SELECT *

SELECT * FROM table_name

SELECT DISTINCT

SELECT DISTINCT column_name(s)
FROM table_name

Note:

Nested SELECT statements will not work with an AND clause in Sisense.

Functions After Select Clause

  • AVG() - Returns the average value

  • COUNT() - Returns the number of rows

  • MAX() - Returns the largest value

  • MIN() - Returns the smallest value

  • SUM() - Returns the sum

Table Join Functions

INNER JOIN

SELECT column_name(s)
FROM table_name1 T1
INNER JOIN table_name2 T2
ON T1.column_name=T2.column_name

LEFT JOIN

SELECT column_name(s)
FROM table_name1 T1
LEFT JOIN table_name2 T2
ON T1.column_name=T2.column_name

RIGHT JOIN

SELECT column_name(s)
FROM table_name1 T1
RIGHT JOIN table_name2 T2
ON T1.column_name=T2.column_name

FULL JOIN

SELECT column_name(s)
FROM table_name1 T1
FULL JOIN table_name2 T2
ON T1.column_name=T2.column_name

AS (alias)

SELECT column_name AS column_alias
FROM table_name

or

SELECT column_name
FROM table_name AS table_alias

WHERE

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Functions After the Where Clause

AND / OR

SELECT column_name(s)
FROM table_name
WHERE condition AND|OR condition

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

IN

SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)

LIKE

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

Ordering and Grouping After Where Conditions

GROUP BY

SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

ORDER BY

SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]

HAVING

SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Clauses for Combining Tables

UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION ALL

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Example SQL Query

Below is a side-by-side example of an SQL query with some SQL main clauses next to the corresponding syntax.

SQL Example Corresponding SQL Syntax 
SELECT CustomerLastName, CustomerName, ContactEmail, Count(OrderID) AS Counter
FROM Customers c
INNER JOIN Order o
ON c.CustomerID = o.CustomerID
WHERE
o.OrderId BETWEEN 10 AND 100 AND
(c.CustomerName IN ('John','Mary','David') OR c.CustomerLastName LIKE 'Harrison%')
GROUP BY c.CustomerLastName, c.CustomerName, c.ContactEmail
HAVING Count(o.OrderID) > 3
ORDER BY c.CustomerLastName, c.CustomerName
 
SELECT FieldName(s),Function(), *
FROM TableName 1
INNER JOIN\ LEFT JOIN\RIGHT JOIN\FULL JOIN TableName 2
ON TableName 1. JoinField = TableName 2. JoinField
WHERE FieldName Condition
AND\OR FieldName Condition
 
GROUP BY FieldName(s)
HAVING FieldName(s) Condition
ORDER BY FieldName(s)

Other SQL Reference Documentation

The Snowflake SQL Command Reference documentation can provide additional detail regarding the use of SQL and SQL syntax.