0Pricing
Supabase Backend as a Service · Ders

Gelişmiş SQL Sorguları ve Birleştirmeler

Karmaşık SQL sorgularında; çeşitli birleştirme türleri, alt sorgular ve gelişmiş veri kümelerini almak için pencere işlevleri dâhil olmak üzere uzmanlaşın.

Gelişmiş SQL Sorguları ve Birleştirmeler, CoddyKit'te ücretsiz bir Supabase Backend as a Service dersidir. Bu, 3 dersinin 1. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, Supabase Backend as a Service öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. Supabase Backend as a Service kursu toplamda 3 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

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!

Sıkça Sorulan Sorular

“Gelişmiş SQL Sorguları ve Birleştirmeler” dersi ücretsiz mi?

Evet — “Gelişmiş SQL Sorguları ve Birleştirmeler” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve Supabase Backend as a Service kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. Supabase Backend as a Service kursu toplamda 3 dersten oluşur.

“Gelişmiş SQL Sorguları ve Birleştirmeler” dersinde ne öğreneceğim?

Karmaşık SQL sorgularında; çeşitli birleştirme türleri, alt sorgular ve gelişmiş veri kümelerini almak için pencere işlevleri dâhil olmak üzere uzmanlaşın. Supabase Backend as a Service ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

Supabase Backend as a Service öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te Supabase Backend as a Service, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 3 dersinin 1. dersidir.

“Gelişmiş SQL Sorguları ve Birleştirmeler” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu Supabase Backend as a Service dersinde kod yazıp çalıştırabilir miyim?

Evet. Her Supabase Backend as a Service dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

  1. Gelişmiş SQL Sorguları ve Birleştirmeler
  2. Performans için Veritabanı İndeksleme
  3. Veritabanı İşlevleri ve Tetikleyiciler
← Supabase Backend as a Service Sayfasına Dön