Joins With

CommentsProcedural SQL (PSQL) Statements

Joins with `LATERAL` Derived Tables

A derived table defined with the LATERAL keyword is called a lateral derived table.If a derived table is defined as lateral, then it is allowed to refer to other tables in the same FROM clause, but only those declared before it in the FROM clause.

Lateral Derived Table Examples

/* select customers with their last order date and number */
select c.name, ox.order_date as last_order, ox.number
from customer c
  left join LATERAL (
    select first 1 o.order_date, o.number
    from orders o
    where o.id_customer = c.id
    order by o.ORDER_DATE desc
  ) as ox on true
--
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c
  cross join LATERAL (
    select first 1 city_name, population
    from cities
    where cities.country_name = c.country_name
    order by population desc
  ) AS dt;
--
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
  LATERAL ( select max(amount) as amount
            from all_sales
            where all_sales.salesperson_id = salespeople.id
  ) as max_sale,
  LATERAL ( select customer_name
            from all_sales
            where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
  ) as customer_of_max_sale;

The `WHERE` clause

The WHERE clause serves to limit the rows returned to the ones that the caller is interested in.The condition following the keyword WHERE can be as simple as a check like “AMOUNT = 3” or it can be a multilayered, convoluted expression containing subselects, predicates, function calls, mathematical and logical operators, context variables and more.

The condition in the WHERE clause is often called the search condition, the search expression or simply the search.

In DSQL and ESQL, the search condition may contain parameters.This is useful if a query has to be repeated a number of times with different input values.In the SQL string as it is passed to the server, question marks are used as placeholders for the parameters.These question marks are called positional parameters because they can only be told apart by their position in the string.Connectivity libraries often support named parameters of the form :id, :amount, :a etc.These are more user-friendly;the library takes care of translating the named parameters to positional parameters before passing the statement to the server.

The search condition may also contain local (PSQL) or host (ESQL) variable names, preceded by a colon.

Syntax

SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]

Table 1. Argument of `WHERE`
Parameter	Description
search-condition	A Boolean expression returning `TRUE`, `FALSE` or `UNKNOWN` (`NULL`)

Only those rows for which the search condition evaluates to TRUE are included in the result set.Be careful with possible NULL outcomes: if you negate a NULL expression with NOT, the result will still be NULL and the row will not pass.This is demonstrated in one of the examples below.

Joins With

Joins with LATERAL Derived Tables

The WHERE clause

Joins with `LATERAL` Derived Tables

The `WHERE` clause