# Redis ### SELECT Statements The Provider for Redis is SQL-92 compliant. Below are some example SELECT statements. 1. Return all columns:
| `SELECT * FROM Customers` | | ------------------------- | 2. Rename a column:
| `SELECT [CompanyName] AS MY_CompanyName FROM Customers` | | ------------------------------------------------------- | 3. Search data:
| `SELECT * FROM Customers WHERE Country = 'US';` | | ----------------------------------------------- | 4. Return the number of items in a group:
| `SELECT COUNT(*) AS MyCount FROM Customers` | | ------------------------------------------- | 5. Return the number of unique items in a group:
| `SELECT COUNT(DISTINCT CompanyName) FROM Customers` | | --------------------------------------------------- | 6. Summarize data:
| `SELECT CompanyName, MAX(Balance) FROM Customers GROUP BY CompanyName` | | ---------------------------------------------------------------------- | 7. Retrieve data from multiple tables.
| `SELECT Restaurants.name, Zips.city FROM Restaurants INNER JOIN Zips ON Restaurants.zipcode = Zips.id` | | ------------------------------------------------------------------------------------------------------ | See JOIN Queries below for details. 8. Sort a result set in ascending order:
| `SELECT City, CompanyName FROM Customers ORDER BY CompanyName ASC` | | ------------------------------------------------------------------ | 9. Restrict a result set to the specified number of rows:
| `SELECT City, CompanyName FROM Customers LIMIT 10` | | -------------------------------------------------- | 10. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
| `SELECT * FROM Customers WHERE Country = @param` | | ------------------------------------------------ | ### Aggregate Functions The provider supports SQL-92 summary functions. #### COUNT Returns the number of rows matching the query criteria.
| `SELECT COUNT(*) FROM Customers WHERE Country = US` | | --------------------------------------------------- | #### COUNT\_DISTINCT Returns the number of distinct, non-null field values matching the query criteria.
| `SELECT COUNT_DISTINCT(City) AS DistinctValues FROM Customers` | | -------------------------------------------------------------- | #### AVG Returns the average of the column values.
| `SELECT CompanyName, AVG(Balance) FROM Customers GROUP BY CompanyName` | | ----------------------------------------------------------------------- | #### MIN Returns the minimum column value.
| `SELECT MIN(Balance), CompanyName FROM Customers GROUP BY CompanyName` | | ---------------------------------------------------------------------- | #### MAX Returns the maximum column value.
| `SELECT CompanyName, MAX(Balance) FROM Customers GROUP BY CompanyName` | | ---------------------------------------------------------------------- | #### SUM Returns the total sum of the column values.
| `SELECT SUM(Balance) FROM Customers WHERE Country = US` | | ------------------------------------------------------- | #### JOIN Queries The Provider for Redis supports joins of multiple tables. #### Joining Multiple Tables You can join multiple tables just like you would in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use two tables: Restaurants and Zips. The query below returns the Restaurant records that exist, if any, for each ZIP code:
| `SELECT z.city, r.name, r.borough, r.cuisine, r.zipcodeFROM Zips zLEFT JOIN Restaurants rON r.zipcode = z._id` | | -------------------------------------------------------------------------------------------------------------- | The query below returns records from both tables that match the join condition:
| `SELECT z.city, r.name, r.borough, r.cuisine, r.zipcodeFROM Restaurants rINNER JOIN Zips zON r.zipcode = z._id` | | --------------------------------------------------------------------------------------------------------------- | ### Date Literal Functions The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not. #### L\_TODAY() The current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` | | ----------------------------------------------------- | #### L\_YESTERDAY() The previous day.
| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` | | --------------------------------------------------------- | #### L\_TOMORROW() The following day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` | | -------------------------------------------------------- | #### L\_LAST\_WEEK() Every day in the preceding week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` | | --------------------------------------------------------- | #### L\_THIS\_WEEK() Every day in the current week.
| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` | | --------------------------------------------------------- | #### L\_NEXT\_WEEK() Every day in the following week.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` | | --------------------------------------------------------- | Also available: * L\_LAST/L\_THIS/L\_NEXT MONTH * L\_LAST/L\_THIS/L\_NEXT QUARTER * L\_LAST/L\_THIS/L\_NEXT YEAR #### L\_LAST\_N\_DAYS(n) The previous n days, excluding the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` | | ------------------------------------------------------------ | #### L\_NEXT\_N\_DAYS(n) The following n days, including the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)` | | ------------------------------------------------------------ | Also available: * L\_LAST/L\_NEXT\_90\_DAYS #### L\_LAST\_N\_WEEKS(n) Every day in every week, starting n weeks before current week, and ending in the previous week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)` | | ------------------------------------------------------------- | #### L\_NEXT\_N\_WEEKS(n) Every day in every week, starting the following week, and ending n weeks in the future.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)` | | ------------------------------------------------------------- | Also available: * L\_LAST/L\_NEXT\_N\_MONTHS(n) * L\_LAST/L\_NEXT\_N\_QUARTERS(n) * L\_LAST/L\_NEXT\_N\_YEARS(n)