0Pricing
Supabase Backend as a Service · Lekcja

Zaawansowane zapytania SQL i złączenia

Opanuj złożone zapytania SQL, w tym różne rodzaje złączeń, podzapytania i funkcje okna, aby pobierać zaawansowane zestawy danych.

Zaawansowane zapytania SQL i złączenia to bezpłatna lekcja Supabase Backend as a Service na CoddyKit. To lekcja 1 z 3. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej Supabase Backend as a Service, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Supabase Backend as a Service zawiera 3 lekcji w sumie.

Części tej lekcji nie zostały jeszcze przetłumaczone i są wyświetlane po angielsku.

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!

Często zadawane pytania

Czy lekcja „Zaawansowane zapytania SQL i złączenia” jest bezpłatna?

Tak — pełny tekst „Zaawansowane zapytania SQL i złączenia” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu Supabase Backend as a Service, przejdź na CoddyKit PRO. Kurs Supabase Backend as a Service zawiera 3 lekcji w sumie.

Co nauczysz się w „Zaawansowane zapytania SQL i złączenia”?

Opanuj złożone zapytania SQL, w tym różne rodzaje złączeń, podzapytania i funkcje okna, aby pobierać zaawansowane zestawy danych. Ćwiczysz Supabase Backend as a Service z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć Supabase Backend as a Service?

Nie wymagamy żadnego doświadczenia. Supabase Backend as a Service w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 3.

Ile czasu zajmuje lekcja „Zaawansowane zapytania SQL i złączenia”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji Supabase Backend as a Service?

Tak. Każda lekcja Supabase Backend as a Service zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Zaawansowane zapytania SQL i złączenia
  2. Indeksowanie bazy danych pod kątem wydajności
  3. Funkcje i wyzwalacze bazy danych
← Powrót do Supabase Backend as a Service