Value blocks

A value block is a table created and defined within a query that exists only in the scope of that query. It acts like any other table and can be used to extend the data in an existing table by means of a small look up. For example, let’s say we wanted to add the city locations of the regional offices for each sales person to the results of a query. While the regions are stored in our tables, the cities where they are headquartered are not. The first step would be to create a value block with the regions and the cities. The query to create the block would look like this:

SELECT *
  FROM (VALUES ("Central", "Chicago"), ("East", "New York"), ("West", "San Francisco")) AS Regional_headquarters(region, city)

and the resulting table would be:

This table can be joined with the sales team table to return a list of sales agents and their headquartered offices:

SELECT Regional_headquarters.city,
       sales_teams.sales_agent,
       sales_teams.manager
  FROM (VALUES ("Central", "Chicago"), ("East", "New York"), ("West", "San Francisco")) AS Regional_headquarters(region, city)
       JOIN sales_teams
       ON sales_teams.regional_office = Regional_headquarters.region
       ORDER BY sales_teams.sales_agent

and the results would be: