Page History
...
An object name may be quoted or unquoted. If an object name contains special characters, you must quote it whenever you refer to it. The quote identifiers supported by ExcelSQL are the square brackets ([object name]) and double quotes ("object name").
Anchor | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
Info |
---|
Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes. |
Query Syntax
ExcelSQL queries are SELECT statements of below form:
...
Code Block |
---|
SELECT [NO_HEADER_ROW] [DISTINCT] * | column1 [AS alias1], column2 [AS alias2], ... [FROM worksheet] [WHERE condition] [GROUP BY column1, column2, ...] [HAVING condition] [ORDER BY column1 [DESC|ASC], column2 [DESC|ASC], ...] [LIMIT number [OFFSET number] GO |
SELECT clause
The SELECT clause is used to select data from an Excel worksheet.
SELECT * | COUNT(*) | column1, column2, ...
SELECT * | column1 [AS alias1], column2 [AS alias2]
Example
Code Block |
---|
SELECT * FROM Sheet1 GO SELECT city, zipcode FROM zips GO SELECT COUNT(*) FROM zips GO SELECT SUM(population) AS [Total Population] FROM zips GO |
NO_HEADER_ROW option
NO_HEADER_ROW
By default, the first row is considered as headers. If there is no header, specify the NO_HEADER_ROW option and Excel's headers will be used as column names.
...
Code Block |
---|
SELECT NO_HEADER_ROW * FROM Sheet2 GO SELECT NO_HEADER_ROW COUNT(*) FROM Sheet2 GO SELECT NO_HEADER_ROW B, C FROM Sheet2 GO SELECT NO_HEADER_ROW [employee].A, [project].B AS Project FROM employee INNER JOIN project ON [employee].B = [project].B GO |
DISTINCT clause
DISTINCT
A column can contain duplicate values, and to list the distinct values, use the SELECT DISTINCT clause. The DISTINCT clause can be used to return only distinct values from a set of records.
...
Code Block |
---|
SELECT DISTINCT city FROM zips GO SELECT DISTINCT * FROM employee GO |
FROM clause
Determines the specific dataset to examine to retrieve data. For Excel, this would be indicated as a specific worksheet.
We support the following syntax for specifying a worksheet that is in a different schema (Excel file) than the currently connected schema.
SELECT * FROM workbook.worksheet
where the Excel file name is workbook and the worksheet name is worksheet.
SubTable feature
In addition, to query a range of cells in the worksheet, use the following syntax to specify the starting cell and the ending cell of the range.
SELECT * FROM [worksheet$A1:F500]
where the worksheet name is worksheet, the starting cell is A1, and the ending cell is F500.
...
You can also use the LIMIT and OFFSET clauses to query a range of cells in the worksheet.
SELECT * FROM worksheet LIMIT 500 OFFSET 10
Examples
Code Block |
---|
SELECT NO_HEADER_ROW B, C, D FROM orders LIMIT 1000 OFFSET 5 GO |
WHERE clause
The WHERE clause, sometimes called the predicate, states the qualifying conditions for a query. You can combine the ExcelSQL Arithmetic Operators and the ExcelSQL Comparison Operators in the WHERE clause. Multiple conditions can be joined by the AND and OR clauses, optionally surrounded by (parentheses) to group them. Only the records that satisfy the specified criteria are returned by the query.
SELECT column FROM worksheet WHERE column operator value …
Conditions:
condition AND condition
condition ANY condition
condition OR condition
column = predicate
column != predicate
column <> predicate
column >= predicate
column > predicate
column <= predicate
column < predicate
column EXISTS
column NOT EXISTS
column BETWEEN value1 AND value2
column NOT BETWEEN value1 AND value2
column IN (value [, value ...])
column LIKE like_expr
column IS NULL
column IS NOT NULL
column NOT (value [, value ...])
column NOT IN (value [, value ...])
column NOT LIKE like_expr
When specifying the condition, value must be an exact match of the column value in the worksheet. String value comparisons are case sensitive. You can use the UPPER() function to perform case insensitive comparisons. You can find more information here. Or you can use the SET IGNORECASE TRUE command to make string comparisons be case insensitive.
...
Code Block |
---|
SELECT * FROM mixedDataTypes.Sheet1 WHERE TO_CHAR(column2) = '11.0' GO SELECT * FROM mixedDataTypes.Sheet1 WHERE TO_CHAR(column2) = '11.0' OR TO_CHAR(column2) = 'test' GO |
GROUP BY clause
The GROUP BY clause is used in combination with aggregate functions to group the results by one or more columns. See the SQL Functions page for a list of aggregate functions supported by ExcelSQL.
SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1
Example
Code Block |
---|
SELECT name, COUNT(orders) FROM zips GROUP BY name GO |
HAVING clause
The HAVING clause states the qualifying conditions for aggregated values. It is used in conjunction with aggregate functions to filter aggregated values.
SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1
HAVING aggregate_function(column2) operator value
Example
Code Block |
---|
SELECT name, COUNT(orders) FROM zips GROUP BY name HAVING COUNT(orders) > 25 GO |
ORDER BY clause
ORDER BY is used to sort the results by one or more columns and sorts in ascending order by default. To sort in descending order, use the DESC keyword.
SELECT column1, column2 FROM worksheet ORDER BY column1 ASC | DESC, column2 ASC | DESC
Examples
Code Block |
---|
SELECT * FROM zips ORDER BY country DESC GO SELECT city, zipcode FROM zips ORDER BY city, zipcode GO |
...
Code Block |
---|
SELECT PostalCode FROM zips ORDER BY TO_CHAR(PostalCode) GO |
LIMIT and OFFSET clause
LIMIT and OFFSET allow you to retrieve just a portion of the result set returned by the query. LIMIT restricts the number of rows returned by the SELECT statement. OFFSET indicates how many rows to skip. OFFSET 0 is the same as omitting the OFFSET clause.
SELECT column FROM worksheet LIMIT number OFFSET number
Examples
Code Block |
---|
SELECT * FROM zips LIMIT 5 GO SELECT * FROM city LIMIT 2 OFFSET 3 |
Set Operators
ExcelSQL supports the below set operators. Set operators combine results from two or more queries into a single result set.
MINUS, EXCEPT, INTERSECT
UNION and UNION ALL
MINUS
The MINUS operator returns only unique rows that are returned by the first query but not by the second query.
...
Code Block |
---|
SELECT city, zipcode FROM zips MINUS SELECT city, zipcode FROM pins |
EXCEPT
The EXCEPT operator returns rows that are returned by the first query but not by the second query.
...
Code Block |
---|
SELECT city, zipcode FROM zips EXCEPT SELECT city, zipcode FROM pins |
INTERSECT
The INTERSECT operator returns only rows that are returned by both queries.
...
Code Block |
---|
SELECT city, zipcode FROM zips INTERSECT SELECT city, zipcode FROM pins |
UNION Operator
The UNION operator combines the results of two queries and eliminates duplicate rows.
SELECT column_names(s) FROM table1 UNION SELECT column_names(s) FROM table2
Examples
Code Block |
---|
SELECT city, zipcode FROM zips UNION SELECT city, zipcode FROM pins GO |
UNION ALL Operator
The UNION ALL operator combines the results of two queries. It does not remove duplicate rows. All rows are returned.
SELECT column_names(s) FROM table1
UNION ALL
SELECT column_names(s) FROM table2
Examples
Code Block |
---|
SELECT city, zipcode FROM zips UNION ALL SELECT city, zipcode FROM pins GO |
SUBQUERY
A subquery is a SELECT statement nested inside a SELECT statement or inside another subquery. It is usually added within the WHERE clause of another ExcelSQL SELECT statement.
...
Code Block |
---|
SELECT (SELECT P_id FROM Project where E_id=1), Fname FROM Employee WHERE E_id=1 GO SELECT a,b AS total_sum FROM (SELECT SUM(expenses) AS a, SUM(Salary) AS b FROM Employee) GO SELECT * FROM Employee E WHERE E_id IN (SELECT P_id FROM Project WHERE P_id = 10001) GO |
SQL Aliases
SQL aliases are used to temporarily assign a different name to a table or column heading. Basically aliases are created to make column names more readable. The WHERE, ORDER BY, GROUP BY, HAVING and JOIN clauses also support aliases for ExcelSQL.
...
Code Block |
---|
SELECT zipc AS ZipCode FROM zips GO SELECT city FROM zips AS Z GO SELECT A.E_id FROM employee AS A WHERE A.E_id = 1 GO SELECT COUNT(A.E_id) as B FROM Employee AS A HAVING COUNT(A.age) < 10 GO SELECT A.E_id, B.P_id FROM Employee AS A INNER JOIN Project AS B ON B.p_id = A.e_id GO |
Joins
There are different types of joins available in ExcelSQL: INNER JOIN, LEFT JOIN and RIGHT JOIN. INNER JOIN returns rows when there is a match on both tables. LEFT JOIN returns all rows from the left table even if there are no matches in the right table. RIGHT JOIN returns all rows from the right table even if there are no matches in the left table. You can find the syntax for the different joins below.
INNER JOIN clause
SELECT column1, column2 FROM worksheet1 INNER JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
...
Code Block |
---|
SELECT C.name, O.customerID from [pubs].Customers C INNER JOIN [bi].Orders O ON C.customerID = O.customerID INNER JOIN [pubs].Sales S ON S.orderID > C.customerID |
LEFT JOIN clause
SELECT column1, column2 FROM worksheet1 LEFT JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2
The LEFT JOIN clause returns all rows from the left table (worksheet1) with the matching rows in the right table (worksheet2). The result set returns no values in the right table when there is no match.
...
Code Block |
---|
SELECT C.name, O.customerID FROM Customers C LEFT JOIN Orders O ON C.customerID = O.customerID |
RIGHT JOIN clause
SELECT column1, column2 FROM worksheet1 RIGHT JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2
The RIGHT JOIN clause returns all rows from the right table (worksheet2) with the matching rows in the left table (worksheet1). The result set returns no values in the left table when there is no match.
...
Note that you can use the UNION ALL clause to combine two joins to achieve a a FULL JOIN.
Example
Code Block |
---|
SELECT C.name, O.customerID FROM Customers C LEFT JOIN Orders O ON C.customerID = O.CustomerID UNION ALL SELECT C.name, O.customerID FROM Customers C RIGHT JOIN Orders O ON C.customerID = O.customerID |
Self Join
SELECT Column1, Column2 FROM Worksheet1 A, Worksheet1 B WHERE A.Column1 = B.Column1
Self join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
...
Code Block |
---|
SELECT A.E_id, B.E_id FROM employee A, employee B WHERE A.E_id = B.Mgr_id |
CROSS JOIN clause
SELECT Column1,Column2 FROM Worksheet1 CROSS JOIN Worksheet2
The CROSS JOIN (or CARTESIAN JOIN) returns the Cartesian product of the sets of records from two or more joined tables.
...
Code Block |
---|
SELECT A.E_id, B.P_id, A.fname FROM employee A CROSS JOIN project B |
Multiple AND/OR Conditions on Joins
Example
Code Block |
---|
SELECT * FROM employee INNER JOIN project ON employee.E_id = Project.P_id AND employee.fname = project.p_name AND employee.salary = project.p_profit GO SELECT * FROM employee LEFT JOIN project ON employee.E_id = Project.P_id OR employee.fname = project.p_name OR employee.salary = project.p_profit |
Derived Tables
The Derived table is a technique for creating a temporary set of records which can be used within another query in ExcelSQL. You can use derived tables to shorten long SQL queries.
...
Code Block |
---|
SELECT Project.P_id, Project.budget FROM (SELECT E.E_id, E.Salary FROM Employee E) emp INNER JOIN Project on emp.E_id = Project.P_id |
ExcelSQL Arithmetic Operators
Arithmetic operators perform mathematical operations on two expressions of one or more datatypes of the integer data type category.
+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
% (Modulo, returns the integer remainder of a division)
Example
Code Block |
---|
SELECT 15+10-5*5/5 FROM [bi].zips GO SELECT cust_name, openg_amt, receiv_amt, outstand_amt, (receiv_amt*5/ 100) AS commission FROM [bi].customer WHERE outstanding_amt <= 4000 GO SELECT StockUnits, StockUnits%3 AS "Modulo by 3" FROM [bi].products |
ExcelSQL Comparison Operators
Comparison operators test whether two expressions are the same.The following table lists the ExcelSQL comparison operators.
= (Equal to)
> (Greater than)
< (Less than)
>= (Greater than or equal to)
<= (Less than or equal to)
!= (Not equal to)
<> (Not equal to)
Example
Code Block |
---|
SELECT * FROM [bi].zips WHERE city = 'Cochin' GO SELECT * FROM [bi].zips WHERE city <> 'Cochin' GO SELECT * FROM [bi].zips WHERE zipcode <= 682017 GO |
Configuration Functions
SELECT @@database
This function returns information on the current database context.
...
Code Block |
---|
SELECT @@database |
SELECT @@schema
This function returns information on the current schema context.
...
Code Block |
---|
SELECT @@schema |
Utility Statements
DATABASE database_name
This statement sets the database context and keeps this context for subsequent statements until the end of the session or another DATABASE statement is encountered.
...
Code Block |
---|
DATABASE Folder2 |
SET SCHEMA schema_name
This statement sets the schema context and keeps this context for subsequent statements until the end of the session or another SET SCHEMA statement is encountered.
...
Code Block |
---|
SET SCHEMA ExcelFile2 |
SET IGNORECASE TRUE|FALSE
This statement specifies whether text columns are case sensitive. Default is FALSE (i.e. case sensitive). SET IGNORECASE TRUE changes text comparisons to be case insensitive.
...
Code Block |
---|
SET IGNORECASE FALSE |
SET COLLATION { OFF | collationName [ STRENGTH { PRIMARY | SECONDARY } TERTIARY | IDENTICAL } ] }
This statement sets the collation used for comparing and sorting strings. The COLLATION setting takes precedence over the IGNORECASE setting.
...
Code Block |
---|
SET COLLATION ENGLISH STRENGTH PRIMARY |
See the the SHOW Commands page for additional information about commands used to describe database schema objects.