DataJoint for SQL Users¶

This tutorial maps SQL concepts to DataJoint for users with relational database experience. You'll see:

• How DataJoint syntax corresponds to SQL
• What DataJoint adds beyond standard SQL
• When to use each approach

Prerequisites: Familiarity with SQL (SELECT, JOIN, WHERE, GROUP BY).

Setup¶

Schema Definition¶

SQL¶

CREATE TABLE Researcher (
    researcher_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (researcher_id)
);

CREATE TABLE Subject (
    subject_id INT NOT NULL,
    species VARCHAR(32) NOT NULL,
    sex ENUM('M', 'F', 'unknown'),
    PRIMARY KEY (subject_id)
);

CREATE TABLE Session (
    subject_id INT NOT NULL,
    session_date DATE NOT NULL,
    researcher_id INT NOT NULL,
    notes VARCHAR(255),
    PRIMARY KEY (subject_id, session_date),
    FOREIGN KEY (subject_id) REFERENCES Subject(subject_id),
    FOREIGN KEY (researcher_id) REFERENCES Researcher(researcher_id)
);

DataJoint¶

Key Differences¶

Insert Sample Data¶

Query Comparison¶

SELECT * FROM table¶

WHERE — Restriction (&)¶

Restriction by Query (Subqueries)¶

In SQL, you often use subqueries to filter based on another table:

-- Sessions with mice only
SELECT * FROM Session 
WHERE subject_id IN (SELECT subject_id FROM Subject WHERE species = 'mouse')

In DataJoint, you simply restrict by another query — no special subquery syntax needed:

Semantic Matching¶

A fundamental difference between SQL and DataJoint is semantic matching — the principle that attributes acquire meaning through foreign key relationships, and all binary operators use this meaning to determine how tables combine.

The Problem with SQL¶

SQL requires you to explicitly specify how tables connect:

SELECT * FROM Session 
JOIN Subject ON Session.subject_id = Subject.subject_id;

This is verbose and error-prone. Nothing prevents you from joining on unrelated columns that happen to share a name, or accidentally creating a Cartesian product when tables have no common columns.

Experienced SQL programmers learn to always join through foreign key relationships. DataJoint makes this the default and enforced behavior.

How Semantic Matching Works¶

In DataJoint, when you declare a foreign key with -> Subject, the subject_id attribute in your table inherits its meaning from the Subject table. This meaning propagates through the foreign key graph.

Semantic matching means: all binary operators (*, &, -, +, .aggr()) match attributes based on shared meaning — those connected through foreign keys. If two tables have no semantically matching attributes, the operation raises an error rather than silently producing incorrect results.

One Join Operator Instead of Many¶

SQL has multiple join types (INNER, LEFT, RIGHT, FULL OUTER, CROSS) because it must handle arbitrary column matching. DataJoint's single join operator (*) is sufficient because semantic matching is more restrictive than SQL's natural joins:

• SQL natural joins match on all columns with the same name — which can accidentally match unrelated columns
• DataJoint semantic joins match only on attributes connected through foreign keys — and raise an error if you attempt to join on attributes that shouldn't be joined

This catches errors at query time rather than producing silently incorrect results.

Algebraic Closure¶

In standard SQL, query results are just "bags of rows" — they don't have a defined entity type. You cannot know what kind of thing each row represents without external context.

DataJoint achieves algebraic closure: every query result is a valid entity set with a well-defined entity type. You always know what kind of entity the result represents, identified by a specific primary key. This means:

1. Every operator returns a valid relation — not just rows, but a set of entities of a known type
2. Operators compose indefinitely — you can chain any sequence of operations
3. Results remain queryable — a query result can be used as an operand in further operations

The entity type (and its primary key) is determined by precise rules based on the operator and the functional dependencies between operands. See the Primary Keys specification for details.

SELECT columns — Projection (.proj())¶

JOIN¶

GROUP BY — Aggregation (.aggr())¶

NOT IN — Negative Restriction (-)¶

Combined Example¶

Operator Reference¶

What DataJoint Adds¶

DataJoint is not just "Python syntax for SQL." It adds:

1. Table Tiers¶

Tables are classified by their role in the workflow:

2. Automatic Computation¶

Computed tables have a make() method that runs automatically. An important principle: make() should only fetch data from upstream tables — those declared as dependencies in the table definition.

In SQL, you'd need triggers, stored procedures, or external scheduling to achieve this.

3. Cascading Deletes¶

DataJoint enforces referential integrity with automatic cascading:

4. Schema as Workflow¶

The diagram shows the computational workflow, not just relationships:

• Green = Manual (input)
• Red = Computed (derived)
• Arrows show dependency/execution order

5. Object Storage Integration¶

Store large objects (arrays, files) with relational semantics:

class Recording(dj.Imported):
    definition = """
    -> Session
    ---
    raw_data : <blob>       # NumPy array stored in database
    video : <blob@storage>  # Large file stored externally
    """

SQL has no standard way to handle this.

When to Use Raw SQL¶

DataJoint generates SQL under the hood. Sometimes raw SQL is useful:

Summary¶

DataJoint uses SQL databases (MySQL/PostgreSQL) underneath but provides:

• Pythonic syntax for queries
• Workflow semantics for scientific pipelines
• Automatic computation via populate()
• Object storage for large scientific data