Query Operators Reference

This section provides a comprehensive reference for the query operators available in our system. These operators allow you to efficiently retrieve, filter, and manipulate data from various sources.

Overview

Query operators are powerful tools for data access. They provide a declarative way to express complex data retrieval logic. Understanding these operators is crucial for building performant and scalable applications.

Commonly Used Operators

Here's a table summarizing some of the most frequently used query operators:

Operator	Description	Syntax Example
`SELECT`	Retrieves a set of values from a data source.	`SELECT Name, Age FROM Users`
`WHERE`	Filters data based on specified conditions.	`SELECT * FROM Products WHERE Price > 100`
`ORDER BY`	Sorts the result set in ascending or descending order.	`SELECT Name FROM Customers ORDER BY LastName ASC`
`GROUP BY`	Groups rows that have the same values in specified columns.	`SELECT COUNT(ID), Category FROM Items GROUP BY Category`
`JOIN`	Combines rows from two or more tables based on a related column.	`SELECT Orders.OrderID, Customers.CustomerName FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID`
`FILTER`	A more general-purpose filtering operator, often used in conjunction with other operations.	`FILTER Items WHERE IsActive = true`
`MAP`	Transforms each element of a collection.	`MAP Users TO User.FullName`

Detailed Operator Descriptions

`SELECT`

The SELECT operator is used to project specific columns or computed values from a data source. It forms the basis of most data retrieval queries.

Syntax:

SELECT column1, column2, ...
FROM data_source

You can also use expressions and aliases:

SELECT Name AS CustomerName, Price * 1.1 AS PriceWithTax
FROM Products

`WHERE`

The WHERE operator allows you to filter records based on one or more conditions. Only records that satisfy the condition are returned.

Syntax:

WHERE condition

Conditions can involve comparisons (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), and pattern matching (LIKE).

WHERE Age > 18 AND City = 'New York'

WHERE Description LIKE '%important%'

`ORDER BY`

Use ORDER BY to sort the query results. You can specify one or more columns to sort by, and whether the order should be ascending (ASC, default) or descending (DESC).

Syntax:

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

ORDER BY DateCreated DESC

ORDER BY Category ASC, Price DESC

`GROUP BY`

The GROUP BY operator is used to group rows that have the same values in one or more columns into a summary row. It's often used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Syntax:

GROUP BY column1, column2, ...

Typically used with SELECT and aggregate functions:

SELECT Category, COUNT(ID) AS NumberOfItems
FROM Products
GROUP BY Category

`JOIN`

JOIN clauses are used to combine rows from two or more tables based on a related column between them. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Syntax (INNER JOIN example):

FROM table1
JOIN table2 ON table1.column = table2.column

SELECT u.Username, o.OrderDate
FROM Users u
INNER JOIN Orders o ON u.UserID = o.UserID

`FILTER`

FILTER is a versatile operator that can be used for conditional selection of data. It's often more general than WHERE and can be used in various contexts.

Syntax:

FILTER expression WHERE condition

Example:

FILTER Users WHERE IsAdmin = true

`MAP`

The MAP operator applies a transformation function to each element in a collection or data set, producing a new collection of transformed elements.

Syntax:

MAP collection TO transformation_function

Example:

MAP Products TO p.Name

Example with a lambda:

MAP Users TO user => { return { id: user.UserID, fullName: user.FirstName + ' ' + user.LastName }; }

Advanced Concepts

Beyond these fundamental operators, explore advanced topics such as subqueries, window functions, and the use of operators within stored procedures and triggers for more complex data manipulation scenarios.