Value blocks

An introduction to 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:

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

and the resulting table would be:

region city
Central Chicago
East New York
West San Francisco

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

  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:

city sales_agent manager
Chicago Anna Snelling Dustin Brinkmann
New York Boris Faz Rocco Neubert
San Francisco Carl Lin Summer Sewald
San Francisco Carol Thompson Celia Rouche
New York Cassey Cress Rocco Neubert
Chicago Cecily Lampkin Dustin Brinkmann
New York Corliss Cosme Cara Losch
New York Daniell Hammack Rocco Neubert
Chicago Darcel Schlecht Melvin Marxen