SQL Reference

You can use SQL to create new tables and fields in Sisense.

Below is an example of an SQL query with some SQL main clauses:

SQL Structure SQL Example

SQL Structure	SQL Example
`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)`	`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)

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 STATEMENT
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

FUNCTIONS

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_nameor 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 TO COMBINE 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