Page History
...
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 |
Note
- ORDER BY clause cannot be used on a column with mixed data types. An error message "Data conversion error converting" will be displayed.
If a column contains data with mixed data types, the data needs to be converted to one data type. Use the data conversion functions in the query.
Example
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.
...
The MINUS operator returns only unique rows that are returned by the first query but not by the second query.
Examples
...
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.
Examples
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.
Examples
...
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.
Examples
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.
...
SELECT column_name FROM worksheet AS alias_name
Examples
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.
...
In addition, nested joins are supported.
Example
Code Block |
---|
SELECT C.name, O.customerID FROM Customers C INNER JOIN Orders O ON C.customerID = O.CustomerID |
...
Example for 3 table joins in the same schema (same workbook)
Code Block |
---|
SELECT C.name, O.customerID FROM [pubs].Customers C INNER JOIN [pubs].Orders O ON C.customerID = O.customerID RIGHT JOIN [pubs].Sales S ON S.orderID = C. |
...
customerID |
Example for 3 table joins between different schemas (different workbooks)
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
...
In addition, nested joins are supported.
Example
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
...
In addition, nested joins are supported.
Example
Code Block |
---|
SELECT C.name, O.customerID FROM Customers C RIGHT JOIN Orders O ON C.customerID = O.customerID |
...
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
...
The valid operator for the ON clause are AND, OR, =, >, <, <>, >=, <=, !=.
Example
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.
Example
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.
Examples
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.
...
% (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.
...
!= (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.
Example
Code Block |
---|
SELECT @@database |
...
SELECT @@schema
This function returns information on the current schema context.
Example
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.
Example
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.
Example
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.
Example
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.
...
STRENGTH option: PRIMARY is usually case- and umlaut-insensitive; SECONDARY is case-insensitive but umlaut-sensitive; TERTIARY is both case- and umlaut-sensitive; IDENTICAL is sensitive to all differences and only affects ordering.
Example
Code Block |
---|
SET COLLATION ENGLISH STRENGTH PRIMARY |
...
See the the SHOW Commands page for additional information about commands used to describe database schema objects.Back to top