SQL Support in ShardOne

Synopsis

SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT count ]
[ OFFSET count ]

where from_item is one of

table_name [ [ AS ] alias ]

from_item join_type from_item
[ ON join_condition | USING ( join_column ) ]

The table_name could be either

<Datasource_Name>.<Table_Name>

(eg: MyMongoDB.User)

Item_Alias_Name

(eg: Person)

The join_condition is one of

[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
NATURAL JOIN

Note: If the table name or any attribute is an SQL Reserved Keyword, then it needs to be enclosed in backticks (``). This enables ShardOne to distinguish between a keyword and a table/attribute.

For example,

SELECT `year` FROM `Groups`

Prior to delving into the intricacies of the clauses, a sample table named "Users" is introduced, aiding in the comprehension of the ensuing clauses.

+----+----------------+-----+---------------+
| ID | Name           | Age | City          |
+----+----------------+-----+---------------+
| 1  | John Doe       | 32  | New York      |
| 2  | Jane Smith     | 32  | Los Angeles   |
| 3  | Mike Johnson   | 40  | Chicago       |
| 4  | Sarah Thompson | 28  | Houston       |
| 5  | David Wilson   | 36  | San Francisco |
+----+----------------+-----+---------------+

SELECT clause

The SELECT clause specifies the output of the query. Each select_expression defines a column or columns to be included in the result.

SELECT [ ALL | DISTINCT ] select_expression [, ...]

The quantifiers ALL and DISTINCT control the inclusion of duplicate rows in the result set. When using the ALL argument, all rows are included. On the other hand, when using the DISTINCT argument, only unique rows are included. It is important to note that for the DISTINCT argument, the columns in the output must be of a type that allows comparison. If neither argument is specified, the default behavior is equivalent to using the ALL argument.

Each select_expression must be in one of the following forms:

expression [ [ AS ] column_alias ]

*

In the case of expression [ [ AS ] column_alias ], a single output column is defined.

For example,

 SELECT Name as UserName FROM Users

+----------------+
| UserName       |
+----------------+
| John Doe       |
| Jane Smith     |
| Mike Johnson   |
| Sarah Thompson |
| David Wilson   |
+----------------+

In the case of *, all columns of the relation defined by the query are included in the result set.

For example,

SELECT * FROM Users

+----+----------------+-----+---------------+
| ID | Name           | Age | City          |
+----+----------------+-----+---------------+
| 1  | John Doe       | 32  | New York      |
| 2  | Jane Smith     | 32  | Los Angeles   |
| 3  | Mike Johnson   | 40  | Chicago       |
| 4  | Sarah Thompson | 28  | Houston       |
| 5  | David Wilson   | 36  | San Francisco |
+----+----------------+-----+---------------+

In the result set, the order of columns is the same as the order of their specification by the select expressions. If a select expression returns multiple columns, they are ordered the same way they were ordered in the source relation or row-type expression. If column aliases are specified, they override any preexisting column names.

WHERE Clause

The WHERE clause in SQL is used to filter records from a table based on specified conditions. It allows retrieving of only those rows that meet the specified criteria.

Conditions in WHERE clause supported by ShardOne are

Equality operator (=) : Retrieve rows where a column is equal to a specific value. Inequality operators (<, >, <=, >=, <>) : Retrieve rows based on values greater than, less than, greater than or equal to, less than or equal to, or not equal to a specific value. Logical operators (AND, OR, NOT) : Combine multiple conditions to create more complex filtering criteria.

For example,

SELECT Name FROM Users WHERE Age >= 30 AND Age <= 35;

+----------------+
|     Name       |
+----------------+
|   Jane Smith   |
| David Wilson   |
+----------------+

GROUP BY Clause

By using the GROUP BY clause in a SELECT statement, the resulting output can be organized into distinct groups based on matching values. This clause allows for grouping rows together. A GROUP BY clause can consist of an expression formed from input columns.

For example,

SELECT Age, COUNT(Age) FROM Users GROUP BY Age

+-----+----------+
| Age | COUNT(Age) |
+-----+----------+
| 32  | 2        |
| 28  | 1        |
| 36  | 1        |
| 40  | 1        |
+-----+----------+

Aggregation Functions

Similar to the usage of COUNT in GROUP BY, ShardOne supports various other aggregate functions. Below is the comprehensive list of available functions:

COUNT - Returns the number of rows or non-null values in a column.

SUM - Calculates the sum of values in a numeric column.

AVG - Computes the average (mean) of values in a numeric column.

MIN - Retrieves the minimum value from a column.

MAX - Retrieves the maximum value from a column.

For example,

SELECT AVG(Age) from Users

+----------+
| AVG(Age) |
+----------+
|   32.2   |
+----------+

HAVING Clause

When working with aggregate functions and the GROUP BY clause, the HAVING clause comes into play to determine the selected groups. Its purpose is to filter out groups that do not meet specific conditions. By using the HAVING clause, groups can be eliminated based on the given criteria. It is important to note that the HAVING clause operates on groups after the groups and aggregates have been calculated.

For example,

SELECT Age, COUNT(Age) as Age_Count
FROM Users
GROUP BY Age
HAVING Age_Count > 1

+-----+-----------+
| Age | Age_Count |
+-----+-----------+
|  32 |    2      |
+-----+-----------+

ORDER BY Clause

The ORDER BY clause is used to sort a result set by one or more output expressions:

ORDER BY expression [ ASC | DESC ] [, ...]

For Example,

SELECT Age, COUNT(Age)
FROM Users
GROUP BY Age
ORDER BY COUNT(AGE) DESC, Age ASC

+-----+------------+
| Age | COUNT(Age) |
+-----+------------+
|  32 |     2      |
|  28 |     1      |
|  36 |     1      |
|  40 |     1      |
+-----+------------+

OFFSET Clause

The OFFSET clause is used to discard a number of leading rows from the result set:

OFFSET count

If the ORDER BY clause is present, the OFFSET clause is evaluated over a sorted result set, and the set remains sorted after the leading rows are discarded.

For example,

SELECT Name, Age
FROM Users
ORDER BY Age
OFFSET 2

+----------------+-----+
| Name           | Age |
+----------------+-----+
| Jane Smith     | 32  |
| David Wilson   | 36  |
| Mike Johnson   | 40  |
+----------------+-----+

LIMIT Clause

The LIMIT clause restricts the number of rows in the result set.

LIMIT count

For example,

SELECT * FROM Users LIMIT 3

+----+----------------+-----+---------------+
| ID | Name           | Age | City          |
+----+----------------+-----+---------------+
| 1  | John Doe       | 32  | New York      |
| 2  | Jane Smith     | 32  | Los Angeles   |
| 3  | Mike Johnson   | 40  | Chicago       |
+----+----------------+-----+---------------+

JOIN Clause

A JOIN clause is used in SQL to combine rows from two or more tables based on a related column between them.

To discuss different types of JOIN let’s define 2 tables with which understanding each operation will be more easy.

Table: Customers

+----+----------------+-----+---------------+
| ID | Name           | Age | City          |
+----+----------------+-----+---------------+
| 1  | John Doe       | 32  | New York      |
| 2  | Jane Smith     | 32  | Los Angeles   |
| 3  | Mike Johnson   | 40  | Chicago       |
| 4  | Sarah Thompson | 28  | Houston       |
| 5  | David Wilson   | 36  | San Francisco |
+----+----------------+-----+---------------+

Table: Orders

+----+---------+-------+
| ID | OrderID | Price |
+----+---------+-------+
| 1  | 1001    | 50    |
| 2  | 1002    | 75    |
| 3  | 1003    | 60    |
| 4  | 1004    | 45    |
| 6  | 1005    | 55    |
+----+---------+-------+

ShardOne supports the following types of JOIN:

JOIN/INNER JOIN

Retrieves the matching records between two tables based on a specified condition. Only the rows with matching values in both tables are returned.

For example,

SELECT * FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.ID

+----+----------------+-----+---------------+----+---------+-------+
| ID | Name           | Age | City          | ID | OrderID | Price |
+----+----------------+-----+---------------+----+---------+-------+
| 1  | John Doe       | 32  | New York      | 1  | 1001    | 50    |
| 2  | Jane Smith     | 32  | Los Angeles   | 2  | 1002    | 75    |
| 3  | Mike Johnson   | 40  | Chicago       | 3  | 1003    | 60    |
| 4  | Sarah Thompson | 28  | Houston       | 4  | 1004    | 45    |
+----+----------------+-----+---------------+----+---------+-------+

LEFT JOIN/LEFT OUTER JOIN

Retrieves all records from the left table and the matching records from the right table based on a specified condition. If there are no matches in the right table, NULL values are returned.

For example,

SELECT * FROM Customers 
LEFT OUTER JOIN Orders 
ON Customers.ID = Orders.ID

+----+----------------+-----+---------------+----+---------+-------+
| ID | Name           | Age | City          | ID | OrderID | Price |
+----+----------------+-----+---------------+----+---------+-------+
| 1  | John Doe       | 32  | New York      | 1  | 1001    | 50    |
| 2  | Jane Smith     | 32  | Los Angeles   | 2  | 1002    | 75    |
| 3  | Mike Johnson   | 40  | Chicago       | 3  | 1003    | 60    |
| 4  | Sarah Thompson | 28  | Houston       | 4  | 1004    | 45    |
| 5  | David Wilson   | 36  | San Francisco | NULL | NULL   | NULL  |
+----+----------------+-----+---------------+----+---------+-------+

RIGHT JOIN/RIGHT OUTER JOIN

Retrieves all records from the right table and the matching records from the left table based on a specified condition. If there are no matches in the left table, NULL values are returned.

For example,

SELECT * FROM Customers 
RIGHT OUTER JOIN Orders 
ON Customers.ID = Orders.ID

+----+----------------+-----+---------------+----+---------+-------+
| ID | Name           | Age | City          | ID | OrderID | Price |
+----+----------------+-----+---------------+----+---------+-------+
| 1  | John Doe       | 32  | New York      | 1  | 1001    | 50    |
| 2  | Jane Smith     | 32  | Los Angeles   | 2  | 1002    | 75    |
| 3  | Mike Johnson   | 40  | Chicago       | 3  | 1003    | 60    |
| 4  | Sarah Thompson | 28  | Houston       | 4  | 1004    | 45    |
|NULL| NULL           | NULL| NULL          | 6  | 1005    | 55    |
+----+----------------+-----+---------------+----+---------+-------+

NATURAL JOIN

It is a specific type of join in SQL that combines two tables based on matching column names. It automatically matches the columns with the same names and datatypes in both tables and returns the resulting rows.

For example,

SELECT * FROM Customers
NATURAL JOIN Orders

+----+----------------+-----+---------------+---------+-------+
| ID | Name           | Age | City          | OrderID | Price |
+----+----------------+-----+---------------+---------+-------+
| 1  | John Doe       | 32  | New York      | 1001    | 50    |
| 2  | Jane Smith     | 32  | Los Angeles   | 1002    | 75    |
| 3  | Mike Johnson   | 40  | Chicago       | 1003    | 60    |
| 4  | Sarah Thompson | 28  | Houston       | 1004    | 45    |
+----+----------------+-----+---------------+---------+-------+

JOIN with USING Clause

USING Clause is used to match two tables when they both have a column with the same name.

For example,

SELECT * FROM Customers
JOIN Orders USING (ID);

+----+----------------+-----+---------------+---------+-------+
| ID | Name           | Age | City          | OrderID | Price |
+----+----------------+-----+---------------+---------+-------+
| 1  | John Doe       | 32  | New York      | 1001    | 50    |
| 2  | Jane Smith     | 32  | Los Angeles   | 1002    | 75    |
| 3  | Mike Johnson   | 40  | Chicago       | 1003    | 60    |
| 4  | Sarah Thompson | 28  | Houston       | 1004    | 45    |
+----+----------------+-----+---------------+---------+-------+