Supabase Backend as a Service · Lektion

Erweiterte SQL-Abfragen und Joins

Meistern Sie komplexe SQL-Abfragen einschließlich verschiedener Join-Typen, Unterabfragen und Window Functions, um anspruchsvolle Datensätze abzurufen.

Lektion 1 von 311 Schritte

Erweiterte SQL-Abfragen und Joins ist eine kostenlose Supabase Backend as a Service-Lektion auf CoddyKit. Dies ist Lektion 1 von 3. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Supabase Backend as a Service-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Supabase Backend as a Service-Kurs umfasst insgesamt 3 Lektionen.

Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.

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?

Beantworte es in der interaktiven Lektion

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!

Kostenlos starten

Lerne Supabase Backend as a Service mit einem KI-Tutor — kostenlos

Schreibe und führe echten Code in deinem Browser aus, bekomme sofortige Hilfe von einem 24/7 KI-Tutor und setze dein Lernen im Web oder in der App fort.

Kurse: 11
Lektionen: 40

Kostenlos lernen Den vollständigen Kurs ansehen

Häufig gestellte Fragen

Ist die Lektion „Erweiterte SQL-Abfragen und Joins“ kostenlos?

Ja — der vollständige Text von „Erweiterte SQL-Abfragen und Joins“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Supabase Backend as a Service-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Supabase Backend as a Service-Kurs umfasst insgesamt 3 Lektionen.

Was lerne ich in „Erweiterte SQL-Abfragen und Joins“?

Meistern Sie komplexe SQL-Abfragen einschließlich verschiedener Join-Typen, Unterabfragen und Window Functions, um anspruchsvolle Datensätze abzurufen. Du übst Supabase Backend as a Service mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um Supabase Backend as a Service zu starten?

Keine Vorkenntnisse erforderlich. Supabase Backend as a Service auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 3.

Wie lange dauert die Lektion „Erweiterte SQL-Abfragen und Joins“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser Supabase Backend as a Service-Lektion Code schreiben und ausführen?

Ja. Jede Supabase Backend as a Service-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Erweiterte SQL-Abfragen und Joins
Datenbankindizierung für bessere Leistung
Datenbankfunktionen und Trigger

← Zurück zu Supabase Backend as a Service