# Youtube 2020 ### SELECT Statements A SELECT statement can consist of the following basic clauses. * SELECT * INTO * FROM * JOIN * WHERE * GROUP BY * HAVING * UNION * ORDER BY * LIMIT ### SELECT Syntax The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
\|

SELECT {

\[ TOP \ | DISTINCT ]

{

*

| {

\ \[ \[ AS ] \ ]

| { \ | \ } .*

} \[ , ... ]

}

\[ INTO csv:// \[ filename= ] \ \[ ;delimiter=tab ] ]

{

FROM \ \[ \[ AS ] \ ]

} \[ , ... ]

\[ \[

INNER | { { LEFT | RIGHT | FULL } \[ OUTER ] }

] JOIN \ \[ ON \ ] \[ \[ AS ] \ ]

] \[ ... ]

\[ WHERE \ ]

\[ GROUP BY \ \[ , ... ]

\[ HAVING \ ]

\[ UNION \[ ALL ] \ ]

\[

ORDER BY

\ \[ ASC | DESC ] \[ NULLS FIRST | NULLS LAST ]

]

\[

LIMIT \

\[

{ OFFSET | , }

\

]

]

} | SCOPE\_IDENTITY()

\ ::=

| \

| @ \

| ?

| COUNT( \* | { \[ DISTINCT ] \ } )

| { AVG | MAX | MIN | SUM | COUNT } ( \ )

| NULLIF ( \ , \ )

| COALESCE ( \ , ... )

| CASE \

WHEN { \ | \ } THEN { \ | NULL } \[ ... ]

\[ ELSE { \ | NULL } ]

END

| \

| \

\ ::=

{

\ { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } \[ \ ]

} \[ { AND | OR } ... ]

| \| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | #### Examples 1. Return all columns:
| `SELECT * FROM Videos` | | ---------------------- | 2. Rename a column:
| `SELECT [ViewCount] AS MY_ViewCount FROM Videos` | | ------------------------------------------------ | 3. Cast a column's data as a different data type:
| `SELECT CAST(ViewCount AS VARCHAR) AS Str_ViewCount FROM Videos` | | ---------------------------------------------------------------- | 4. Search data:
| `SELECT * FROM Videos WHERE Id = 'MyId';` | | ----------------------------------------- | 5. Return the number of items matching the query criteria:
| `SELECT COUNT(*) AS MyCount FROM Videos` | | ---------------------------------------- | 6. Return the number of unique items matching the query criteria:
| `SELECT COUNT(DISTINCT ViewCount) FROM Videos` | | ---------------------------------------------- | 7. Return the unique items matching the query criteria:
| `SELECT DISTINCT ViewCount FROM Videos` | | --------------------------------------- | 8. Summarize data:
| `SELECT ViewCount, MAX(ViewCount) FROM Videos GROUP BY ViewCount` | | ----------------------------------------------------------------- | See Aggregate Functions below for details. 9. Retrieve data from multiple tables.
| `SELECT Channels.Title, Playlists.Title FROM Channels, Playlists WHERE Channels.Id = Playlists.ChannelId` | | --------------------------------------------------------------------------------------------------------- | See JOIN Queries below for details. 10. Sort a result set in ascending order:
| `SELECT Id, ViewCount FROM Videos ORDER BY ViewCount ASC` | | ---------------------------------------------------------- | 11. Restrict a result set to the specified number of rows:
| `SELECT Id, ViewCount FROM Videos LIMIT 10` | | ------------------------------------------- | 12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
| `SELECT * FROM Videos WHERE Id = @param` | | ---------------------------------------- | ### Aggregate Functions #### COUNT Returns the number of rows matching the query criteria.
| `SELECT COUNT(*) FROM Videos WHERE Id = 'MyId'` | | ----------------------------------------------- | #### COUNT(DISTINCT) Returns the number of distinct, non-null field values matching the query criteria.
| `SELECT COUNT(DISTINCT Id) AS DistinctValues FROM Videos WHERE Id = 'MyId'` | | --------------------------------------------------------------------------- | #### AVG Returns the average of the column values.
| `SELECT ViewCount, AVG(ViewCount) FROM Videos WHERE Id = 'MyId'` `GROUP BY ViewCount` | | -------------------------------------------------------------------------------------- | #### MIN Returns the minimum column value.
| `SELECT MIN(ViewCount), ViewCount FROM Videos WHERE Id = 'MyId'` `GROUP BY ViewCount` | | ------------------------------------------------------------------------------------- | #### MAX Returns the maximum column value.
| `SELECT ViewCount, MAX(ViewCount) FROM Videos WHERE Id = 'MyId'` `GROUP BY ViewCount` | | ------------------------------------------------------------------------------------- | #### SUM Returns the total sum of the column values.
| `SELECT SUM(ViewCount) FROM Videos WHERE Id = 'MyId'` | | ----------------------------------------------------- | ### JOIN Queries The Provider for YouTube 2020 supports standard SQL joins like the following examples. #### Inner Join An inner join selects only rows from both tables that match the join condition:
| `SELECT Channels.Title, Playlists.Title FROM Channels, Playlists WHERE Channels.Id = Playlists.ChannelId` | | --------------------------------------------------------------------------------------------------------- | #### Left Join A left join selects all rows in the FROM table and only matching rows in the JOIN table:
| `SELECT Channels.Title, Playlists.Title FROM Channels LEFT OUTER JOIN Playlists ON Channels.Id = Playlists.ChannelId` | | --------------------------------------------------------------------------------------------------------------------- | ### 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. #### TODAY() The current day.
| `SELECT * FROM MyTable WHERE MyDateField = TODAY()` | | --------------------------------------------------- | #### YESTERDAY() The previous day.
| `SELECT * FROM MyTable WHERE MyDateField = YESTERDAY()` | | ------------------------------------------------------- | #### TOMORROW() The following day.
| `SELECT * FROM MyTable WHERE MyDateField = TOMORROW()` | | ------------------------------------------------------ | #### LAST\_WEEK() Every day in the preceding week.
| `SELECT * FROM MyTable WHERE MyDateField = LAST_WEEK()` | | ------------------------------------------------------- | #### THIS\_WEEK() Every day in the current week.
| `SELECT * FROM MyTable WHERE MyDateField = THIS_WEEK()` | | ------------------------------------------------------- | #### NEXT\_WEEK() Every day in the following week.
| `SELECT * FROM MyTable WHERE MyDateField = NEXT_WEEK()` | | ------------------------------------------------------- | Also available: * LAST/THIS/NEXT MONTH * LAST/THIS/NEXT QUARTER * LAST/THIS/NEXT YEAR #### LAST\_N\_DAYS(n) The previous n days, excluding the current day.
| `SELECT * FROM MyTable WHERE MyDateField = LAST_N_DAYS(3)` | | ---------------------------------------------------------- | #### NEXT\_N\_DAYS(n) The following n days, including the current day.
| `SELECT * FROM MyTable WHERE MyDateField = NEXT_N_DAYS(3)` | | ---------------------------------------------------------- | Also available: * LAST/NEXT\_90\_DAYS #### 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 = LAST_N_WEEKS(3)` | | ----------------------------------------------------------- | #### 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 = NEXT_N_WEEKS(3)` | | ----------------------------------------------------------- | Also available: * LAST/NEXT\_N\_MONTHS(n) * LAST/NEXT\_N\_QUARTERS(n) * LAST/NEXT\_N\_YEARS(n)