Tech BlogShowcase

Table Queries & Joins

Table Queries & Joins

Query your data and relate it across tables for powerful composition.

Overview

Tableland is an openly readable network — anyone or anything can read from the network. Queries allow you to search for data across one or multiple tables, define the rules for filtering the data, and relate it with data stored elsewhere. This opens up a ton of doors for true composability across multiple tables and chains, but let’s start with some of the basics.

Sample Data

We’ll start with a table that resembles the following to help demonstrate how queries work. Let’s call this table query_table_1, which holds data about an animal and its color.

id
name
color
0
crow
black
1
fish
silver

Selecting Data

Let’s start with the most basic SQL query — a SELECT * statement:

SELECT * FROM query_table_1

Whether or you’re familiar with SQL syntax, check out the full SQL Specification, which takes from heavily from SQLite. What the query above is saying is to retrieve all data from the table named query_table_1: to “select all” of it.

Let’s walk through another basic use case — selecting a single row from a table and all of its values at some {id}:

SELECT * FROM query_table_1 WHERE id={id}

And one final example. Instead of getting all of the columns in a row, maybe we only want a subset of them — the id and name column:

SELECT id, name FROM query_table_1 WHERE id={id}

Awesome! This query syntax can be used for much more complex queries that are nested to find a specific data that spans across more than one table. This is where a simple JOIN statement is useful.

JOIN Across Tables

Use a JOIN to search and combine data across two tables. Recall that it’s not required to “own” the table in order to make a JOIN with another tables. This opens up a number of interesting possibilities where you can relate & compose data across tables with a matching key.

Sample Data

Let’s introduce a new table that is related to the example above. Here, our join_table_2 table holds skills & level the animals defined above have:

id
skill
level
0
fly
2
1
swim
5

If we want to combine (or compose) the data across these two tables, we can run a JOIN like so and use aliases (q for query_table_1, and a for join_table_2) to make the queries more readable:

SELECT 
  q.id, q.name, j.skill
FROM 
  query_table_1 as q
  JOIN join_table_2 as j
WHERE 
  q.id = j.id 
  AND q.id = {id}

Note the that if there are overlapping column definitions (e.g., the primary key id), you must define which table to select the id from. The resulting table is a combination of the two and only includes the columns requested — for example, if the {id} passed was 1:

id
name
skill
1
fish
swim

What’s great is that with all of this querying & composing, Tableland returns a response that can conform to the end needs, such as something like a JSON object or CSV list. This is especially useful with NFTs, as an example, which can leverage a JOIN to “extend” it with additional functionality defined in some separate table — see

for more detail.