Understanding SQL vs. TypeQL

This guide helps SQL users understand TypeQL by mapping familiar SQL constructs to their TypeQL equivalents.

TypeQL operates on a data model using entity, relation, and attribute types (and their instances), instead of tables and rows. This difference means that some SQL concepts map directly and others have different formulations in TypeQL.

Schema setup

Let’s use a small schema for people, companies, and employments.

If this schema were implemented in a typical SQL database, you’d define two main tables (person and company) with attributes for each, and a join table (employment) to represent the relationship between people and companies.

Each person has a name, email, and age. Each company has a name. The employment table links people to companies using foreign keys.

Example SQL table definitions:

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name VARCHAR,
  email VARCHAR,
  age INTEGER
);

CREATE TABLE company (
  id SERIAL PRIMARY KEY,
  name VARCHAR
);

CREATE TABLE employment (
  id SERIAL PRIMARY KEY,
  employee_id INTEGER REFERENCES person(id),
  employer_id INTEGER REFERENCES company(id)
);

Here’s the equivalent TypeQL schema:

#!test[schema]
define
  attribute name, value string;
  attribute email, value string;
  attribute age, value integer;
  relation employment, relates employee, relates employer;
  entity person, owns name, owns email, owns age, plays employment:employee;
  entity company, owns name, plays employment:employer;

If you’re testing as you go - here’s some sample data you can use to populate your TypeDB database!

#!test[write, commit]
insert
  $alice isa person, has name "Alice", has email "alice@example.com", has age 30;
  $bob isa person, has name "Bob", has email "bob@example.com", has age 25;
  $charlie isa person, has name "Charlie", has email "charlie@example.com", has age 35;
  $acme isa company, has name "Acme Corp";
  $e1 isa employment, links (employee: $alice, employer: $acme);

Data retrieval

SELECT → match + fetch/select

In SQL, SELECT retrieves data from tables. In TypeQL, match finds data by pattern, and fetch or select projects the results.

SQL:

SELECT name, email FROM person;

TypeQL:

In TypeQL, use select to filter out unwanted data. This is a filtering operation, and does not set ordering of result variables.

#!test[read, count=3]
match
  $p isa person, has name $name, has email $email;
select $name, $email;	# eliminates $p

If you want your data to be returned in JSON, you can format it through a fetch clause, which also works as a projection:

#!test[read, count=3]
match
  $p isa person, has name $name;
fetch {
  "name": $name,
  "email": $p.email  # projection in the fetch clause
};

WHERE → match

SQL’s WHERE clause filters rows. In TypeQL, constraints are part of the match pattern.

SQL:

SELECT name FROM person WHERE age > 25;

TypeQL:

#!test[read, count=2]
match
  $p isa person, has name $name, has age $age;
  $age > 25;
select $name;

Shorthand:

#!test[read, count=2]
match
  $p isa person, has name $name, has age > 25;
select $name;

AND/OR conditions in WHERE clauses

SQL uses AND (conjunction) and OR (disjunction) in WHERE clauses.

In TypeQL, statements and composite patterns are combined an implicit and by default: ; and , both are implicit conjunctions. An or pattern is used to explicitly represent a disjunction.

SQL with AND:

SELECT name FROM person WHERE age > 25 AND email LIKE '%@example.com';

TypeQL:

#!test[read, count=2]
match
  $p isa person, has name $name, has age $age, has email $email;
  $age > 25;
  $email like ".*@example.com";
select $name;

SQL with OR:

SELECT name FROM person WHERE age < 20 OR age > 30;

TypeQL:

#!test[read, count=1]
match
  $p isa person, has name $name, has age $age;
  { $age < 20; } or { $age > 30; };
select $name;

JOIN → relation patterns

SQL uses JOIN to combine data from multiple tables. In TypeQL, relations naturally connect entities through patterns. The type of JOIN determines which entities are included in the results when relationships don’t exist.

INNER JOIN (or JOIN)

An INNER JOIN returns only rows where there is a match in both tables. In TypeQL, this is the default behavior when matching relations.

SQL:

SELECT p.name, e.id
FROM person p
INNER JOIN employment e ON p.id = e.employee_id;

TypeQL:

#!test[read, count=1]
match
  $p isa person, has name $pname;
  $e isa employment, links (employee: $p);
select $pname, $e;

This returns only persons who have an employment relation attached.

LEFT JOIN

A LEFT JOIN returns all rows from the left table (person), and matching rows from the right table (employment). If there’s no match, NULL values are returned for the right table columns. In TypeQL, use the try construct for optional matching.

SQL:

SELECT p.name, e.id
FROM person p
LEFT JOIN employment e ON p.id = e.employee_id;

TypeQL:

#!test[read, count=3]
match
  $p isa person, has name $pname;
  try {
    $e isa employment, links (employee: $p);
  };
select $pname, $e;

This returns all persons, with their employment if they have one. Persons without employment will still appear in the results (with $e unbound).

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table (employment), and matching rows from the left table (person). If there’s no match, NULL values are returned for the left table columns. In TypeQL, reverse the pattern to start from the right side.

SQL:

SELECT p.name, e.id
FROM person p
RIGHT JOIN employment e ON p.id = e.employee_id;

TypeQL:

#!test[read, count=1]
match
  $e isa employment;
  try {
    $p isa person, has name $pname;
    $e links (employee: $p);
  };
select $pname;

This returns employments' employee names if they exist. Employments without matching persons will still appear in the results (with $pname unbound, set to None).

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables - every row from the first table combined with every row from the second table. In TypeQL, match entities from both types independently without requiring any relation between them.

SQL:

SELECT p.name, e.id
FROM person p
CROSS JOIN employment e;

TypeQL:

#!test[read, count=3]
match
  $p isa person, has name $pname;
  $e isa employment;
select $pname, $e;

This returns every combination of person and employment, regardless of whether they have any relationship. The result set size is the product of the number of persons and employments.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, with NULL values where there’s no match.

Full outer joins do not translate well to TypeQL. If your application requires an equivalent of a full outer join, please contact us!

Data modification

INSERT → insert

SQL:

INSERT INTO person (name, email, age) VALUES ('David', 'david@example.com', 28);

TypeQL:

#!test[write, commit, count=1]
insert
  $p isa person, has name "David", has email "david@example.com", has age 28;

SQL INSERT with SELECT:

INSERT INTO employment (employee_id, employer_id)
SELECT p.id, c.id
FROM person p, company c
WHERE p.name = 'Alice' AND c.name = 'Acme Corp';

TypeQL:

#!test[write, commit, count=1]
match
  $p isa person, has name "Alice";
  $c isa company, has name "Acme Corp";
insert
  $e isa employment, links (employee: $p, employer: $c);

UPDATE → update

SQL:

UPDATE person SET email = 'newemail@example.com' WHERE name = 'Alice';

TypeQL:

#!test[write, count=1]
match
  $p isa person, has name "Alice";
update $p has email "newemail@example.com";

The update clause in TypeQL works only for attributes with cardinality @card(0..1) or @card(1). It automatically deletes the old value and inserts the new one.

DELETE → delete

SQL:

DELETE FROM person WHERE name = 'Charlie';

TypeQL:

#!test[write, rollback, count=1]
match
  $p isa person, has name "Charlie";
delete $p;

SQL DELETE with JOIN:

DELETE e FROM employment e
INNER JOIN person p ON e.employee_id = p.id
WHERE p.name = 'Alice';

TypeQL:

#!test[write, rollback, count=2]
match
  $p isa person, has name "Alice";
  $e isa employment, links (employee: $p);
delete $e;

Aggregation and grouping

GROUP BY → reduce with groupby

SQL:

SELECT age, COUNT(*) as count
FROM person
GROUP BY age;

TypeQL:

#!test[read, count=4]
match
  $p isa person, has age $age;
reduce $count = count groupby $age;

Aggregate Functions

SQL:

SELECT
  COUNT(*) as total,
  AVG(age) as avg_age,
  MAX(age) as max_age,
  MIN(age) as min_age,
  SUM(age) as sum_age
FROM person;

TypeQL:

#!test[read, count=1]
match
  $p isa person, has age $age;
reduce
  $total = count,
  $avg_age = mean($age),
  $max_age = max($age),
  $min_age = min($age),
  $sum_age = sum($age);

HAVING → reduce + match

SQL:

SELECT age, COUNT(*) as count
FROM person
GROUP BY age
HAVING COUNT(*) > 1;

TypeQL:

#!test[read, count=0]
match
  $p isa person, has age $age;
reduce $count = count groupby $age;
match $count > 1;

Sorting and pagination

ORDER BY → sort

SQL:

SELECT name, age FROM person ORDER BY age DESC, name ASC;

TypeQL:

#!test[read, count=4]
match
  $p isa person, has name $name, has age $age;
sort $age desc, $name asc;
select $name, $age;

LIMIT and OFFSET

SQL:

SELECT name FROM person ORDER BY name LIMIT 10 OFFSET 20;

TypeQL:

In TypeQL, we call stages like limit/offset (also, select, sort) stream operators, since they only manipulate the answer stream and don’t access the database. Order of stages matter!

#!test[read, count=0]
match
  $p isa person, has name $name;
sort $name;
offset 20;
limit 10;
select $name;

Here we take the match answers, sort them, skip the first 20 with offset, the take the first 10.

If we did limit, then order, we would limit the sorted answers to the first then, and then try to skip the first 20 with offset - resulting in 0 answers every time!

Advanced operations

DISTINCT → distinct

SQL:

SELECT DISTINCT age FROM person;

TypeQL:

#!test[read, count=4]
match
  $p isa person, has age $age;
select $age;
distinct;

Subqueries → functions

SQL:

SELECT name FROM person
WHERE age > (SELECT AVG(age) FROM person);

TypeQL:

#!test[read, count=2]
with fun avg_age() -> double:
  match $p isa person, has age $age;
  return mean($age);
match
  $p isa person, has name $name, has age $age;
  let $avg = avg_age();
  $age > $avg;
select $avg, $name, $age;

UNION → or patterns

SQL:

SELECT name FROM person WHERE age < 25
UNION
SELECT name FROM person WHERE age >= 35;

TypeQL:

In TypeQL, you have multiple ways to execute this union using or:

#!test[read, count=1]
match
  $p isa person, has name $name, has age $age;
  { $age < 25; } or { $age >= 35; };
select $name;

#!test[read, count=1]
match
  {
    $p isa person, has name $name, has age < 25;
  } or {
    $p isa person, has name $name, has age >= 35;
  };
select $name;

Both are valid!

EXISTS → not { not { … } }

SQL:

SELECT name FROM person p
WHERE EXISTS (
  SELECT 1 FROM employment e
  WHERE e.employee_id = p.id OR e.employer_id = p.id
);

TypeQL:

#!test[read, count=1]
match
  $p isa person, has name $name;
  not { not { $e isa employment, links (employee: $p); }; };
select $name;

This reads as: we’re looking for a person such that it’s not true that: there is not an employment involving that person.

This will be improved in future versions of TypeQL!

IN → multiple value constraints

SQL:

SELECT name FROM person WHERE age IN (25, 30, 35);

TypeQL:

#!test[read, count=3]
match
  $p isa person, has name $name, has age $age;
  { $age == 25; } or { $age == 30; } or { $age == 35; };
select $name;

TypeQL’s roadmap features lists, which would allow writing the following:

# NOTE: roadmap feature!
match
  $p isa person, has name $name, has age $age;
  $age in [25, 30, 35];
select $name;

LIKE → like (regex)

SQL:

SELECT name FROM person WHERE email LIKE '%@example.com';

TypeQL:

#!test[read, count=4]
match
  $p isa person, has name $name, has email $email;
  $email like ".*@example.com";
select $name;

NULL checks → not { … }

SQL:

SELECT name FROM person WHERE email IS NULL;

TypeQL:

TypeQL doesn’t store NULLs. A value is either existing or absent in the database:

#!test[read, count=0]
match
  $p isa person, has name $name;
  not { $p has email $_; };
select $name;

SQL:

SELECT name FROM person WHERE email IS NOT NULL;

TypeQL:

#!test[read, count=4]
match
  $p isa person, has name $name, has email $_;
select $name;

Note that for optional matches like when using LEFT/RIGHT joins, TypeQL’s try construct can create None values. Unlike null`s in SQL, a `None is not a value that can be stored. It’s also only possible to generated for optional variables (ie. ones generated in try patterns.)

Fundamentally, in SQL a null is an extra value that any column could contain. In TypeQL, a None is a marker given to a variable when it is unset, but it is never used as a value in the data. In other words, any typical value in SQL has the range of possible values of: <values> + null. In TypeQL, variables that are optional can hold Either<None, Value> instead of the typical Value.

Key differences and concepts

No explicit FROM clause

In TypeQL, there’s no FROM clause. The match pattern implicitly defines what data to search through by specifying types and their relationships.

Pattern-based matching

TypeQL uses pattern matching rather than table joins. Relations are first-class citizens that naturally connect entities, making complex queries more intuitive.

Strong typing

TypeQL is strongly typed. Variables are bound to specific types (entities, relations, attributes), and the query engine enforces type safety. Optional variables are known at query compilation time and validated throughout.

Pipeline model

TypeQL queries are pipelines where each clause processes a stream of answers. This allows for powerful composition:

#!test[read, count=1]
match
  $p isa person, has name $name, has age $age;
reduce $avg_age = mean($age);
match $avg_age > 29;

Relations vs foreign keys

In SQL, relationships are represented through foreign keys. In TypeQL, relationships are explicit relations that can have their own attributes and connect multiple entities.

SQL (with foreign keys):

CREATE TABLE employment (
  id INT PRIMARY KEY,
  employee_id INT REFERENCES person(id),
  employer_id INT REFERENCES company(id),
  start_date DATE
);

TypeQL:

define
  entity person plays employment:employee;
  entity company plays employment:employer;
  relation employment, relates employee, relates employer, owns start-date;
  attribute start-date, value datetime;

Summary table

SQL Construct TypeQL Equivalent Notes

SQL Construct	TypeQL Equivalent	Notes
`SELECT`	`match` + `fetch`/`select`	Pattern matching replaces table selection
`FROM`	Implicit in `match` patterns	Types are specified in the pattern
`WHERE`	Constraints in `match`	Filtering is part of the pattern
`JOIN`	Relation patterns	Relations naturally connect entities
`INNER JOIN`	Required relation patterns	Both sides must exist
`LEFT JOIN`	Optional patterns with `try`	Use `try` for optional matches
`RIGHT JOIN`	Optional patterns with `try`	Reverse pattern direction
`CROSS JOIN`	Disconnected match statements
`INSERT`	`insert`	Similar syntax, pattern-based
`UPDATE`	`update`	Only for single-value attributes
`DELETE`	`delete`	Pattern-based deletion
`GROUP BY`	`reduce … groupby`	Aggregation with grouping
`HAVING`	`reduce` + `match`	Filter aggregated results
`ORDER BY`	`sort`	Similar functionality
`LIMIT`	`limit`	Identical
`OFFSET`	`offset`	Identical
`DISTINCT`	`distinct`	Identical
`COUNT`/`SUM`/`AVG`/`etc.`	`reduce` with `count`/`sum`/`mean`/etc.	Similar, assigned to a variable
	`UNION`	`or` patterns
Pattern disjunction	`EXISTS`	`not { not { … } }`
Double negation pattern	`IN`	`or` with multiple `==`
Pattern disjunction	`LIKE`	`like` with regex
Uses regex syntax	`IS NULL`	`not { … has … }`
Negation pattern	`IS NOT NULL`	Implicit in pattern

SELECT

match + fetch/select

Pattern matching replaces table selection

FROM

Implicit in match patterns

Types are specified in the pattern

WHERE

Constraints in match

Filtering is part of the pattern

JOIN

Relation patterns

Relations naturally connect entities

INNER JOIN

Required relation patterns

Both sides must exist

LEFT JOIN

Optional patterns with try

Use try for optional matches

RIGHT JOIN

Optional patterns with try

Reverse pattern direction

CROSS JOIN

Disconnected match statements

INSERT

insert

Similar syntax, pattern-based

UPDATE

update

Only for single-value attributes

DELETE

delete

Pattern-based deletion

GROUP BY

reduce … groupby

Aggregation with grouping

HAVING

reduce + match

Filter aggregated results

ORDER BY

sort

Similar functionality

LIMIT

limit

Identical

OFFSET

offset

Identical

DISTINCT

distinct

Identical

COUNT/SUM/AVG/etc.

reduce with count/sum/mean/etc.

Similar, assigned to a variable

UNION

or patterns

Pattern disjunction

EXISTS

not { not { … } }

Double negation pattern

IN

or with multiple ==

Pattern disjunction

LIKE

like with regex

Uses regex syntax

IS NULL

not { … has … }

Negation pattern

IS NOT NULL

Implicit in pattern

Services

Platform

Tools

By use case

By industry

Learn

Content

Understanding SQL vs. TypeQL

Schema setup

Data retrieval

SELECT → match + fetch/select

WHERE → match

AND/OR conditions in WHERE clauses

JOIN → relation patterns

INNER JOIN (or JOIN)

LEFT JOIN

RIGHT JOIN

CROSS JOIN

FULL OUTER JOIN

Data modification

INSERT → insert

UPDATE → update

DELETE → delete

Aggregation and grouping

GROUP BY → reduce with groupby

Aggregate Functions

HAVING → reduce + match

Sorting and pagination

ORDER BY → sort

LIMIT and OFFSET

Advanced operations

DISTINCT → distinct

Subqueries → functions

UNION → or patterns

EXISTS → not { not { …​ } }

IN → multiple value constraints

LIKE → like (regex)

NULL checks → not { …​ }

Key differences and concepts

No explicit FROM clause

Pattern-based matching

Strong typing

Pipeline model

Relations vs foreign keys

Summary table

References

EXISTS → not { not { … } }

NULL checks → not { … }