---
title: "Relational style joins | SurrealDB University"
description: "Relational style joins. A chapter of SurrealDB Fundamentals, a hands-on course with runnable examples."
url: https://surrealdb.com/learn/fundamentals/relationships/relational-style
---

[Back to Courses](https://surrealdb.com/learn)

Course chapters

[SurrealDB Fundamentals](https://surrealdb.com/learn/fundamentals) [Introduction](https://surrealdb.com/learn/fundamentals) [Welcome to SurrealDB University](https://surrealdb.com/learn/fundamentals/intro/welcome) [Intro to SurrealDB](https://surrealdb.com/learn/fundamentals/intro/surrealdb) [Why SurrealQL is SQL-like](https://surrealdb.com/learn/fundamentals/intro/surrealql) [Part 1: Schemaless CRUD](https://surrealdb.com/learn/fundamentals/schemaless) [Introduction](https://surrealdb.com/learn/fundamentals/schemaless) [Record IDs](https://surrealdb.com/learn/fundamentals/schemaless/record-ids) [Inserting data](https://surrealdb.com/learn/fundamentals/schemaless/inserting-data) [Reading data](https://surrealdb.com/learn/fundamentals/schemaless/reading-data) [Updating data](https://surrealdb.com/learn/fundamentals/schemaless/updating-data) [Deleting data](https://surrealdb.com/learn/fundamentals/schemaless/deleting-data) [Part 2: Adding relationships](https://surrealdb.com/learn/fundamentals/relationships) [Introduction](https://surrealdb.com/learn/fundamentals/relationships) [Graph relations](https://surrealdb.com/learn/fundamentals/relationships/graph-relations) [Record links](https://surrealdb.com/learn/fundamentals/relationships/record-links) [Relational style joins](https://surrealdb.com/learn/fundamentals/relationships/relational-style) [Part 3: Making it schemafull](https://surrealdb.com/learn/fundamentals/schemafull) [Introduction](https://surrealdb.com/learn/fundamentals/schemafull) [Define tables, views and changefeeds](https://surrealdb.com/learn/fundamentals/schemafull/define-table) [Define fields, constraints and assertions](https://surrealdb.com/learn/fundamentals/schemafull/define-fields) [Schemafull CRUD](https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud) [Part 4: Making it secure](https://surrealdb.com/learn/fundamentals/security) [Introduction](https://surrealdb.com/learn/fundamentals/security) [Authentication](https://surrealdb.com/learn/fundamentals/security/authentication) [Query capabilities](https://surrealdb.com/learn/fundamentals/security/query-capabilities) [Part 5: Making it performant](https://surrealdb.com/learn/fundamentals/performance) [Introduction](https://surrealdb.com/learn/fundamentals/performance) [Indexing & data model considerations](https://surrealdb.com/learn/fundamentals/performance/index-data-model) [Deployment & storage layer considerations](https://surrealdb.com/learn/fundamentals/performance/deployment-storage) [Completion](https://surrealdb.com/learn/fundamentals/completion) Certification Pending completion

# Relational style joins

The third and final relationship type we'll explore is relational style joins.

We'll go through

- Semi-joins
- Anti-joins
- Correlated subqueries

Relational style joins is very intentional wording as SurrealDB doesn't do traditional SQL joins.

Instead, we have thought from first principles about what developers need from database relationships to develop easily and scale quickly.

This led us to our primary way of creating relationships at write time to simplify scaling and improve developer experience at query time.

We've covered these in our previous two lessons. Now, we will learn about semi-joins, anti-joins, and correlated subqueries.

Those are fancy terms but don't worry; it will all make sense soon enough.

## Semi-joins

A semi-join is a subquery that filters the outer query with the results of the inner query.

Now that we've got the dictionary definition out of the way, let's get to the exciting stuff by exploring the same query written in 3 different ways in both SQL and SurrealQL.

This will really help you see the difference between a semi-join, correlated subquery and a traditional SQL join.

### `IN` example

As SurrealQL is a SQL-like language, you'll notice that the examples will look very similar. See if you can spot the difference.

### SQL: `IN`

```sql
-- Outer query
SELECT name
FROM product
WHERE id IN (
  -- Inner query
  SELECT in
  FROM product_sku
  WHERE colour = "Black Pink"
)
AND id = "01G0MW4VTG8QZR3A4BTEXHXWS7";
```

### SurrealQL: `IN`

```
-- Outer querySELECT nameFROM product:01G0MW4VTG8QZR3A4BTEXHXWS7WHERE id IN (    -- Inner query    SELECT VALUE in    FROM product_sku    WHERE colour = "Black Pink");
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

In the SQL example since inner queries run before outer queries, it's often good to read subqueries from the inside out.

Starting with us filtering for product sku's in the `product_sku` table that have the colour of “black pink”, then "joining" the `product_sku` table to the `product` table using the `id` and `in` fields.

This then allows us to see the name of the product which has the colour "Black Pink".

In the SurrealQL example, the reason you see the `VALUE` clause added to the `SELECT` statement is because by default SurrealDB returns objects with their field names. Since we want to check if an ID is in an array of IDs, [`SELECT VALUE`](https://surrealdb.com/docs/reference/query-language/statements/select#basic-usage) allows you to return an array of just the values, no field names included.

A key thing to note is that the entirety of the inner query is run before the outer query, which would make it an uncorrelated subquery. This does have some pros and cons, as we'll explore in our next example.

### Correlated subquery example

This is a subquery that filters the outer query with the results of the inner query and is a type of semi-join.

### SQL: correlated subquery

```sql
SELECT name
FROM product
WHERE EXISTS (
  SELECT in FROM product_sku
  WHERE product_sku.in = product.id
  AND colour = "Black Pink"
)
AND id = "01G0MW4VTG8QZR3A4BTEXHXWS7";
```

### SurrealQL: correlated subquery

```
SELECT nameFROM product:01G0MW4VTG8QZR3A4BTEXHXWS7WHERE (    SELECT in FROM product_sku    WHERE in = $parent.id    AND colour = "Black Pink");
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

The key difference here is that the inner query can't run without the outer query, as you can see in the `WHERE` statement.

The defining characteristic of correlated subqueries is that they reference a column in the outer query and execute the subquery once for each row in the outer query.

Now, you might be wondering, why would we do that?

Well, it might be more performant. But as always, it depends.

One of the things it depends on, is how your query optimiser handles your query. It also depends on how large the table of the inner query is and how complicated the logic is.

In our example, the product table is small and only filters on colour, so it's not a big deal to scan the whole table.

The reason why this `WHERE` condition can be more performant is because we are limiting the range of possible records to scan only those relevant to the outer query.

The key thing to remember is that you cannot assume one way is always better, as it depends on the above factors and more.

Therefore, if you need the best performance, it's worth testing both, as the results might surprise you.

## Anti-joins

Anti-joins are still a subquery that filters the outer query with the results of the inner query.

The only difference is that it's the opposite of the queries as seen in the previous examples. Instead of `IN`, it is `NOT IN`, and instead of `EXISTS`, it is `NOT EXISTS`. However, since SurrealDB doesn't use `EXIST` you can instead put an `!` exclamation mark in front of the subquery, to indicate `NOT EXISTS` like in this SurrealQL example.

```
SELECT nameFROM productWHERE !(    SELECT in FROM product_sku    WHERE in = $parent.id    AND colour = "Black Pink");
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

Ok, enough with the subqueries, let's look at how this compares to a normal SQL join.

### `JOIN` example using SQL

```sql
SELECT product.name
FROM product_sku
JOIN product ON product.id = product_sku.in
WHERE product_sku.colour = "Black Pink"
AND product_sku.in = 01G0MW4VTG8QZR3A4BTEXHXWS7
```

Just to reiterate, the `IN`, `EXIST`, and `JOIN` examples all give the same results, they just have a different way of getting there.

### Graph example using SurrealQL

```
SELECT <-product.nameFROM product_sku:01FRVHS08G9WMAHFQTFY82ENXQWHERE colour = "Black Pink"
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

As you can see in this example, things don't need to be complicated.

If you can make relationships at write time, you can completely eliminate the typical complexity. That allows you to avoid errors people make with joins at query time such as not knowing the grain of the data and whether you need a left, right, inner, outer, lateral, or cross join.

## Summary

Let's summarise what we've learned.

Semi-joins, anti-joins and correlated subqueries are all subqueries that filter the outer query with the results of the inner query.

We've shown how to:

- Use the `IN` clause along with `SELECT VALUE` for semi-joins.
- Use `NOT IN` and the exclamation mark `!` instead of `NOT EXISTS` for turning semi-joins into anti-joins.
- Use the `$parent` parameter for correlated subqueries.
- Turn a SQL join into an example with graph relations.

That's everything about relational style joins, I hope you have enjoyed this part on relationships.

Hopefully, this lesson clears up some of the fears of missing out (FOMO) that you might have had about SurrealDB not having traditional SQL joins. See you in Part 3.

Previous

Record links

[Previous](https://surrealdb.com/learn/fundamentals/relationships/record-links)

Next lesson

Part 3: Making it schemafull

[Next lesson](https://surrealdb.com/learn/fundamentals/schemafull)

```json
{"@context":"https://schema.org","@type":"Course","name":"SurrealDB Fundamentals","description":"The most efficient way to learn SurrealDB through guided hands-on learning","url":"https://surrealdb.com/learn/fundamentals","inLanguage":"en","isAccessibleForFree":false,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Welcome to SurrealDB University","url":"https://surrealdb.com/learn/fundamentals/intro/welcome"},{"@type":"LearningResource","name":"Intro to SurrealDB","url":"https://surrealdb.com/learn/fundamentals/intro/surrealdb"},{"@type":"LearningResource","name":"Why SurrealQL is SQL-like","url":"https://surrealdb.com/learn/fundamentals/intro/surrealql"},{"@type":"LearningResource","name":"Part 1: Schemaless CRUD","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Record IDs","url":"https://surrealdb.com/learn/fundamentals/schemaless/record-ids"},{"@type":"LearningResource","name":"Inserting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/inserting-data"},{"@type":"LearningResource","name":"Reading data","url":"https://surrealdb.com/learn/fundamentals/schemaless/reading-data"},{"@type":"LearningResource","name":"Updating data","url":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data"},{"@type":"LearningResource","name":"Deleting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/deleting-data"},{"@type":"LearningResource","name":"Part 2: Adding relationships","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Graph relations","url":"https://surrealdb.com/learn/fundamentals/relationships/graph-relations"},{"@type":"LearningResource","name":"Record links","url":"https://surrealdb.com/learn/fundamentals/relationships/record-links"},{"@type":"LearningResource","name":"Relational style joins","url":"https://surrealdb.com/learn/fundamentals/relationships/relational-style"},{"@type":"LearningResource","name":"Part 3: Making it schemafull","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Define tables, views and changefeeds","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-table"},{"@type":"LearningResource","name":"Define fields, constraints and assertions","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-fields"},{"@type":"LearningResource","name":"Schemafull CRUD","url":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud"},{"@type":"LearningResource","name":"Part 4: Making it secure","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Authentication","url":"https://surrealdb.com/learn/fundamentals/security/authentication"},{"@type":"LearningResource","name":"Query capabilities","url":"https://surrealdb.com/learn/fundamentals/security/query-capabilities"},{"@type":"LearningResource","name":"Part 5: Making it performant","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Indexing \u0026 data model considerations","url":"https://surrealdb.com/learn/fundamentals/performance/index-data-model"},{"@type":"LearningResource","name":"Deployment \u0026 storage layer considerations","url":"https://surrealdb.com/learn/fundamentals/performance/deployment-storage"},{"@type":"LearningResource","name":"Completion","url":"https://surrealdb.com/learn/fundamentals/completion"}]}
```

```json
{"@context":"https://schema.org","@type":"LearningResource","name":"Relational style joins","description":"Relational style joins","url":"https://surrealdb.com/learn/fundamentals/relationships/relational-style","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},"position":17}
```

```json
{"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]}
```

```json
{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Relational style","item":"https://surrealdb.com/learn/fundamentals/relationships/relational-style"}]}
```