Joins

Joins combine rows from two or more tables based on related columns. Teide supports inner joins, left joins, and cross joins.

Sample Data

The examples on this page use these two tables:

CREATE TABLE employees (
  id INTEGER, name VARCHAR, dept_id INTEGER, salary REAL
);
INSERT INTO employees VALUES
  (1, 'Alice',   10, 95000),
  (2, 'Bob',     20, 72000),
  (3, 'Carol',   10, 105000),
  (4, 'Dave',    30, 68000),
  (5, 'Eve',     NULL, 88000);

CREATE TABLE departments (
  id INTEGER, name VARCHAR, location VARCHAR
);
INSERT INTO departments VALUES
  (10, 'Engineering', 'Building A'),
  (20, 'Marketing',   'Building B'),
  (40, 'Research',     'Building C');

INNER JOIN

Returns only rows that have matching values in both tables. Rows without a match are excluded.

SELECT e.name, e.salary, d.name AS department, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Result:

Dave (dept_id=30, no matching department) and Eve (dept_id=NULL) are excluded. Research (id=40, no matching employees) is also excluded.

LEFT JOIN

Returns all rows from the left table, with matching rows from the right table. If there is no match, the right-side columns are filled with NULL.

SELECT e.name, e.salary, d.name AS department, d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Result:

CROSS JOIN

Returns the Cartesian product of two tables: every row from the left table paired with every row from the right table. No ON clause is used.

SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;

With 5 employees and 3 departments, this produces 15 rows.

Table Aliases

Use short aliases to make join queries more readable. The AS keyword is optional.

-- With AS
SELECT e.name, d.name
FROM employees AS e
INNER JOIN departments AS d ON e.dept_id = d.id;

-- Without AS (equivalent)
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Multi-Table Joins

Chain multiple joins to combine data from several tables.

CREATE TABLE projects (
  id INTEGER, name VARCHAR, dept_id INTEGER, budget REAL
);

SELECT
  e.name AS employee,
  d.name AS department,
  p.name AS project,
  p.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN projects p ON d.id = p.dept_id
ORDER BY e.name;

Joining with Aggregation

-- Department headcount and total salary
SELECT
  d.name AS department,
  d.location,
  COUNT(*) AS headcount,
  SUM(e.salary) AS total_salary,
  AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.name, d.location
ORDER BY total_salary DESC;

Self Joins

A table joined with itself, useful for hierarchical data.

-- Find employees and their managers
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Join with Filtering

-- Combine WHERE with JOIN
SELECT e.name, e.salary, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 80000
  AND d.location = 'Building A'
ORDER BY e.salary DESC;