SQL commands

This list includes commands currently supported by Network Reporting.

group by

Groups together rows in a table that share the same values in the specified columns.

GROUP BY expression [, ...]

SELECT hco_type__v, COUNT(*) FROM hco GROUP BY hco_type__v;

inner join (or just JOIN)

For each row in the first table in the expression, the joined table includes a row for each row in the second table that satisfies the join condition.

table1 INNER JOIN table2 [ ON table1_column = table2_column ]

SELECT formatted_name__v, hcp_type__v, address_line_1__v, locality__v, administrative_area__v, country__v, postal_pre__v FROM hcp INNER JOIN address ON hcp.vid__v = address.entity_vid__v

left (outer) join

After an inner join, a left join adds rows containing null values to the second table in the join, for each row that does not satisfy the join condition.

table1 LEFT [OUTER] JOIN table2 [ ON table1_column = table2_column ]

SELECT vid__v FROM hcp LEFT OUTER JOIN license ON hcp.vid__v = license.entity_vid__v WHERE license.entity_vid__v IS NULL

order by

Specifies the sort order of a query result.

SELECT column FROM table ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]

SELECT vid__v, hcp_type__v FROM hcp ORDER BY hcp_type__v

right (outer) join

After an inner join, a right join adds rows containing null values to the first table in the join, for each row that does not satisfy the join condition.

table1 RIGHT [OUTER] JOIN table2 [ ON table1_column = table2_column ]

SELECT vid__v FROM license RIGHT OUTER JOIN hcp ON hcp.vid__v = license.entity_vid__v WHERE license.entity_vid__v IS NULL

select

Returns rows from tables, views, and user-defined functions.

SELECT [ TOP number | [ ALL | DISTINCT ] * | expression [ AS output_name ] [, ...] ] [ FROM table_reference [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition ] [ { UNION | ALL | INTERSECT | EXCEPT } query ] [ ORDER BY expression [ ASC | DESC ] [ LIMIT { number | ALL } ] [ OFFSET start ]

SELECT vid__v FROM hco WHERE hco_type__v = '11_1'

union

Compares and merges the results of two separate query expressions. The UNION ALL operator is used when duplicate rows (if any) must be retained in the result.

query1 UNION [ALL] query2

SELECT vid__v FROM hcp UNION SELECT vid__v FROM hco