About
Protocol
Walkthroughs
Integrations
Intro to NFT Metadata
Tutorials
Smart Contracts
Concepts
Playbooks
Learn
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
← Previous
Next →