0Pricing
Supabase Backend as a Service · Lesson

Advanced SQL Queries & Joins

Master complex SQL queries, including various types of joins, subqueries, and window functions to retrieve sophisticated datasets.

Advanced SQL Queries & Joins is a free Supabase Backend as a Service lesson on CoddyKit — lesson 1 of 3. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Supabase Backend as a Service learning path, one of 3 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Beyond Basic Queries

Welcome to Advanced SQL Queries! So far, you've learned to select, filter, and sort data. But real-world applications often need to combine data from multiple sources or perform complex calculations.

This lesson will equip you with powerful techniques to retrieve sophisticated datasets, making your Supabase applications even smarter.

Joins Recap: Inner Join

Let's quickly refresh our memory on joins. A JOIN combines rows from two or more tables based on a related column between them.

An INNER JOIN returns only the rows where there is a match in both tables. If a row in one table doesn't have a match in the other, it's excluded.

Try running this example to set up our tables and see an INNER JOIN:

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  department_id INT REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering'),
(4, 'HR');

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES
(101, 'Alice', 'Smith', 1),
(102, 'Bob', 'Johnson', 2),
(103, 'Charlie', 'Brown', 1),
(104, 'Diana', 'Prince', 3),
(105, 'Eve', 'Adams', NULL);

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
INNER JOIN
  departments d ON e.department_id = d.department_id;

Left Join: All from the Left

What if you want to see all employees, even those not assigned to a department yet? That's where LEFT JOIN (or LEFT OUTER JOIN) comes in handy.

A LEFT JOIN returns all rows from the 'left' table (the first one mentioned) and the matching rows from the 'right' table. If there's no match on the right, NULL values are returned for the right table's columns.

Run this to see employee Eve Adams, who has no department:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
LEFT JOIN
  departments d ON e.department_id = d.department_id;

Right Join: All from the Right

The opposite of a LEFT JOIN is a RIGHT JOIN (or RIGHT OUTER JOIN). It returns all rows from the 'right' table and matching rows from the 'left' table.

If there's no match on the left, NULL values are returned for the left table's columns. This is less common, as you can often rewrite it as a LEFT JOIN by swapping table order.

Let's find all departments, including 'HR' which currently has no employees:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
RIGHT JOIN
  departments d ON e.department_id = d.department_id;

Full Outer Join: Everything!

Want to see everything? A FULL OUTER JOIN (or just FULL JOIN) returns all rows when there is a match in either the left or the right table.

If a row doesn't have a match in the other table, the columns from the non-matching side will have NULL values. It's like combining a LEFT and a RIGHT join.

Observe how both Eve (no department) and HR (no employees) appear:

SELECT
  e.first_name,
  e.last_name,
  d.department_name
FROM
  employees e
FULL OUTER JOIN
  departments d ON e.department_id = d.department_id;

Self Join: Table to Itself

Sometimes, you need to join a table to itself. This is called a SELF JOIN and is useful for finding relationships within the same table, like 'employees and their managers'.

To do this, you use table aliases to treat the same table as two separate entities in your query.

Let's add a manager column and find out who manages whom:

ALTER TABLE employees
ADD COLUMN manager_id INT REFERENCES employees(employee_id);

UPDATE employees SET manager_id = 101 WHERE employee_id = 102;
UPDATE employees SET manager_id = 101 WHERE employee_id = 103;
UPDATE employees SET manager_id = 104 WHERE employee_id = 105;

SELECT
  E.first_name AS employee_name,
  M.first_name AS manager_name
FROM
  employees E
INNER JOIN
  employees M ON E.manager_id = M.employee_id;

Introducing Subqueries

Beyond joins, subqueries (also called inner queries or nested queries) are another powerful tool. A subquery is simply a SQL query nested inside a larger query.

They can be used to:

  • Filter data in a WHERE clause.
  • Define columns in a SELECT clause.
  • Create derived tables in a FROM clause.

Subqueries execute first, and their result is then used by the outer query.

Subqueries in WHERE Clause

A common use for subqueries is in the WHERE clause to filter results dynamically. You can use operators like IN, EXISTS, =, <, > with subqueries.

For example, let's find all employees who work in the 'Sales' department without knowing the department_id beforehand:

SELECT
  first_name, last_name
FROM
  employees
WHERE
  department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
  );

Scalar Subqueries in SELECT

A scalar subquery is a subquery that returns a single value (one row, one column). These are often used in the SELECT clause to add a calculated value to each row of the main query.

Let's find each employee's name and also show the total number of employees in their department. This demonstrates how a subquery can compute a value for each row.

SELECT
  e.first_name,
  e.last_name,
  d.department_name,
  (SELECT COUNT(*)
   FROM employees
   WHERE department_id = e.department_id) AS dept_employee_count
FROM
  employees e
LEFT JOIN
  departments d ON e.department_id = d.department_id;

Advanced Queries Challenge

Consider a scenario where you want to list all departments, and for each department, show the names of employees working there. If a department has no employees, it should still appear in the list with NULL for employee names.

Which SQL JOIN type is most appropriate for this task?

Recap: Your SQL Superpowers

You've gained some serious SQL superpowers today!

  • Joins: Beyond INNER JOIN, you learned about LEFT, RIGHT, and FULL OUTER JOINs to handle different data inclusion needs.
  • Self Join: How to join a table to itself for hierarchical data.
  • Subqueries: Nesting queries to filter data (WHERE clause) or compute scalar values (SELECT clause).

These techniques are fundamental for building powerful and flexible data retrieval logic in your Supabase projects. Keep practicing!

Frequently asked questions

Is the “Advanced SQL Queries & Joins” lesson free?

Yes — the full text of “Advanced SQL Queries & Joins” is free to read here on the web, and the Supabase Backend as a Service course includes 3 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Supabase Backend as a Service course, upgrade to CoddyKit PRO.

What will I learn in “Advanced SQL Queries & Joins”?

Master complex SQL queries, including various types of joins, subqueries, and window functions to retrieve sophisticated datasets. You practise Supabase Backend as a Service with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Supabase Backend as a Service?

No prior experience is required. Supabase Backend as a Service on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 3, so you can start here or from the beginning and move at your own pace.

How long does the “Advanced SQL Queries & Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Supabase Backend as a Service lesson?

Yes. Every Supabase Backend as a Service lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Advanced SQL Queries & Joins
  2. Database Indexing for Performance
  3. Database Functions and Triggers
← Back to Supabase Backend as a Service