Query statements scan one or more tables or expressions and return the computed result rows. This topic describes the syntax for SQL queries in BigQuery.
SQL Syntax
query_statement: query_expr query_expr: [ WITH with_query_name AS ( query_expr ) [, ...] ] { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW named_window_expression AS { named_window | ( [ window_definition ] ) } [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
Notation:
- Square brackets "[ ]" indicate optional clauses.
- Parentheses "( )" indicate literal parentheses.
- The vertical bar "|" indicates a logical OR.
- Curly braces "{ }" enclose a set of options.
- A comma followed by an ellipsis within square brackets "[, ... ]" indicates that the preceding item can repeat in a comma-separated list.
Sample tables
The following tables are used to illustrate the behavior of different query clauses in this reference.
Roster Table
The Roster
table includes a list of player names (LastName
) and the
unique ID assigned to their school (SchoolID
). It looks like this:
+-----------------------+
| LastName | SchoolID |
+-----------------------+
| Adams | 50 |
| Buchanan | 52 |
| Coolidge | 52 |
| Davis | 51 |
| Eisenhower | 77 |
+-----------------------+
You can use this WITH
clause to emulate a temporary table name for the
examples in this reference:
WITH Roster AS
(SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
SELECT 'Buchanan', 52 UNION ALL
SELECT 'Coolidge', 52 UNION ALL
SELECT 'Davis', 51 UNION ALL
SELECT 'Eisenhower', 77)
SELECT * FROM Roster
PlayerStats Table
The PlayerStats
table includes a list of player names (LastName
) and the
unique ID assigned to the opponent they played in a given game (OpponentID
)
and the number of points scored by the athlete in that game (PointsScored
).
+----------------------------------------+
| LastName | OpponentID | PointsScored |
+----------------------------------------+
| Adams | 51 | 3 |
| Buchanan | 77 | 0 |
| Coolidge | 77 | 1 |
| Davis | 52 | 4 |
| Eisenhower | 50 | 13 |
+----------------------------------------+
You can use this WITH
clause to emulate a temporary table name for the
examples in this reference:
WITH PlayerStats AS
(SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 77, 0 UNION ALL
SELECT 'Coolidge', 77, 1 UNION ALL
SELECT 'Adams', 52, 4 UNION ALL
SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats
TeamMascot Table
The TeamMascot
table includes a list of unique school IDs (SchoolID
) and the
mascot for that school (Mascot
).
+---------------------+
| SchoolID | Mascot |
+---------------------+
| 50 | Jaguars |
| 51 | Knights |
| 52 | Lakers |
| 53 | Mustangs |
+---------------------+
You can use this WITH
clause to emulate a temporary table name for the
examples in this reference:
WITH TeamMascot AS
(SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
SELECT 51, 'Knights' UNION ALL
SELECT 52, 'Lakers' UNION ALL
SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot
SELECT list
Syntax:
SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...]
The SELECT
list defines the columns that the query will return. Expressions in
the SELECT
list can refer to columns in any of the from_item
s in its
corresponding FROM
clause.
Each item in the SELECT
list is one of:
- *
expression
expression.*
SELECT *
SELECT *
, often referred to as select star, produces one output column for
each column that is visible after executing the full query.
SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
+-------+-----------+
SELECT expression
Items in a SELECT
list can be expressions. These expressions evaluate to a
single value and produce one output column, with an optional explicit alias
.
If the expression does not have an explicit alias, it receives an implicit alias according to the rules for implicit aliases, if possible. Otherwise, the column is anonymous and you cannot refer to it by name elsewhere in the query.
SELECT expression.*
An item in a SELECT
list can also take the form of expression.*
. This
produces one output column for each column or top-level field of expression
.
The expression must either be a table alias or evaluate to a single value of a
data type with fields, such as a STRUCT.
The following query produces one output column for each column in the table
groceries
, aliased as g
.
WITH groceries AS
(SELECT "milk" AS dairy,
"eggs" AS protein,
"bread" AS grain)
SELECT g.*
FROM groceries AS g;
+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk | eggs | bread |
+-------+---------+-------+
More examples:
WITH locations AS
(SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
UNION ALL
SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona |
+---------+------------+
WITH locations AS
(SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
+---------+------------+
SELECT modifiers
You can modify the results returned from a SELECT
query, as follows.
SELECT DISTINCT
A SELECT DISTINCT
statement discards duplicate rows and returns only the
remaining rows. SELECT DISTINCT
cannot return columns of the following types:
- STRUCT
- ARRAY
SELECT * EXCEPT
A SELECT * EXCEPT
statement specifies the names of one or more columns to
exclude from the result. All matching column names are omitted from the output.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
Note: SELECT * EXCEPT
does not exclude columns that do not have names.
SELECT * REPLACE
A SELECT * REPLACE
statement specifies one or more
expression AS identifier
clauses. Each identifier must match a column name
from the SELECT *
statement. In the output column list, the column that
matches the identifier in a REPLACE
clause is replaced by the expression in
that REPLACE
clause.
A SELECT * REPLACE
statement does not change the names or order of columns.
However, it can change the value and the value type.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | widget | 200 |
+----------+-----------+----------+
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
+----------+-----------+----------+
Note: SELECT * REPLACE
does not replace columns that do not have names.

SELECT ALL
is the default behavior of SELECT
.
Value tables
In BigQuery, a value table is a table where the row type is a single value. In a regular table, each row is made up of columns, each of which has a name and a type. In a value table, the row type is just a single value, and there are no column names.
Note: In BigQuery, a query can only return a value table with a type of
STRUCT
.
In contexts where a query with exactly one column is expected, a value table query can be used instead. For example, scalar subqueries and array subqueries (see Subqueries) normally require a single-column query, but in BigQuery, they also allow using a value table query.
A query will produce a value table if it uses SELECT AS
, using one of the
syntaxes below:
SELECT AS STRUCT
Syntax:
SELECT AS STRUCT expr1 [struct_field_name1] [,... ]
This produces a value table with a STRUCT row type, where the STRUCT field
names and types match the column names and types produced in the SELECT
list.
Example:
SELECT
ARRAY(SELECT AS STRUCT t.f1, t.f2 WHERE t.f3=true)
FROM
Table t
SELECT AS STRUCT
can be used in a scalar or array subquery to produce a single
STRUCT type grouping multiple values together. Scalar
and array subqueries (see Subqueries) are normally not allowed to
return multiple columns.
SELECT AS VALUE
SELECT AS VALUE
produces a value table from any SELECT
list that
produces exactly one column. Instead of producing an output table with one
column, possibly with a name, the output will be a value table where the row
type is just the value type that was produced in the one SELECT
column. Any
alias the column had will be discarded in the value table.
Example:
SELECT AS VALUE STRUCT(1 a, 2 b) xyz FROM Table;
The query above produces a table with row type STRUCT<a int64, b int64>
.
Aliases
See Using Aliases for information on syntax and visibility for
SELECT
list aliases.
FROM clause
The FROM
clause indicates the table or tables from which to retrieve rows, and
specifies how to join those rows together to produce a single stream of
rows for processing in the rest of the query.
Syntax
from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] }
table_name
The name (optionally qualified) of an existing table.
SELECT * FROM Roster; SELECT * FROM dataset.Roster; SELECT * FROM project.dataset.Roster;
FOR SYSTEM_TIME AS OF
FOR SYSTEM_TIME AS OF
references the historical versions of the table
definition and rows that were current at timestamp_expression
.
Limitations:
The source table in the FROM
clause containing FOR SYSTEM_TIME AS OF
must
not be any of the following:
- An
ARRAY
scan, including a flattened array or the output of theUNNEST
operator. - A common table expression defined by a
WITH
clause.
timestamp_expression
must be a constant expression. It cannot
contain the following:
- Subqueries.
- Correlated references (references to columns of a table that appear at
a higher level of the query statement, such as in the
SELECT
list). - User-defined functions (UDFs).
The value of timestamp_expression
cannot fall into the following ranges:
- After the current timestamp (in the future).
- More than seven (7) days before the current timestamp.
A single query statement cannot reference a single table at more than one point in time, including the current time. That is, a query can reference a table multiple times at the same timestamp, but not the current version and a historical version, or two different historical versions.
Examples:
The following query returns a historical version of the table from one hour ago.
SELECT *
FROM t
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
The following query returns a historical version of the table at an absolute point in time.
SELECT *
FROM t
FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';
The following query returns an error because the timestamp_expression
contains
a correlated reference to a column in the containing query.
SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);
The following operations show accessing a historical version of the table before table is replaced.
DECLARE before_replace_timestamp TIMESTAMP;
-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;
-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();
-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;
SELECT * FROM books;
-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;
The following operations show accessing a historical version of the table before a DML job.
DECLARE JOB_START_TIMESTAMP TIMESTAMP;
-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;
-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
('War and Peace', 'Leo Tolstoy');
SELECT * FROM books;
SET JOB_START_TIMESTAMP = (
SELECT start_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE job_type="QUERY"
AND statement_type="INSERT"
ORDER BY start_time DESC
LIMIT 1
);
-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;
join
See JOIN Types below.
select
( select ) [ [ AS ] alias ]
is a table subquery.
field_path
In the FROM
clause, field_path
is any path that
resolves to a field within a data type. field_path
can go
arbitrarily deep into a nested data structure.
 In
addition, field paths cannot contain arrays before the end of the path. For example, the path
array_column.some_array.some_array_field
is invalid because it
contains an array before the end of the path.
Note: If a path has only one name, it is interpreted as a table.
To work around this, wrap the path using UNNEST
, or use the
fully-qualified path.
Note: If a path has more than one name, and it matches a field
name, it is interpreted as a field name. To force the path to be interpreted as
a table name, wrap the path using `
.
UNNEST
The UNNEST
operator takes an ARRAY
and returns a
table, with one row for each element in the ARRAY
.
You can also use UNNEST
outside of the FROM
clause with the
IN
operator.
For input ARRAY
s of most element types, the output of UNNEST
generally has one
column. This single column has an optional alias
, which you can use to refer
to the column elsewhere in the query. ARRAYS
with these element types return
multiple columns:
- STRUCT
UNNEST
destroys the order of elements in the input
ARRAY
. Use the optional WITH OFFSET
clause to
return a second column with the array element indexes (see below).
For an input ARRAY
of STRUCT
s, UNNEST
returns a row for each STRUCT
, with a separate column for each field in the
STRUCT
. The alias for each column is the name of the corresponding STRUCT
field.
Example
SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);
+---+-----+
| x | y |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+
Because the UNNEST
operator returns a
value table,
you can alias UNNEST
to define a range variable that you can reference
elsewhere in the query. If you reference the range variable in the SELECT
list, the query returns a STRUCT
containing all of the fields of the original
STRUCT
in the input table.
Example
SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
AS struct_value;
+---+-----+--------------+
| x | y | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar} |
| 1 | foo | {1, foo} |
+---+-----+--------------+
ARRAY unnesting can be either explicit or implicit. In explicit unnesting,
array_expression
must return an ARRAY value but does not need to resolve
to an ARRAY, and the UNNEST
keyword is required.
Example:
SELECT * FROM UNNEST ([1, 2, 3]);
In implicit unnesting, array_path
must resolve to an ARRAY and the
UNNEST
keyword is optional.
Example:
SELECT x FROM mytable AS t, t.struct_typed_column.array_typed_field1 AS x;
In this scenario, array_path
can go arbitrarily deep into a data
structure, but the last field must be ARRAY-typed. No previous field in the
expression can be ARRAY-typed because it is not possible to extract a named
field from an ARRAY.
UNNEST
treats NULLs as follows:
- NULL and empty ARRAYs produces zero rows.
- An ARRAY containing NULLs produces rows containing NULL values.
The optional WITH
OFFSET
clause returns a separate
column containing the "offset" value (i.e. counting starts at zero) for each row
produced by the UNNEST
operation. This column has an optional
alias
; the default alias is offset.
Example:
SELECT * FROM UNNEST ( ) WITH OFFSET AS num;
See the Arrays topic
for more ways to use UNNEST
, including construction, flattening, and
filtering.
with_query_name
The query names in a WITH
clause (see WITH Clause) act like names of temporary tables that you
can reference anywhere in the FROM
clause. In the example below,
subQ1
and subQ2
are with_query_names
.
Example:
WITH subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52), subQ2 AS (SELECT SchoolID FROM subQ1) SELECT DISTINCT * FROM subQ2;
The WITH
clause hides any permanent tables with the same name
for the duration of the query, unless you qualify the table name, e.g.
dataset.Roster
or project.dataset.Roster
.
Subqueries
A subquery is a query that appears inside another statement, and is written
inside parentheses. These are also referred to as "sub-SELECTs" or
"nested SELECTs". The full SELECT
syntax is valid in
subqueries.
There are two types of subquery:
- Expression Subqueries, which you can use in a query wherever expressions are valid. Expression subqueries return a single value.
- Table subqueries, which you can use only in a
FROM
clause. The outer query treats the result of the subquery as a table.
Note that there must be parentheses around both types of subqueries.
Example:
SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
FROM Stats
WHERE SchoolID = 77 )
Optionally, a table subquery can have an alias.
Example:
SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;
Aliases
See Using Aliases for information on syntax and visibility for
FROM
clause aliases.
JOIN types
Syntax
join: from_item [ join_type ] JOIN from_item [ ON bool_expression | USING ( join_column [, ...] ) ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
The JOIN
clause merges two from_item
s so that the SELECT
clause can
query them as one source. The join_type
and ON
or USING
clause (a
"join condition") specify how to combine and discard rows from the two
from_item
s to form a single source.
All JOIN
clauses require a join_type
.
A JOIN
clause requires a join condition unless one of the following conditions
is true:
join_type
isCROSS
.- One or both of the
from_item
s is not a table, e.g. anarray_path
orfield_path
.
[INNER] JOIN
An INNER JOIN
, or simply JOIN
, effectively calculates the Cartesian product
of the two from_item
s and discards all rows that do not meet the join
condition. "Effectively" means that it is possible to implement an INNER JOIN
without actually calculating the Cartesian product.
CROSS JOIN
CROSS JOIN
returns the Cartesian product of the two from_item
s. In other
words, it combines each row from the first from_item
with each row from the
second from_item
. If there are M rows from the first and N rows from the
second, the result is M * N rows. Note that if either from_item
has zero
rows, the result is zero rows.
Comma cross joins
CROSS JOIN
s can be written explicitly (see directly above) or implicitly using
a comma to separate the from_item
s.
Example of an implicit "comma cross join":
SELECT * FROM Roster, TeamMascot;
Here is the explicit cross join equivalent:
SELECT * FROM Roster CROSS JOIN TeamMascot;
You cannot write comma cross joins inside parentheses.
Invalid - comma cross join inside parentheses:
SELECT * FROM t CROSS JOIN (Roster, TeamMascot); // INVALID.
See Sequences of JOINs for details on how a comma cross join behaves in a sequence of JOINs.
FULL [OUTER] JOIN
A FULL OUTER JOIN
(or simply FULL JOIN
) returns all fields for all rows in
both from_item
s that meet the join condition.
FULL
indicates that all rows from both from_item
s are
returned, even if they do not meet the join condition.
OUTER
indicates that if a given row from one from_item
does not
join to any row in the other from_item
, the row will return with NULLs
for all columns from the other from_item
.
LEFT [OUTER] JOIN
The result of a LEFT OUTER JOIN
(or simply LEFT JOIN
) for two
from_item
s always retains all rows of the left from_item
in the
JOIN
clause, even if no rows in the right from_item
satisfy the join
predicate.
LEFT
indicates that all rows from the left from_item
are
returned; if a given row from the left from_item
does not join to any row
in the right from_item
, the row will return with NULLs for all
columns from the right from_item
. Rows from the right from_item
that
do not join to any row in the left from_item
are discarded.
RIGHT [OUTER] JOIN
The result of a RIGHT OUTER JOIN
(or simply RIGHT JOIN
) is similar and
symmetric to that of LEFT OUTER JOIN
.
ON clause
The ON
clause contains a bool_expression
. A combined row (the result of
joining two rows) meets the join condition if bool_expression
returns
TRUE.
Example:
SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
USING clause
The USING
clause requires a column_list
of one or more columns which
occur in both input tables. It performs an equality comparison on that column,
and the rows meet the join condition if the equality comparison returns TRUE.
For example, consider this statement which performs an INNER JOIN
on the
Roster
and TeamMascot
table:
SELECT * FROM Roster INNER JOIN TeamMascot
USING (SchoolID);
+----------------------------------------+
| SchoolID | LastName | Mascot |
+----------------------------------------+
| 50 | Adams | Jaguars |
| 52 | Buchanan | Lakers |
| 52 | Coolidge | Lakers |
| 51 | Davis | Knights |
+----------------------------------------+
This statement returns the rows from Roster
and TeamMascot
where
Roster.SchooldID
is the same as TeamMascot.SchooldID
. The results include
a single SchooldID
column.
In most cases, a statement with the USING
keyword is equivalent to using the
ON
keyword. For example, the statement which performs an INNER JOIN
on
the Roster
and PlayerStats
table:
SELECT LastName
FROM Roster INNER JOIN PlayerStats
USING (LastName);
is equivalent to:
SELECT Roster.LastName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
Sequences of JOINs
The FROM
clause can contain multiple JOIN
clauses in sequence.
Example:
SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;
where a
, b
, and c
are any from_item
s. JOINs are bound from left to
right, but you can insert parentheses to group them in a different order.
Consider the following queries: A (without parentheses) and B (with parentheses)
are equivalent to each other but not to C. The FULL JOIN
in bold binds
first.
A.
SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID) FULL JOIN PlayerStats USING (LastName);
B.
SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID)) FULL JOIN PlayerStats USING (LastName));
C.
SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING (LastName)) USING (SchoolID)) ;
When comma cross joins are present in a query with a sequence of JOINs, they
group from left to right like other JOIN
types.
Example:
SELECT * FROM a JOIN b ON TRUE, b JOIN c ON TRUE;
The query above is equivalent to
SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);
There cannot be a RIGHT JOIN
or FULL JOIN
after a comma join.
Invalid - RIGHT JOIN
after a comma cross join:
SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE; // INVALID.
WHERE clause
Syntax
WHERE bool_expression
The WHERE
clause filters out rows by evaluating each row against
bool_expression
, and discards all rows that do not return TRUE (that is,
rows that return FALSE or NULL).
Example:
SELECT * FROM Roster
WHERE SchoolID = 52;
The bool_expression
can contain multiple sub-conditions.
Example:
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
You cannot reference column aliases from the SELECT
list in the WHERE
clause.
Expressions in an INNER JOIN
have an equivalent expression in the
WHERE
clause. For example, a query using INNER
JOIN
and ON
has an
equivalent expression using CROSS JOIN
and WHERE
.
Example - this query:
SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
is equivalent to:
SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
GROUP BY clause
Syntax
GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }
The GROUP BY
clause groups together rows in a table with non-distinct values
for the expression
in the GROUP BY
clause. For multiple rows in the
source table with non-distinct values for expression
, the
GROUP BY
clause produces a single combined row. GROUP BY
is commonly used
when aggregate functions are present in the SELECT
list, or to eliminate
redundancy in the output. The data type of expression
must be groupable.
Example:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;
The GROUP BY
clause can refer to expression names in the SELECT
list. The
GROUP BY
clause also allows ordinal references to expressions in the SELECT
list using integer values. 1
refers to the first expression in the
SELECT
list, 2
the second, and so forth. The expression list can combine
ordinals and expression names.
Example:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;
The query above is equivalent to:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;
GROUP BY
clauses may also refer to aliases. If a query contains aliases in
the SELECT
clause, those aliases override names in the corresponding FROM
clause.
Example:
SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;
GROUP BY ROLLUP
returns the results of GROUP BY
for
prefixes of the expressions in the ROLLUP
list, each of which is known as a
grouping set. For the ROLLUP
list (a, b, c)
, the grouping sets are
(a, b, c)
, (a, b)
, (a)
, ()
. When evaluating the results of GROUP BY
for a particular grouping set, GROUP BY ROLLUP
treats expressions that are not
in the grouping set as having a NULL
value. A SELECT
statement like this
one:
SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);
uses the rollup list (a, b)
. The result will include the
results of GROUP BY
for the grouping sets (a, b)
, (a)
, and ()
, which
includes all rows.
Example:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);
The query above outputs a row for each day in addition to the rolled up total
across all days, as indicated by a NULL
day:
+------+-------+
| day | total |
+------+-------+
| NULL | 39.77 |
| 1 | 23.54 |
| 2 | 9.99 |
| 3 | 6.24 |
+------+-------+
Example:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;
The query above returns rows grouped by the following grouping sets:
- sku and day
- sku (day is
NULL
) - The empty grouping set (day and sku are
NULL
)
The sums for these grouping sets correspond to the total for each distinct sku-day combination, the total for each sku across all days, and the grand total:
+------+------+-------+
| sku | day | total |
+------+------+-------+
| NULL | NULL | 39.77 |
| 123 | NULL | 28.97 |
| 123 | 1 | 18.98 |
| 123 | 2 | 9.99 |
| 456 | NULL | 8.81 |
| 456 | 1 | 4.56 |
| 456 | 3 | 4.25 |
| 789 | 3 | 1.99 |
| 789 | NULL | 1.99 |
+------+------+-------+
HAVING clause
Syntax
HAVING bool_expression
The HAVING
clause is similar to the WHERE
clause: it filters out rows that
do not return TRUE when they are evaluated against the bool_expression
.
As with the WHERE
clause, the bool_expression
can be any expression
that returns a boolean, and can contain multiple sub-conditions.
The HAVING
clause differs from the WHERE
clause in that:
- The
HAVING
clause requiresGROUP BY
or aggregation to be present in the query. - The
HAVING
clause occurs afterGROUP BY
and aggregation, and beforeORDER BY
. This means that theHAVING
clause is evaluated once for every aggregated row in the result set. This differs from theWHERE
clause, which is evaluated beforeGROUP BY
and aggregation.
The HAVING
clause can reference columns available via the FROM
clause, as
well as SELECT
list aliases. Expressions referenced in the HAVING
clause
must either appear in the GROUP BY
clause or they must be the result of an
aggregate function:
SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
If a query contains aliases in the SELECT
clause, those aliases override names
in a FROM
clause.
SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;
Mandatory aggregation
Aggregation does not have to be present in the HAVING
clause itself, but
aggregation must be present in at least one of the following forms:
Aggregation function in the SELECT
list.
SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;
Aggregation function in the 'HAVING' clause.
SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Aggregation in both the SELECT
list and HAVING
clause.
When aggregation functions are present in both the SELECT
list and HAVING
clause, the aggregation functions and the columns they reference do not need
to be the same. In the example below, the two aggregation functions,
COUNT()
and SUM()
, are different and also use different columns.
SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
ORDER BY clause
Syntax
ORDER BY expression [{ ASC | DESC }] [{ NULLS FIRST | NULLS LAST }] [, ...]
The ORDER BY
clause specifies a column or expression as the sort criterion for
the result set. If an ORDER BY clause is not present, the order of the results
of a query is not defined. Column aliases from a FROM
clause or SELECT
list
are allowed. If a query contains aliases in the SELECT
clause, those aliases
override names in the corresponding FROM
clause.
Optional Clauses
NULLS FIRST | NULLS LAST
:NULLS FIRST
: Sort null values before non-null values.NULLS LAST
. Sort null values after non-null values.
ASC | DESC
: Sort the results in ascending or descending order ofexpression
values.ASC
is the default value. If null ordering is not specified withNULLS FIRST
orNULLS LAST
:NULLS FIRST
is applied by default if the sort order is ascending.NULLS LAST
is applied by default if the sort order is descending.
Examples
Use the default sort order (ascending).
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x;
+------+-------+
| x | y |
+------+-------+
| NULL | false |
| 1 | true |
| 9 | true |
+------+-------+
Use the default sort order (ascending), but return null values last.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x NULLS LAST;
+------+-------+
| x | y |
+------+-------+
| 1 | true |
| 9 | true |
| NULL | false |
+------+-------+
Use descending sort order.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC;
+------+-------+
| x | y |
+------+-------+
| 9 | true |
| 1 | true |
| NULL | false |
+------+-------+
Use descending sort order, but return null values first.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x | y |
+------+-------+
| NULL | false |
| 9 | true |
| 1 | true |
+------+-------+
It is possible to order by multiple columns. In the example below, the result
set is ordered first by SchoolID
and then by LastName
:
SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;
The following rules apply when ordering values:
- NULLs: In the context of the
ORDER BY
clause, NULLs are the minimum possible value; that is, NULLs appear first inASC
sorts and last inDESC
sorts. - Floating point data types: see Floating Point Semantics on ordering and grouping.
When used in conjunction with set operators, the ORDER BY
clause applies to the result set of the entire query; it does not
apply only to the closest SELECT
statement. For this reason, it can be helpful
(though it is not required) to use parentheses to show the scope of the ORDER
BY
.
This query without parentheses:
SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;
is equivalent to this query with parentheses:
( SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot )
ORDER BY SchoolID;
but is not equivalent to this query, where the ORDER BY
clause applies only to
the second SELECT
statement:
SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
ORDER BY SchoolID );
You can also use integer literals as column references in ORDER BY
clauses. An
integer literal becomes an ordinal (for example, counting starts at 1) into
the SELECT
list.
Example - the following two queries are equivalent:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;
WINDOW clause
Syntax
WINDOW named_window_expression [, ...] named_window_expression: named_window AS { named_window | ( [ window_specification ] ) }
A WINDOW
clause defines a list of named windows.
A named window represents a group of rows in a table upon which to use an
analytic function. A named window can be defined with
a window specification or reference another
named window. If another named window is referenced, the definition of the
referenced window must precede the referencing window.
Examples
These examples reference a table called Produce
.
They all return the same result. Note the different
ways you can combine named windows and use them in an analytic function's
OVER
clause.
SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
SELECT item, purchases, category, LAST_VALUE(item)
OVER (d) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
d AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS b
Set operators
Syntax
UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT
Set operators combine results from two or
more input queries into a single result set.  If DISTINCT
is specified, duplicate rows are discarded.
If a given row R appears exactly m times in the first input query and n times in the second input query (m >= 0, n >= 0):
- For
UNION ALL
, R appears exactly m + n times in the result. - For
UNION DISTINCT
, theDISTINCT
is computed after theUNION
is computed, so R appears exactly one time. - For
INTERSECT DISTINCT
, theDISTINCT
is computed after the result above is computed. - For
EXCEPT DISTINCT
, row R appears once in the output if m > 0 and n = 0. - If there are more than two input queries, the above operations generalize and the output is the same as if the inputs were combined incrementally from left to right.
The following rules apply:
- For set operations other than
UNION ALL
, all column types must support equality comparison. - The input queries on each side of the operator must return the same number of columns.
- The operators pair the columns returned by each input query according to
the columns' positions in their respective
SELECT
lists. That is, the first column in the first input query is paired with the first column in the second input query. - The result set always uses the column names from the first input query.
- The result set always uses the supertypes of input types in corresponding columns, so paired columns must also have either the same data type or a common supertype.
- You must use parentheses to separate different set operations; for
this purpose, set operations such as
UNION ALL
andUNION DISTINCT
are different. If the statement only repeats the same set operation, parentheses are not necessary.

UNION
The UNION
operator combines the result sets of two or more input queries by
pairing columns from the result set of each query and vertically concatenating
them.
INTERSECT
The INTERSECT
operator returns rows that are found in the result sets of both
the left and right input queries. Unlike EXCEPT
, the positioning of the input
queries (to the left versus right of the INTERSECT
operator) does not matter.
EXCEPT
The EXCEPT
operator returns rows from the left input query that are
not present in the right input query.
Example:
SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;
+--------+
| number |
+--------+
| 2 |
| 3 |
+--------+
LIMIT clause and OFFSET clause
Syntax
LIMIT count [ OFFSET skip_rows ]
LIMIT
specifies a non-negative count
of type INT64,
and no more than count
rows will be returned. LIMIT
0
returns 0 rows.
If there is a set operation, LIMIT
is applied after the set operation is
evaluated.
OFFSET
specifies a non-negative number of rows to skip before applying
LIMIT
. skip_rows
is of type INT64.
These clauses accept only literal or parameter values. The rows that are
returned by LIMIT
and OFFSET
are unspecified unless these
operators are used after ORDER BY
.
Examples:
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2
+---------+
| letter |
+---------+
| a |
| b |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1
+---------+
| letter |
+---------+
| b |
| c |
| d |
+---------+
WITH clause
The WITH
clause contains one or more named subqueries which execute
every time a subsequent SELECT
statement references them. Any clause
or subquery can reference subqueries you define in the WITH
clause. This
includes any SELECT
statements on either side of a set operator,
such as UNION
.
The WITH
clause is useful primarily for readability, because BigQuery does
not materialize the result of the queries inside the WITH
clause. If a query
appears in more than one WITH
clause, it executes in each clause.
Example:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
Another useful role of the WITH
clause is to break up more complex queries
into a WITH
SELECT
statement and WITH
clauses, where the less desirable
alternative is writing nested table subqueries. If a WITH
clause contains
multiple subqueries, the subquery names cannot repeat.
BigQuery supports WITH
clauses in subqueries, such as table
subqueries, expression subqueries, and so on.
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
The following are scoping rules for WITH
clauses:
- Aliases are scoped so that the aliases introduced in a
WITH
clause are visible only in the later subqueries in the sameWITH
clause, and in the query under theWITH
clause. - Aliases introduced in the same
WITH
clause must be unique, but the same alias can be used in multipleWITH
clauses in the same query. The local alias overrides any outer aliases anywhere that the local alias is visible. - Aliased subqueries in a
WITH
clause can never be correlated. No columns from outside the query are visible. The only names from outside that are visible are otherWITH
aliases that were introduced earlier in the sameWITH
clause.

SELECT * FROM q1) # q1 resolves to the third inner WITH subquery.
NOTE: BigQuery does not support WITH RECURSIVE
.
Using Aliases
An alias is a temporary name given to a table, column, or expression present in
a query. You can introduce explicit aliases in the SELECT
list or FROM
clause, or BigQuery will infer an implicit alias for some expressions.
Expressions with neither an explicit nor implicit alias are anonymous and the
query cannot reference them by name.
Explicit alias syntax
You can introduce explicit aliases in either the FROM
clause or the SELECT
list.
In a FROM
clause, you can introduce explicit aliases for any item, including
tables, arrays, subqueries, and UNNEST
clauses, using [AS] alias
. The AS
keyword is optional.
Example:
SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;
You can introduce explicit aliases for any expression in the SELECT
list using
[AS] alias
. The AS
keyword is optional.
Example:
SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;
Explicit alias visibility
After you introduce an explicit alias in a query, there are restrictions on where else in the query you can reference that alias. These restrictions on alias visibility are the result of BigQuery's name scoping rules.
FROM clause aliases
BigQuery processes aliases in a FROM
clause from left to right,
and aliases are visible only to subsequent path expressions in a FROM
clause.
Example:
Assume the Singers
table had a Concerts
column of ARRAY
type.
SELECT FirstName
FROM Singers AS s, s.Concerts;
Invalid:
SELECT FirstName
FROM s.Concerts, Singers AS s; // INVALID.
FROM
clause aliases are not visible to subqueries in the same FROM
clause. Subqueries in a FROM
clause cannot contain correlated references to
other tables in the same FROM
clause.
Invalid:
SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s) // INVALID.
You can use any column name from a table in the FROM
as an alias anywhere in
the query, with or without qualification with the table name.
Example:
SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;
If the FROM
clause contains an explicit alias, you must use the explicit alias
instead of the implicit alias for the remainder of the query (see
Implicit Aliases). A table alias is useful for brevity or
to eliminate ambiguity in cases such as self-joins, where the same table is
scanned multiple times during query processing.
Example:
SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName
Invalid — ORDER BY
does not use the table alias:
SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName; // INVALID.
SELECT list aliases
Aliases in the SELECT
list are visible only to the following clauses:
GROUP BY
clauseORDER BY
clauseHAVING
clause
Example:
SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;
Explicit aliases in GROUP BY, ORDER BY, and HAVING clauses
These three clauses, GROUP BY
, ORDER BY
, and HAVING
, can refer to only the
following values:
- Tables in the
FROM
clause and any of their columns. - Aliases from the
SELECT
list.
GROUP BY
and ORDER BY
can also refer to a third group:
- Integer literals, which refer to items in the
SELECT
list. The integer1
refers to the first item in theSELECT
list,2
refers to the second item, etc.
Example:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;
The query above is equivalent to:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;
Ambiguous aliases
BigQuery provides an error if a name is ambiguous, meaning it can resolve to more than one unique object.
Examples:
This query contains column names that conflict between tables, since both
Singers
and Songs
have a column named SingerID
:
SELECT SingerID
FROM Singers, Songs;
This query contains aliases that are ambiguous in the GROUP BY
clause because
they are duplicated in the SELECT
list:
SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;
This query contains aliases that are ambiguous in the SELECT
list and FROM
clause because they share the same name. Assume table
has columns x
, y
,
and z
. z
is of type STRUCT and has fields
v
, w
, and x
.
Example:
SELECT x, z AS T
FROM table AS T
GROUP BY T.x;
The alias T
is ambiguous and will produce an error because T.x
in the GROUP
BY
clause could refer to either table.x
or table.z.x
.
A name is not ambiguous in GROUP BY
, ORDER BY
or HAVING
if it is both
a column name and a SELECT
list alias, as long as the name resolves to the
same underlying object.
Example:
SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;
The alias BirthYear
is not ambiguous because it resolves to the same
underlying column, Singers.BirthYear
.
Implicit aliases
In the SELECT
list, if there is an expression that does not have an explicit
alias, BigQuery assigns an implicit alias according to the following
rules. There can be multiple columns with the same alias in the SELECT
list.
- For identifiers, the alias is the identifier. For example,
SELECT abc
impliesAS abc
. - For path expressions, the alias is the last identifier in the path. For
example,
SELECT abc.def.ghi
impliesAS ghi
. - For field access using the "dot" member field access operator, the alias is
the field name. For example,
SELECT (struct_function()).fname
impliesAS fname
.
In all other cases, there is no implicit alias, so the column is anonymous and cannot be referenced by name. The data from that column will still be returned and the displayed query results may have a generated label for that column, but the label cannot be used like an alias.
In a FROM
clause, from_item
s are not required to have an alias. The
following rules apply:
- If there is an expression that does not have an explicit alias, BigQuery assigns an implicit alias in these cases:
- For identifiers, the alias is the identifier. For example,
FROM abc
impliesAS abc
. - For path expressions, the alias is the last identifier in the path. For
example,
FROM abc.def.ghi
impliesAS ghi
- The column produced using
WITH OFFSET
has the implicit aliasoffset
. - Table subqueries do not have implicit aliases.
FROM UNNEST(x)
does not have an implicit alias.
Appendix A: examples with sample data
JOIN types
1) [INNER] JOIN
Example:
SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Results:
| LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
|---|---|---|---|
| Adams | 50 | 50 | Jaguars |
| Buchanan | 52 | 52 | Lakers |
| Coolidge | 52 | 52 | Lakers |
| Davis | 51 | 51 | Knights |
2) CROSS JOIN
Example:
SELECT * FROM Roster CROSS JOIN TeamMascot;
Results:
| LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
|---|---|---|---|
| Adams | 50 | 50 | Jaguars |
| Adams | 50 | 51 | Knights |
| Adams | 50 | 52 | Lakers |
| Adams | 50 | 53 | Mustangs |
| Buchanan | 52 | 50 | Jaguars |
| Buchanan | 52 | 51 | Knights |
| Buchanan | 52 | 52 | Lakers |
| Buchanan | 52 | 53 | Mustangs |
| Coolidge | 52 | 50 | Jaguars |
| Coolidge | 52 | 51 | Knights |
| Coolidge | 52 | 52 | Lakers |
| Coolidge | 52 | 53 | Mustangs |
| Davis | 51 | 50 | Jaguars |
| Davis | 51 | 51 | Knights |
| Davis | 51 | 52 | Lakers |
| Davis | 51 | 53 | Mustangs |
| Eisenhower | 77 | 50 | Jaguars |
| Eisenhower | 77 | 51 | Knights |
| Eisenhower | 77 | 52 | Lakers |
| Eisenhower | 77 | 53 | Mustangs |
3) FULL [OUTER] JOIN
Example:
SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
| LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
|---|---|---|---|
| Adams | 50 | 50 | Jaguars |
| Buchanan | 52 | 52 | Lakers |
| Coolidge | 52 | 52 | Lakers |
| Davis | 51 | 51 | Knights |
| Eisenhower | 77 | NULL | NULL |
| NULL | NULL | 53 | Mustangs |
4) LEFT [OUTER] JOIN
Example:
SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Results:
| LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
|---|---|---|---|
| Adams | 50 | 50 | Jaguars |
| Buchanan | 52 | 52 | Lakers |
| Coolidge | 52 | 52 | Lakers |
| Davis | 51 | 51 | Knights |
| Eisenhower | 77 | NULL | NULL |
5) RIGHT [OUTER] JOIN
Example:
SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Results:
| LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
|---|---|---|---|
| Adams | 50 | 50 | Jaguars |
| Davis | 51 | 51 | Knights |
| Coolidge | 52 | 52 | Lakers |
| Buchanan | 52 | 52 | Lakers |
| NULL | NULL | 53 | Mustangs |
GROUP BY clause
Example:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
| LastName | SUM |
|---|---|
| Adams | 7 |
| Buchanan | 13 |
| Coolidge | 1 |
Set operators
UNION
The UNION
operator combines the result sets of two or more SELECT
statements
by pairing columns from the result set of each SELECT
statement and vertically
concatenating them.
Example:
SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;
Results:
| X | Y |
|---|---|
| Jaguars | 50 |
| Knights | 51 |
| Lakers | 52 |
| Mustangs | 53 |
| Adams | 3 |
| Buchanan | 0 |
| Coolidge | 1 |
| Adams | 4 |
| Buchanan | 13 |
INTERSECT
This query returns the last names that are present in both Roster and PlayerStats.
SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;
Results:
| LastName |
|---|
| Adams |
| Coolidge |
| Buchanan |
EXCEPT
The query below returns last names in Roster that are not present in PlayerStats.
SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;
Results:
| LastName |
|---|
| Eisenhower |
| Davis |
Reversing the order of the SELECT
statements will return last names in
PlayerStats that are not present in Roster:
SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;
Results:
(empty)


Home
About Us
Quiz
Feedback
Site Map
SVG Site Map
presentation page
Home page
Shop
comments
discover Srilanka
site map
quiz
About Us
deep sea diving
boat excations
safary
location
christmas
Nallur Festival
Esala Perahera
Corona
Get in touch


Macros
TOC
-
Description
- Macros for various use.
List
-
color_a9f1aad2_e23c_4f7e_9dce_0f1c116f4968
-
- Name
color_a9f1aad2_e23c_4f7e_9dce_0f1c116f4968
- Description
- Header of Color library is included and ready for use.
- Example
-
#ifdef color_a9f1aad2_e23c_4f7e_9dce_0f1c116f4968
typedef ::color::rgb < std::uint8_t > rgb_t;
#else
#error "Missing color library"
#endif
COLOR_USE_PP2FILE
-
- Name
COLOR_USE_PP2FILE
- Description
- Instead to internally use files scattered across
color
folder use only one already preprocessed.
This will speed up compilation process.
- Example
-
#define COLOR_USE_PP2FILE
#include "color/color.hpp"
- See also:
-
How to reduce file count?


Step 1: Better Discord Is Installer !
Step 2: Open Discord
Step 3: Open Discord User Setting
Step 4: Tap To Plugin In Better Discord
Step 5: Click Open Plugin Folder
Step 6: Copy NitroPerks.plugin.js is download!
Step 7: Paste To Plugin Folder


---
layout: archive
title: Lean Prover Zulip Chat Archive
permalink: 113488general/97801IndependenceofstatementfromaxiomsinLean.html
---
The archive URL format has changed. Please find this page at:
https://leanprover-community.github.io/archive/stream/113488-general/topic/Independence.20of.20statement.20from.20axioms.20in.20Lean.html


Application Form for Individual Applicants
Application Form
a. After submitting your application form and within few days, you will receive notification concerning your participation to Thracing on MIO.
b. In case of approval of your application, you will be kindly asked to deposit your participation fee (see below). Please send a scanned copy of the bank transfer receipt via e-mail at: (email). Be sure that in your email and in the receipt your full name is mentioned.
c. Soon after the deposit of your participation fee, you will receive information about your Delegation assignment (Committee & country of representation).
Group Applications
Application Form
Applicants from the enlisted Greek Universities/Department of Studies:
Students currently enrolled in the below mentioned Department of Studies/Universities in Greece shall contact their Academic liaisons (Professors or Head Delegates) in order to express their interest to participate in their respective organised group delegations. Their applications as individual applicants will be ignored.
Applicants from other Greek Universities / Department of Studies:
Applicants from other Greek University Departments (other than those mentioned in the list above) who would like to apply as a group are welcome to do so, taking into consideration that they have to be students of the same Academic Department of Studies. In this case, their contact liaison (Head Delegate) shall express the group’s interest (via e-mail at: mail). Further information and guidance will be provided afterwards.
Applicants from Abroad:
Applicants from abroad who are students at the same University and/or Academic Department of Studies are eligible to apply as a group. In this case, their contact liaison (Head Delegate) shall express the group’s interest (via e-mail at: mail). Further information and guidance will be provided afterwards.
Participation Fee
The Participation Fee is 55€
Early Bird Fee (for everyone who applies from 01/07/2020 to 31/07/2020): 50€
Withdrawal with reimbursement of 25.00€: no later than September 30/09/2020 (only for Individual Applicants) After applying,
you are kindly asked to deposit, to the following bank account:
IBAN:
Account Holder:
Bank:
*In order your application to be considered as valid, please send us a scanned copy of the bank transfer receipt via e-mail at: mail. Please be sure that the copy of your receipt includes your full name.
The participation fee includes:
-The conference bags and badges.
-All the material that will be distributed.
-All the lunches and receptions that are mentioned in the official program.
-All the coffee breaks that are mentioned in the official program.
-Transportation to University Campus.
Not Included:
-Accommodation and travel expenses.
-Any optional excursions that may be organized.
-Any social events that may be organized.
For Press Team
Application Form
The participation fee for journalists is 40€.
For Administrative Staff Members
Application Form
The participation fee for administrative staff members is 20€.


|
MysoreScript
|
Typedefs | |
| typedef std::function< Value *(Compiler::Context &c, Value *, Value *)> | BinOpFn |
A function type that is used by the compileBinaryOp function. More... | |
Functions | |
| template<typename T > | |
| Value * | staticAddress (Compiler::Context &c, T *ptr, Type *ty) |
| Helper function that turns a pointer that is known at compile time into a constant in the code. More... | |
| Value * | compileSmallInt (Compiler::Context &c, Value *i) |
| Generate a small integer object from an integer value. More... | |
| Value * | compileSmallInt (Compiler::Context &c, intptr_t i) |
| Generate a small integer object from an integer constant. More... | |
| Value * | getAsObject (Compiler::Context &c, Value *i) |
| Get the specified value as the LLVM type used for object pointers. More... | |
| Value * | getAsSmallInt (Compiler::Context &c, Value *i) |
| Get the specified value as the LLVM type used for small integer objects. More... | |
| Value * | compileCompare (Compiler::Context &c, CmpInst::Predicate Op, Value *LHS, Value *RHS) |
| Helper function that inserts all of the code required for comparison operations. More... | |
| Value * | compileBinaryOp (Compiler::Context &c, Value *LHS, Value *RHS, Instruction::BinaryOps Op, const char *slowCallFnName) |
| Helper function that inserts all of the code required for small integer operations, either calling the relevant method or doing the arithmetic. More... | |
| typedef std::function<Value*(Compiler::Context &c, Value*, Value*)> anonymous_namespace{compiler.cc}::BinOpFn |
A function type that is used by the compileBinaryOp
function.
Each compileBinOp
method for arithmetic operations provides an instance of this type to insert the correct instruction for performing its operation on the two arguments.
Definition at line 644 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::compileBinaryOp | ( | Compiler::Context & | c, |
| Value * | LHS, | ||
| Value * | RHS, | ||
| Instruction::BinaryOps | Op, | ||
| const char * | slowCallFnName | ||
| ) |
Helper function that inserts all of the code required for small integer operations, either calling the relevant method or doing the arithmetic.
For real objects, the function named by the slowCallFnName
parameter is called, which then invokes the correct method. For integers, the function inserts the binary op specified by Op
.
Definition at line 672 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::compileCompare | ( | Compiler::Context & | c, |
| CmpInst::Predicate | Op, | ||
| Value * | LHS, | ||
| Value * | RHS | ||
| ) |
Helper function that inserts all of the code required for comparison operations.
Definition at line 650 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::compileSmallInt | ( | Compiler::Context & | c, |
| Value * | i | ||
| ) |
Generate a small integer object from an integer value.
Definition at line 34 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::compileSmallInt | ( | Compiler::Context & | c, |
| intptr_t | i | ||
| ) |
Generate a small integer object from an integer constant.
Definition at line 42 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::getAsObject | ( | Compiler::Context & | c, |
| Value * | i | ||
| ) |
Get the specified value as the LLVM type used for object pointers.
This inserts a bitcast or inttoptr instruction as appropriate for the source type.
Definition at line 50 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::getAsSmallInt | ( | Compiler::Context & | c, |
| Value * | i | ||
| ) |
Get the specified value as the LLVM type used for small integer objects.
This inserts a bitcast or ptrtoint instruction as appropriate for the source type.
Definition at line 63 of file compiler.cc.
| Value* anonymous_namespace{compiler.cc}::staticAddress | ( | Compiler::Context & | c, |
| T * | ptr, | ||
| Type * | ty | ||
| ) |
Helper function that turns a pointer that is known at compile time into a constant in the code.
Note that the garbage collector can't see the code (we could tweak the memory manager to use the GC to allocate memory, but even then constants are often materialised as immediate operands to two or more instructions so the GC wouldn't necessarily see them). This means that the address must be a GC root or must be used to store pointers to non-GC'd memory.
Definition at line 26 of file compiler.cc.


MessageFormat
problems in property values:
Unknown format types
MessageFormat
supports only these format types:
Unpaired quotes
property.key=Shouldn't happen: {0}
A single quote is interpreted as an escape until the end of the property, and will not be present in the result string. In most cases this is not what is intended. If a single quote should be present in the result string, it has to be duplicated in the property.
Unmatched braces
Every placeholder must have a closing brace.
Too many quotes
In some cases (e.g. it's
), it is expected that only one quote ends up in the result string.
Cases where two or more quotes are placed together in the result string are reported.
Incorrect lower bounds for nested ChoiceFormat
Lower bounds are expected to be numbers and to be sorted in ascending order.
Wrong number of quotes around parameters
In java.text.MessageFormat
patterns single quotes are used for escaping.
To keep quotes visible, they must be duplicated.
For example when passing 1
:
'{0}'
→ {0}
''{0}''
→ '1'
When using choice format, nested formats are evaluated as format strings themselves, and quotes will need to be duplicated twice.
For example when passing 1
:
{0, choice, 0#no|#1''{0}'' files}
→ {0} files
{0, choice, 0#no|#1''''{0}'''' files}
→ '1' files
Note
Property values are verified only if they contain the literal text {0}
, {0,
, {1}
or {1,
.
This is to make sure that these property values are actually used as MessageFormat
patterns.
New in 2023.2


To relay the color styling of your shiny to static R plots, just call thematic_on()
. Although it’s not required, it’s best practice to also add onStop(thematic_off)
(just in case R code that runs after the app doesn’t want to use thematic):
library(shiny)
library(ggplot2)
library(thematic)
thematic_on()
onStop(thematic_off)
shinyApp(
ui = fluidPage(
theme = shinythemes::shinytheme("darkly"),
tabsetPanel(type = "pills",
tabPanel("ggplot", plotOutput("ggplot")),
tabPanel("lattice", plotOutput("lattice")),
tabPanel("base", plotOutput("base"))
)
),
server = function(input, output) {
output$ggplot <- renderPlot({
ggplot(mtcars, aes(wt, mpg, label = rownames(mtcars), color = factor(cyl))) +
geom_point() +
ggrepel::geom_text_repel()
})
output$lattice <- renderPlot(lattice::show.settings())
output$base <- renderPlot({
image(volcano, col = thematic_get_option("sequential"))
})
}
)
Although this particular app leverages shinythemes to style the app, thematic works regardless of how the app is styled (by the way, the new bootstraplib package provides an exciting new alternative to shinythemes that soon will be our recommend way to style shiny apps). The magic is made possible through shiny::getCurrentOutputInfo()
, which returns the relevant color and font information for the plot’s HTML container. That means, if you wanted, you could style the plot’s container in CSS and thematic would pick up those styles. For example, to change the bg/fg of the ggplot output, you could include the following in your UI definition:
And to change the accent color:
By the way, if you’re a shiny or htmlwidget developer, you can also take advantage of shiny::getCurrentOutputInfo()
in your custom widgets (see here for more info).
Rendering of Google Fonts in shiny requires either showtext or ragg to be installed. showtext works with shiny’s default png device, so just make sure it’s installed and add library(showtext)
(to make sure deployment works as well).
If you’d prefer to use ragg instead, which in some environments (especially hosted ones) can produce higher quality and consistent output in less time (see here for more details), do the following:
In the event that your app uses a Google Font (or a font family that is already known to R), you can turn on auto fonts without thinking twice:
By the way, if you’re curious, to use a Google Font (e.g Pacifico), you can import the fonts remotely this way; however, it’s recommended to use local font files (for hosting reasons). The gfonts package makes it really easy to download and use Google Fonts locally.


The PC Gamer Show 182: Half-Life: Alyx, Star Wars Jedi: Fallen Order, Google Stadia
by James DavenportWelcome back to The PC Gamer Show, our weekly podcast. You can watch the show live on Thursdays at 9 am PDT on our Twitch channel, or after the fact through any of the links below.
On this week's show, Tom talks about how much he loves the intense lightsaber combat of Star Wars Jedi: Fallen Order, Joanna breaks down everything Stadia, and we watch the Half-Life: Alyx trailer the minute it's released.
Where to watch:
Hosts this week:
James Davenport (Twitter, Twitch)
Joanna Nelius (Twitter)
Tom Senior (Twitter)


Hista-photo-gram(Copyright)
×
You've got Mail!
Home
Web (and other) Design
Physical Prowess...from high school
&
Award
Lift Goals(Copyright)
See me, you see?


Pirate (ARRR) is a cryptocurrency that solves the issue of financial privacy on the blockchain through forced anonymous transactions. Protect your wallet and transactions with Pirate’s completely anonymous network. With forced private transactions, your privacy cannot be compromised by another users’ activity on the network. If you wish to learn more, please join the official Pirate Discord at https://pirate.black/discord/ or message FishyGuts#5085, who will be happy to help out.
@FishyGuts i'll add the minigpool when i arrive home
Flexatron23-Oct-18 01:17 PM
ARRR TipBotBOTToday at 3:16 PM
I am currently in maintenance mode and cannot interact with the public: I am being migrated to a shiny new server! Thank you for your patience.
Arrr tip bot is out of character "I am currently in lose sea legs mode and cannot converse with ye scallywags, I am being expated to a shiny new vessel. thank ye for waiting"
jeezy23-Oct-18 01:35 PM
500mil diff, new record?
Zach23-Oct-18 01:37 PM
Nope, it hit over 600 at least twice within the past 24 hours(edited)
a7mi23-Oct-18 01:37 PM
whts the price for arr per kmd now
Zach23-Oct-18 01:38 PM
Between 0.11 and 0.14 seemingly
Check out #deleted-channel
a7mi23-Oct-18 01:39 PM
if my hasing is 11k how much in usd
Zach23-Oct-18 01:40 PM
Your hashing is 11k? that doesnt convert into usd.. how much ARRR do you have?
CryptoNinjaa23-Oct-18 01:40 PM
If 11k Arr 1500usd~ rough
a7mi23-Oct-18 01:41 PM
nope
bro
miniing with z9 mini
@CryptoNinjaa
32something
ComputerGenie 👴👴👴23-Oct-18 01:42 PM
@a7mi dafuq you tagging me in #deleted-channel for?


Match Play. Red leads 2-1 to 5. Should Red redouble? If redoubled should White take?
pips Red=111 White=77
(CorrectAnswer= 0/0, ErrorScore= 0)
Position submitted by Arthur Ramer
White needs 18% winning chances to take this redouble – does he have them? The rollout says pass by 0.014 so either take or pass is OK. White's four-point home board comes in to play just often enough to make the take viable.
Red did redouble and White took. Red rolled 31 and correctly played 21/20, 21/18. White’s next three rolls were 11, 66 and 55. Such is backgammon!
White needs 18% winning chances to take this redouble – does he have them? The rollout says pass by 0.014 so either take or pass is OK. White's four-point home board comes in to play just often enough to make the take viable.
Red did redouble and White took. Red rolled 31 and correctly played 21/20, 21/18. White’s next three rolls were 11, 66 and 55. Such is backgammon!
Cube analysis 2-ply cubeless equity +0.755 (Money: +0.800) 0.794 0.221 0.006 - 0.206 0.017 0.000 Cubeful equities: 1. Double, take +0.904 2. Double, pass +1.000 (+0.096) 3. No double +0.903 (-0.001) Proper cube action: Redouble, take


To delete an occurrence of a search string from a multiselection, press &shortcut:UnselectPreviousOccurrence;.


XGBoost Exporter Module¶
-
xgboost_to_pmml.
add_segmentation
(model, segments_equal_to_estimators, mining_schema_for_1st_segment, out, id)[source]¶ It returns segmentation for a mining model
- model :
- Contains Xgboost model object.
- segments_equal_to_estimators : List
- Contains List Segements equals to the number of the estimators of the model.
- mining_schema_for_1st_segment :
- Contains Mining Schema for the First Segment
- out :
- Contains the Output element
- id : Integer
- Index of the Segements
-
xgboost_to_pmml.
create_node
(obj, main_node, derived_col_names)[source]¶ It creates nodes.
- obj : Json
- Contains nodes in json format.
- main_node :
- Contains node build with Nyoka class.
- derived_col_names : List
- Contains column names after preprocessing.
-
xgboost_to_pmml.
generate_Segments_Equal_To_Estimators
(val, derived_col_names, col_names)[source]¶ It returns number of Segments equal to the estimator of the model.
- val : List
- Contains a list of well structured node for binary classification/inner segments for multi-class classification
- derived_col_names : List
- Contains column names after preprocessing.
- col_names : List
- Contains list of feature/column names.
-
xgboost_to_pmml.
get_PMML_kwargs
(model, derived_col_names, col_names, target_name, mining_imp_val, categoric_values)[source]¶ It returns all the pmml elements.
- model :
- Contains XGBoost model object.
- derived_col_names : List
- Contains column names after preprocessing
- col_names : List
- Contains list of feature/column names.
- target_name : String
- Name of the target column .
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
get_ensemble_models
(model, derived_col_names, col_names, target_name, mining_imp_val, categoric_values)[source]¶ It returns the Mining Model element of the model
- model :
- Contains Xgboost model object.
- derived_col_names : List
- Contains column names after preprocessing.
- col_names : List
- Contains list of feature/column names.
- target_name : String
- Name of the Target column.
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value.
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
get_multiple_model_method
(model)[source]¶ It returns the type of multiple model method for MiningModels.
- model :
- Contains Xgboost model object
-
xgboost_to_pmml.
get_outer_segmentation
(model, derived_col_names, col_names, target_name, mining_imp_val, categoric_values)[source]¶ It returns the Segmentation element of the model.
- model :
- Contains Xgboost model object.
- derived_col_names : List
- Contains column names after preprocessing.
- col_names : List
- Contains list of feature/column names.
- target_name : String
- Name of the Target column.
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
get_segments
(model, derived_col_names, col_names, target_name, mining_imp_val, categoric_values)[source]¶ It returns the Segment element of the model.
- model :
- Contains Xgboost model object.
- derived_col_names : List
- Contains column names after preprocessing.
- col_names : List
- Contains list of feature/column names.
- target_name : String
- Name of the Target column.
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
get_segments_for_xgbc
(model, derived_col_names, feature_names, target_name, mining_imp_val, categoric_values)[source]¶ It returns all the segments of the Xgboost classifier.
- model :
- Contains Xgboost model object.
- derived_col_names : List
- Contains column names after preprocessing.
- feature_names: List
- Contains list of feature/column names.
- target_name : String
- Name of the Target column.
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
get_segments_for_xgbr
(model, derived_col_names, feature_names, target_name, mining_imp_val, categorical_values)[source]¶ It returns all the Segments element of the model
- model :
- Contains Xgboost model object.
- derived_col_names : List
- Contains column names after preprocessing.
- feature_names : List
- Contains list of feature/column names.
- target_name : List
- Name of the Target column.
- mining_imp_val : tuple
- Contains the mining_attributes,mining_strategy, mining_impute_value
- categoric_values : tuple
- Contains Categorical attribute names and its values
-
xgboost_to_pmml.
mining_Field_For_First_Segment
(feature_names)[source]¶ It returns the Mining Schema of the First Segment.
- feature_names : List
- Contains list of feature/column names.
-
xgboost_to_pmml.
replace_name_with_derivedColumnNames
(original_name, derived_col_names)[source]¶ It replace the default names with the names of the attributes.
- original_name : List
- The name of the node retrieve from model
- derived_col_names : List
- The name of the derived attributes.
-
xgboost_to_pmml.
xgboost_to_pmml
(pipeline, col_names, target_name, pmml_f_name='from_xgboost.pmml',model_name=None,description=None)[source]¶ Exports xgboost model object into pmml
- pipeline :
- Contains an instance of Pipeline with preprocessing and final estimator
- col_names : List
- Contains list of feature/column names.
- target_name : String
- Name of the target column.
- pmml_f_name : String
- Name of the pmml file. (Default=’from_xgboost.pmml’)
- model_name: String (optional)
- Name of the model
- description : string (optional)
- Description of the model


PL/php - PHP Procedural Language for PostgreSQL ¶
Contents
- What is PL/php?
- Download and Installation
- Creating the PL/php language
- Functions and Arguments
- Data Types and Returns
- Global Shared Variable
- PostgreSQL Array Support
- Polymorphic Arguments and Return Types
- Database Access (SPI)
- Triggers
- Trusted vs. Untrusted
- Composite Type Arguments
- Returning A Row (composite type)
- Returning multiple rows (SRF functions)
- Function Argument Names
- Limitations
What is PL/php? ¶
PL/php is a procedural language with hooks into the PostgreSQL database sytem, intended to allow writing of PHP functions for use as functions inside the PostgreSQL database. It was written by Command Prompt, Inc. and has since been open sourced and licensed under the PHP and PostgreSQL (BSD) licenses.
Download and Installation ¶
Please see the installation documentation for instructions on how to install PL/php 1.0. To install the new code, which only works with PostgreSQL 8.0 and 8.1 and is currently in development, see this page instead.
Creating the PL/php language ¶
Please see the documentation on how to create the language in a database once the library is installed. If you are using PostgreSQL 8.1 you must follow these other instructions instead.
Functions and Arguments ¶
To create a function, use the standard syntax:
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$ # plphp function body here $$ LANGUAGE 'plphp';
Arguments are passed in the $args array and the result value is returned with the 'return' statement.
CREATE FUNCTION plphp_max(integer, integer) RETURNS integer AS $$ if ($args[0] > $args[1]) { return $args[0]; } else { return $args[1]; } $$ STRICT LANGUAGE 'plphp'
NOTE: The use of the STRICT clause saves us from having to think about NULL input values to our function. If a NULL value is passed, the function will not be run at all, but will rather just return a NULL result automatically.
In a non-strict function, if the actual value of an argument is NULL, the corresponding $args[n-1] variable will be set to an empty string (unset).
Data Types and Returns ¶
The arguments passed into your PL/php function are converted to text so you can manipulate them with the loose typing enjoyed in typical PHP scripts. Conversely, the return command will accept any string that is acceptable input format for the function's declared return type.
Global Shared Variable ¶
There is a global variable that can be used to store data between function calls, called $_SHARED.
CREATE FUNCTION set_var(text) RETURNS text AS $$ global $_SHARED; $_SHARED['first']=$args[0]; return 'ok'; $$ LANGUAGE 'plphp'; CREATE FUNCTION get_var() RETURNS text AS $$ global $_SHARED; return $_SHARED['first']; $$ LANGUAGE 'plphp'; SELECT set_var('hello plphp'); SELECT get_var(); -- will return 'hello plphp'
NOTE: The shared global variable is connection-specific. This is useful for passing information around a single script execution, but it is wiped when the connection is closed.
PostgreSQL Array Support ¶
There is support for multi-dimensional arrays.
For example:
CREATE FUNCTION php_array() RETURNS text[][] AS $$ $return = array(array("Steven", "Klassen"), array("Jonathan", "Daugherty")); return $return; $$ LANGUAGE 'plphp';
sklassen=# select php_array(); php_array ----------------------------------------- {{Steven,Klassen},{Jonathan,Daugherty}} (1 row)
Polymorphic Arguments and Return Types ¶
Functions may be declared to accept and return the polymorphic types 'anyelement', 'anyarray', and 'anyrecord'. See the PostgreSQL documentation section 33.2.5 for a more detailed explanation of polymorphic functions.
For example,
CREATE FUNCTION array_three_values(anyelement, anyelement, anyelement) RETURNS anyarray AS ' $ret[0] = $args[0]; $ret[1] = $args[1]; $ret[2] = $args[2]; return $ret; ' LANGUAGE 'plphp'; SELECT array_three_values(3,2,1); array_three_values -------------------- {3,2,1} (1 row) CREATE OR REPLACE FUNCTION php_row(integer) RETURNS record AS ' $ret[f1]=$args[0]; $ret[f2]="hello"; $ret[f3]="world"; return $ret; ' LANGUAGE 'plphp'; select * FROM php_row(1) AS (f1 integer, f2 text, f3 text);
Database Access (SPI) ¶
Some functions are provided for database access.
- spi_exec - Execute a query with optional limit.
resource spi_exec(string query[, int limit])
- spi_fetch_row - Return an associative array of the row's results.
array spi_fetch_row(resource result)
- spi_status - Return the status of a previous query. If this is
SPI_OK_SELECT you can obtain tuples from the result using
spi_fetch_row.
string spi_status(resource result)
- spi_processed - Return the number of tuples in a result.
int spi_processed(resource result)
- spi_rewind - Put the row cursor at the beginning of the
result, so spi_fetch_row will continue fetching tuples from the
beginning of the result.
void spi_rewind(resource result)
For example:
This isn't a particularly useful function, but it will illustrate the above-described access functions. You provide an integer id and it returns the username text field.
CREATE FUNCTION get_username(integer) RETURNS text AS $$ # Assign the query to a variable. $query = "SELECT username FROM users WHERE id = " .$args[0]; # Run the query and get the $result object. $result = spi_exec_query($query); # Fetch the row from the $result. $row = spi_fetch_row($result); return $row['username']; $$ LANGUAGE 'plphp'; sklassen=# select get_username(1); get_username -------------- sklassen (1 row)
Note that results from spi_exec_query are allocated in RAM as a whole, so if you need to process huge result sets, you need to use a cursor. This is not possible with the current interface however, so if you need to do this, bug the developers.
Triggers ¶
When a function is being used to return a trigger, the associative array $_TD contains trigger-related values.
- $_TD["new"]
- An associative array containing the values of the new table row for INSERT/UPDATE actions, or empty for DELETE. The array is indexed by field name. Important note: Fields that are NULL will not appear in the array!
- $_TD["old"]
- An associative array containing the values of the old table row for UPDATE/DELETE actions, or empty for INSERT. The array is indexed by field name. Important note: Fields that are NULL will not appear in the array!
- $_TD["name"]
- Contains the trigger name itself.
- $_TD["event"]
- Contains one of the values: "INSERT", "UPDATE", "DELETE".
- $_TD["when"]
- Contains one of the values: "BEFORE", "AFTER".
- $_TD["level"]
- Contains one of the values: "ROW", "STATEMENT".
- $_TD["relid"]
- Contains the relation ID of the table on which the trigger occured.
- $_TD["relname"]
- Contains the relation name.
- $_TD["args"]
- An array of arguments passed to the trigger, if any. They can be accessed as $_TD["args"][idx]. For example, $_TD["args"][0].
- $_TD["argc"]
- The number of arguments passed to the trigger, 0 if none.
Example of an AFTER INSERT trigger ¶
Suppose you have a users table with the typical columns and an activity table that you're using to track page accesses. On row
INSERT to the activity table, you want to update the last_seen field of the appropriate user's record.
Consider the following table definitions:
CREATE TABLE users ( id serial PRIMARY KEY NOT NULL, username text NOT NULL, email text, last_seen timestamp without time zone, active boolean DEFAULT true NOT NULL ); CREATE TABLE activity ( id serial PRIMARY KEY NOT NULL, users_id integer NOT NULL, file_accessed text NOT NULL, stamp timestamp without time zone DEFAULT now() NOT NULL, CONSTRAINT users_id_exists FOREIGN KEY (users_id) REFERENCES users(id) ); CREATE FUNCTION update_lastseen() RETURNS trigger AS $$ $new =& $_TD['new']; if (isset($new['users_id']) && isset($new['stamp'])) { $query = "UPDATE users SET last_seen = '" .$new['stamp']. "' WHERE id = " .$new['users_id']; $rv = spi_exec($query); } return; $$ LANGUAGE 'plphp'; CREATE TRIGGER after_update_lastseen_trigger AFTER INSERT ON activity FOR EACH ROW EXECUTE PROCEDURE update_lastseen();
1. We'll insert a new user row.
sklassen=# insert into users (username, email) values ('sklassen','sklassen@commandprompt.com'); INSERT 1 sklassen=# select * from users where username = 'sklassen'; id | username | email | last_seen | active ----+----------+----------------------------+-----------+-------- 1 | sklassen |<EMAIL_ADDRESS>| | t (1 row)
2. Insert a new row into the activity table.
sklassen=# insert into activity (users_id, file_accessed) values (1,'index.html'); INSERT 1
3. Check and make sure our trigger fired as expected.
sklassen=# select * from users where username = 'sklassen'; id | username | email | last_seen | active ----+----------+----------------------------+----------------------------+-------- 1 | sklassen |<EMAIL_ADDRESS>| 2005-01-10 09:48:57.191595 | t (1 row)
Example of a BEFORE INSERT OR UPDATE trigger ¶
Let's say we have a user named admin that we want to prevent the application from modifying. We'll create a BEFORE DELETE trigger that prevents them from deleting the row and a BEFORE UPDATE trigger that prevents them modifying the username on which the previous trigger depends.
CREATE TABLE users (username text, email text); INSERT INTO users VALUES ('admin', 'admin@commandprompt.com'); INSERT INTO users VALUES ('darcy', 'darcy@example.com'); CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ # The record may not be deleted if the username is "admin". if ($_TD['old']['username'] == 'admin') { pg_raise('notice', "You cannot delete the admin user"); return 'SKIP'; } return; $$ LANGUAGE 'plphp'; CREATE TRIGGER before_delete_immortal_trigger BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE immortal(); CREATE OR REPLACE FUNCTION protect_admin() RETURNS trigger AS $$ # Do not let them modify the username of the admin account. $oldUsername = $_TD['old']['username']; $newUsername = $_TD['new']['username']; if ($oldUsername == 'admin' && ($oldUsername != $newUsername)) { pg_raise('notice', "You cannot change the admin username."); return 'SKIP'; } else { return; } $$ LANGUAGE 'plphp'; CREATE TRIGGER before_update_protect_admin_trigger BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE protect_admin();
Now the user admin cannot be deleted, nor its username can be changed:
pl_regression=# select * from users; username | email ----------+------------------------- admin |<EMAIL_ADDRESS>darcy |<EMAIL_ADDRESS>(2 rows) pl_regression=# update users set username = 'foobar'; NOTICE: plphp: You cannot change the admin username. UPDATE 1 pl_regression=# select * from users; username | email ----------+------------------------- admin |<EMAIL_ADDRESS>foobar |<EMAIL_ADDRESS>(2 rows) pl_regression=# delete from users; NOTICE: plphp: You cannot delete the admin user DELETE 1 pl_regression=# select * from users; username | email ----------+------------------------- admin |<EMAIL_ADDRESS>(1 row)
Trusted vs. Untrusted ¶
Normally, PL/php is installed as a "trusted" procedural language named 'plphp'. In this configuration, PHP will run in "safe mode". Read more about the restrictions here:
http://www.php.net/manual/en/features.safe-mode.functions.php
In general, the operations that are restricted are those that interact with the environment. This includes file operations, require, and use (for external modules).
Since there is no way to gain access to the internals of the database backend process or the operating system itself, any unprivileged database user may use functions written in this language.
An example of a NON-working function due to security constraints:
CREATE FUNCTION read_passwd_file() RETURNS text AS ' readfile("/etc/passwd"); return 0; ' LANGUAGE 'plphp';
It will appear to execute, but depending on your log level, you'll may see something like the following:
Warning: readfile(): SAFE MODE Restriction in effect. The script whose uid is 500 is not allowed to access /etc/passwd owned by uid 0 in Command line code on line 3 Warning: readfile(/etc/passwd): failed to open stream: Success in plphp function source on line 3
Sometimes it is desirable to write PHP functions that are not restricted. In this case, you can create the language as 'plphpu' to enable the previously unavailable functions.
CREATE LANGUAGE plphpu;
Composite Type Arguments ¶
Composite-type arguments are passed to the function as associative array. The keys of the array are the attribute names of the composite type. Here is an example:
CREATE TABLE employee ( name text, basesalary integer, bonus integer ); CREATE FUNCTION empcomp(employee) RETURNS integer AS ' return $args[0][''basesalary''] + $args[0][''bonus'']; ' LANGUAGE 'plphp'; INSERT INTO employee values ('Josh', 1000, 10); INSERT INTO employee values ('Darcy', 500, 20); SELECT name, empcomp(employee) FROM employee;
This example results in the following output:
name | empcomp -------+--------- Josh | 1010 Darcy | 520 (2 rows)
Returning A Row (composite type) ¶
To return a row or composite-type value from a PL/php-language function, you can use an indexed array:
CREATE TYPE php_row AS (f1 integer, f2 text, f3 text); CREATE OR REPLACE FUNCTION php_row(integer) RETURNS php_row AS $$ $ret['f1'] = $args[0]; $ret['f3'] = "world"; $ret['f2'] = "hello"; return $ret; $$ LANGUAGE 'plphp'; SELECT * FROM php_row(1);
Will return:
f1 | f2 | f3 ----+-------+------- 1 | hello | world
Returning multiple rows (SRF functions) ¶
SRF stands for "set-returning functions". This means you can return multiple rows, like in the following example:
CREATE TYPE php_row AS (f1 integer, f2 text, f3 text); CREATE OR REPLACE FUNCTION php_set(integer) RETURNS SETOF php_row AS $$ $ret['f1'] = $args[0]; $ret['f2'] = "hello"; $ret['f3'] = "world"; return_next($ret); $ret['f1'] = 2 * $args[0]; $ret['f2'] = "hello"; $ret['f3'] = "postgres"; return_next($ret); $ret['f1'] = 3 * $args[0]; $ret['f2'] = "hello"; $ret['f3'] = "plphp"; return_next($ret); $$ LANGUAGE 'plphp';
This will result in:
pl_regression=# SELECT * FROM php_set(1); f1 | f2 | f3 ----+-------+---------- 1 | hello | world 2 | hello | postgres 3 | hello | plphp (3 rows)
The important thing to notice here is the return_next() function. This function must be called with a single parameter, which must be an array with one element per attribute of the return type. Note that currently the names of the array elements are ignored; the elements are assigned to attributes in order. If you must return a null value, asign a null value to an array element. This may be fixed in a future release.
Finishing a SRF is done by either calling return or by falling off the end of the function. At that point the entire results are delivered back to the outer query, be it directly to the user or to be joined to other tables/SRFs/etc.
The return tuples are written to a Postgres "tuplestore", which is disk-backed, and thus do not need to fit entirely in RAM.
Function Argument Names ¶
You can assign names to pl/php function arguments:
CREATE FUNCTION plphp_match(document text, word text) RETURNS BOOL AS $$ if (strstr($document, $word)) return TRUE; else return FALSE; $$ LANGUAGE 'plphp'; Example output: template1=# select plphp_match('PostgreSQL', 'SQL'); plphp_match ------------- t (1 row)
Both $name and $args[position] variable access methods are supported.
Limitations ¶
PL/php functions cannot call each other directly because their names are mangled.
SPI calls have the whole results allocated in memory, so processing huge results may be problematic. Also it's not possible to use cursors with SPI.


I am a technology enthusiast
who is propelled by unquenchable curiosity in the technology world,
my main interests are in Artificial Intelligence, Aviation, Telecommunications, Cloud Computing and Software Engineering
My main objective in Moringa School is to gain high end skills that will help me obtain a position and opportunities which allows me to utilize my vast knowledge and skills in Information Technology and Software Engineering by solving problems , in the end making the world a better place, for you and I.
Tech Support Volunteer - Alphabet Inc. | Google Inc.
January, 2019 - March 2019
Monitor and maintain the computer systems and networks within organisation.
Systems Engineer - IBM Digital
July 2018 - December 2018
Implementing new systems, correcting software errors in existing systems and improving performance through hardware upgrades.
Penetration Testing Engineer | Qatar Airways
January 2018 - June 2018
Probing and exploiting security vulnerabilities in web-based applications, networks and systems within the organization.
Here are some of the programming languages and frameworks that i am proficient in.
HTML 5 is a software solution stack that defines the properties and behaviors of web page content by implementing a markup based pattern to it. HTML 5 is the fifth and current major version of the HTML standard, and subsumes XHTML.
JavaScript, often abbreviated as JS, is a high-level, interpreted programming language that conforms to the ECMAScript specification. JavaScript has curly-bracket syntax, dynamic typing, prototype-based object-orientation, and first-class functions.
Cascading Style Sheets is a style sheet language used for describing the presentation of a document written in a markup language like HTML. CSS is a cornerstone technology of the World Wide Web, alongside HTML and JavaScript.
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system.
Bootstrap is a free and open-source CSS framework directed at responsive, mobile-first front-end web development. It contains CSS- and JavaScript-based design templates for typography, forms, buttons, navigation and other interface components.
PHP: Hypertext Preprocessor is a general-purpose programming language originally designed for web development. It was originally created by Rasmus Lerdorf in 1994; the PHP reference implementation is now produced by The PHP Group.
Moringa School.
March, 2019 - Current
Software Engineering | Full Stack Developer
Junior High School
November 2016
Certificate of Secondary Education
Gerald Primary school
November 2012
Certificate of Primary Education
Some of the impressive projects that i have done.
Kindly click on the Projects Names to view them.
I enjoy doing the following fun activities
Impressed? Fill the following form for any inquiries.
Moringa School | Nairobi
+254 (0) 777 685 128
maureezgaga@gmail.com


{% extends "base.html" %} {% block title %}
Medieval catalogues > UNIVERSITY AND COLLEGE LIBRARIES OF OXFORD > Merton College > Leland c. 1535
{% endif %}9 identified entries found.
All data was derived from the List of Identifications by Professor Richard Sharpe.
{% if not printing %}
A key to codes used in the List is available (opens in new tab).{% endif %}


Here are the results for your comparison request
FROM: Greater Noida
TO:
Pitampura
Total time : 1 hr 7 min
Total distance : 60.1 km
Route : via Noida-Greater Noida Expy and Mahatma Gandhi Marg/Ring Rd
Fastest route now due to traffic conditions


13 February 2006
The Ministry of Health in Indonesia has confirmed an additional two cases of human infection with the H5N1 avian influenza virus. Both cases were fatal.
The first case occurred in a 22-year-old woman who developed symptoms on 25 January and died on 10 February. Her neighbours kept chickens. Samples from these chickens and from pet birds in a market near the woman’s home are being tested by Indonesia’s animal health authorities.
The second case occurred in a 27-year-old woman who developed symptoms on 31 January and died on 10 February. Deaths of chickens in her neighbourhood were reported four days prior to symptom onset.
The two women resided in different sub-districts of West Java Province.
The newly confirmed cases bring the total in Indonesia to 25. Of these, 18 were fatal.


Mood Map
A map showing how the mood in an area is, based on tweets from the period: 13/04/2015 - 21/04/2015.
{% extends "base.html" %} {% load staticfiles %} {% block js_extra %} {% endblock %} {% block header %} {% endblock %} {% block content %}
Twitter mood project for course 02806 Social data analysis and visualization F15
The video from assignment part A can be viewed here


py-pagexml: Python wrapper for the PageXML C++ library
The py-pagexml python package is a library of functions that eases working with omni:us Pages Format XML files (referred to as OPF XML files). It allows you from python to read an OPF file, extract information contained within, modify or add content, create an OPF from scratch, crop parts for page images, etc.
By default OPF XML files are validated against the XSD schema when reading, saving, or on request by calling a function. The documentation the XSD schema and the schema itself included in py-pagexml can be found at:
The official online documentation of py-pagexml is available at https://omni-us.github.io/pagexml/py-pagexml.
The py-pagexml package can be built with two modes: normal and slim. As the name
implies, the slim build is smaller but more importantly it has less library
dependencies. This also means that there are some features which are not
available, namely: functions related to images, e.g.
PageXML.crop
; and functions that perform intersections of polygons, e.g.
PageXML.selectByOverlap
.
Software dependencies
The core of py-pagexml is a compiled C++ library that links with a few libraries, so it requires installation of dependencies that cannot be automatically obtained from pypi servers.
There are docker images available at docker hub which include both the runtime and the build dependencies already installed. In particular the runtime docker images are intended to be used as base images for applications that use pagexml. The specific list of dependencies both for runtime and building are listed below.
Runtime dependencies
- Slim:
python3
- Normal (in addition to the previous):
libopencv-imgcodecs (Ubuntu 18.04/20.04) | libopencv-highgui (Ubuntu 16.04)
libopencv-imgproc
libopencv-core
libgdal
Building dependencies
- Slim:
python3-setuptools
python3-pkgconfig
python3-wheel
python3-dev
swig
- Normal (in addition to the previous):
libopencv-dev
libgdal-dev
libboost-all-dev
Installation from wheel binary file
If you have configured a pypi server that includes pagexml, installation is as simple as:
pip3 install pagexml
The slim build has a different name, thus the install comand would be:
pip3 install pagexml_slim
Otherwise you can install it from a github release. Each release includes multiple wheel files. One for python 3.5 which is built for Ubuntu 16.04, another for python 3.6 built for Ubuntu 18.04 and another for python 3.8 built for Ubuntu 20.04. Once you have located the appropriate wheel file, copy the link and run as follows replacing the URL with the one you copied:
pip3 install https://github.com/omni-us/pagexml/releases/download/20*/pagexml-20*-linux_x86_64.whl
Building the wheel file from source
Clone the github repository https://github.com/omni-us/pagexml.git, go to the py-pagexml directory and then run:
pip3 install --editable .[dev]
./setup.py bdist_wheel
To build the slim package, give the --slim
command line option, e.g.:
./setup.py bdist_wheel --slim
Simple usage examples
Create a new Page XML adding regions, text and properties
import pagexml
pxml = pagexml.PageXML()
# Create a new page xml
file = 'example_image.jpg'
width = 400
height = 200
pxml.newXml('name-and-version-of-tool', file, width, height)
# Add a text region to the Page
page = pxml.selectNth('//_:Page', 0)
reg = pxml.addTextRegion(page)
# Set text region bounding box with a confidence
pxml.setCoordsBBox(reg, 10, 20, 80, 60, 0.8)
# Set the text for the text region with a confidence
pxml.setTextEquiv(reg, 'lorem ipsum', 0.9)
# Add property to text region
pxml.setProperty(reg, 'key', 'value')
# Add a second page with a text region and specific id
page = pxml.addPage('example_image_2.jpg', 300, 300)
reg = pxml.addTextRegion(page, 'regA')
pxml.setCoordsBBox(reg, 15, 12, 76, 128)
# Write XML to file
pxml.write('example_image.xml')
Modify an existing Page XML
# Load an existing XML
import pagexml
pxml = pagexml.PageXML('example_image.xml')
# Add content to loaded XML
pxml.setProperty(pxml.selectNth('//_:Page', 0), 'key', 'value')
# Write XML to file
pxml.write('example_image_2.xml')
Crop an element and save image to disk
# Load an existing XML
import pagexml
pxml = pagexml.PageXML('examples/lorem.xml')
# Crop element with specific ID
cropped = pxml.crop('//*[@id="r1_l1"]/_:Coords')[0]
# Save image to disk
pagexml.imwrite(cropped.name+'.png', cropped.image)


Click visualization to start API session.
Inspect page source for individual examples.
Modify source to change which Graphistry endpoint and client version are being used.
See Graphistry Hub for the latest.
Install
npm i @graphistry/client-api
For direct linking, see folder node_modules/@graphistry/client-api/dist/


Tooltips are essential for displaying extended information for a term or action on a page.
When using the text-only tooltip, assign the has-tip
class to the span that you wish to have the tooltip.
I'm a text only tooltip.
Four positioning options are available: top, right, bottom, and left. Besides that, auto option may be used to detect a position that fits the component on the screen.
Pass a string as tooltip content.
Easily include a tooltip with a custom icon (Angular Font Awesome with JS tree-shaking used in example below).
Create <ng-template #myId>
with any html allowed by Angular, and provide template ref [tooltip]="myId"
as tooltip content.
By using [innerHtml]
inside ng-template
you can display any dynamic html.
When you have some styles on a parent element that interfere with a tooltip, you’ll want to specify a container="body"
so that the tooltip’s HTML will be appended to body. This will help to avoid rendering problems in more complex components (like our input groups, button groups, etc) or inside elements with overflow: hidden
Desktop
Mobile
You can manage tooltip using its show()
, hide()
and toggle()
methods. If you want to manage tooltip's state manually, use triggers=""
This text has attached tooltip.
Hold on cursor above button for 0.5 second or more to see delayed tooltip.