Запросы FROM и JOIN

Оператор FROM указывает источник данных, на которых будет выполняться оставшаяся часть запроса. Логически, оператор FROM — это точка начала выполнения запроса. Оператор FROM может содержать одну таблицу, комбинацию нескольких таблиц, соединенных с помощью операторов JOIN, или другой SELECT запрос внутри подзапроса. DuckDB также имеет необязательный синтаксис FROM-сначала, который позволяет выполнять запросы без оператора SELECT.

Примеры

Выбор всех столбцов из таблицы table_name.

SELECT *
FROM table_name;

Выбор всех столбцов из таблицы с использованием синтаксиса FROM-сначала:

FROM table_name
SELECT *;

Выбор всех столбцов с использованием синтаксиса FROM-сначала и опуская оператор SELECT.

FROM table_name;

Выбор всех столбцов из таблицы table_name через псевдоним tn.

SELECT tn.*
FROM table_name tn;

Выбор всех столбцов из таблицы table_name в схеме schema_name.

SELECT *
FROM schema_name.table_name;

Выбор столбца i из функции таблицы range, где первый столбец функции диапазона переименован в i:

SELECT t.i
FROM range(100) AS t(i);

Выбор всех столбцов из CSV-файла test.csv:

SELECT *
FROM 'test.csv';

Выбор всех столбцов из подзапроса:

SELECT *
FROM (SELECT * FROM table_name);

Выбор всего ряда таблицы как структуры:

SELECT t
FROM t;

Выбор всего ряда подзапроса как структуры (т.е., одного столбца):

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;

Объединение двух таблиц:

SELECT *
FROM table_name
JOIN other_table
  ON table_name.key = other_table.key;

Выбор 10%-ной выборки из таблицы:

SELECT *
FROM table_name
TABLESAMPLE 10%;

Выбор выборки из 10 строк из таблицы:

SELECT *
FROM table_name
TABLESAMPLE 10 ROWS;

Использование синтаксиса FROM-сначала с оператором WHERE и агрегацией:

FROM range(100) AS t(i)
SELECT sum(t.i)
WHERE i % 2 = 0;

Объединения

Объединения — это фундаментальная реляционная операция, используемая для горизонтального соединения двух таблиц или отношений. Отношения называются левой и правой сторонами объединения в зависимости от того, как они написаны в операторе объединения. Каждая строка результата содержит столбцы из обоих отношений.

Объединение использует правило для сопоставления пар строк из каждого отношения. Часто это предикат, но есть и другие подразумеваемые правила, которые могут быть указаны.

Внешние объединения

Строки, не имеющие соответствий, всё равно могут быть возвращены, если указано OUTER объединение. Внешние объединения могут быть следующими:

LEFT (Все строки из левого отношения появляются хотя бы один раз)
RIGHT (Все строки из правого отношения появляются хотя бы один раз)
FULL (Все строки из обоих отношений появляются хотя бы один раз)

Объединение, которое не OUTER является INNER (возвращаются только строки, которые были сопоставлены).

Когда возвращается несопоставленная строка, атрибуты из другой таблицы устанавливаются в NULL.

Декартово произведение

Простейший тип объединения — CROSS JOIN. Для этого типа объединения нет условий, и он просто возвращает все возможные пары.

Возврат всех пар строк:

SELECT a.*, b.*
FROM a
CROSS JOIN b;

Это эквивалентно опущению оператора JOIN:

SELECT a.*, b.*
FROM a, b;

Условные объединения

Большинство объединений задаются предикатом, который связывает атрибуты с одной стороны с атрибутами с другой стороны. Условия могут быть явно указаны с помощью оператора ON в объединении (более понятно) или подразумеваться оператором WHERE (старинный способ).

Используем таблицы l_regions и l_nations из схемы TPC-H.

CREATE TABLE l_regions (
    r_regionkey INTEGER NOT NULL PRIMARY KEY,
    r_name      CHAR(25) NOT NULL,
    r_comment   VARCHAR(152)
);

CREATE TABLE l_nations (
    n_nationkey INTEGER NOT NULL PRIMARY KEY,
    n_name      CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment   VARCHAR(152),
    FOREIGN KEY (n_regionkey) REFERENCES l_regions(r_regionkey)
);

Возвращение регионов для стран:

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r ON (n_regionkey = r_regionkey);

Если имена столбцов одинаковые и должны быть равны, то можно использовать более простой синтаксис USING:

CREATE TABLE l_regions (regionkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        comment   VARCHAR(152));

CREATE TABLE l_nations (nationkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        regionkey INTEGER NOT NULL,
                        comment   VARCHAR(152),
                        FOREIGN KEY (regionkey) REFERENCES l_regions(regionkey));

Возвращение регионов для стран:

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r USING (regionkey);

Выражения не должны быть равенствами — можно использовать любой предикат:

Возврат пар задач, одна из которых выполнялась дольше, но стоила меньше:

SELECT s1.t_id, s2.t_id
FROM west s1, west s2
WHERE s1.time > s2.time
  AND s1.cost < s2.cost;

Естественные объединения

Естественные объединения соединяют две таблицы на основе атрибутов с одинаковыми именами.

Например, рассмотрим пример с городами, кодами аэропортов и названиями аэропортов. Обратите внимание, что обе таблицы умышленно неполные, т.е. они не содержат совпадающей пары в другой таблице.

CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR);
CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR);
INSERT INTO city_airport VALUES
    ('Amsterdam', 'AMS'),
    ('Rotterdam', 'RTM'),
    ('Eindhoven', 'EIN'),
    ('Groningen', 'GRQ');
INSERT INTO airport_names VALUES
    ('AMS', 'Amsterdam Airport Schiphol'),
    ('RTM', 'Rotterdam The Hague Airport'),
    ('MST', 'Maastricht Aachen Airport');

Для объединения таблиц по общим атрибутам IATA выполните:

SELECT *
FROM city_airport
NATURAL JOIN airport_names;

Это даёт следующий результат:

city_name	iata	airport_name
Amsterdam	AMS	Amsterdam Airport Schiphol
Rotterdam	RTM	Rotterdam The Hague Airport

Обратите внимание, что в результате включены только строки, где тот же атрибут iata присутствовал в обеих таблицах.

Мы также можем выразить запрос, используя оператор JOIN с ключевым словом USING:

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

Получение и исключение

Получение возвращает строки из левой таблицы, у которых есть хотя бы одно соответствие в правой таблице. Исключение возвращает строки из левой таблицы, у которых нет соответствий в правой таблице. При использовании получение или исключения, результат никогда не будет содержать больше строк, чем левая таблица. Получение обеспечивает ту же логику, что и оператор IN оператор. Исключение обеспечивает ту же логику, что и оператор NOT IN оператор, за исключением того, что при исключении игнорируются NULL значения из правой таблицы.

Пример получение

Возврат списка пар город–код аэропорта из таблицы city_airport, где название аэропорта имеется в таблице airport_names:

SELECT *
FROM city_airport
SEMI JOIN airport_names
    USING (iata);

city_name	iata
Amsterdam	AMS
Rotterdam	RTM

Этот запрос эквивалентен:

SELECT *
FROM city_airport
WHERE iata IN (SELECT iata FROM airport_names);

Пример исключения

Возврат списка пар город–код аэропорта из таблицы city_airport , где название аэропорта отсутствует в таблице airport_names:

SELECT *
FROM city_airport
ANTI JOIN airport_names
    USING (iata);

city_name	iata
Eindhoven	EIN
Groningen	GRQ

Этот запрос эквивалентен:

SELECT *
FROM city_airport
WHERE iata NOT IN (SELECT iata FROM airport_names WHERE iata IS NOT NULL);

Латеральные объединения

Ключевое слово LATERAL позволяет подзапросам в операторе FROM ссылаться на предыдущие подзапросы. Эта функция также известна как латеральное объединение.

SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

i	j
0	1
2	3
1	2

Латеральные объединения являются обобщением коррелированных подзапросов, поскольку они могут возвращать несколько значений на одно входное значение, а не только одно.

SELECT *
FROM
    generate_series(0, 1) t(i),
    LATERAL (SELECT i + 10 UNION ALL SELECT i + 100) t2(j);

i	j
0	10
1	11
0	100
1	101

Полезно рассматривать LATERAL как цикл, в котором мы итерируемся по строкам первого подзапроса и используем его в качестве входных данных для второго (LATERAL) подзапроса. В примерах выше мы итерируемся по таблице t и ссылаемся на её столбец i из определения таблицы t2. Строки из t2 формируют столбец j в результате.

Можно ссылаться на несколько атрибутов из подзапроса LATERAL.

CREATE TABLE t1 AS
    SELECT *
    FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

SELECT *
    FROM t1, LATERAL (SELECT i + j) t2(k)
    ORDER BY ALL;

i	j	k
0	1	1
1	2	3
2	3	5

DuckDB определяет, когда следует использовать LATERAL объединения, что делает использование ключевого слова LATERAL необязательным.

Позиционные объединения

При работе с таблицами или другими вложенными таблицами одного размера строки могут иметь естественное соответствие на основе их физического порядка. В языках сценариев это легко выражается с помощью цикла:

for (i = 0; i < n; i++) {
    f(t1.a[i], t2.b[i]);
}

Это сложно выразить на стандартном SQL, потому что реляционные таблицы не упорядочены, но импортированные таблицы, такие как таблицы данных или файлы на диске (например, CSV или Parquet), имеют естественный порядок.

Соединение их с использованием этого порядка называется позиционным объединением:

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (s VARCHAR);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES ('a'), ('b');

SELECT *
FROM t1
POSITIONAL JOIN t2;

x	s
1	a
2	b
3	NULL

Позиционные соединения всегда являются FULL OUTER соединениями, т. е. отсутствующие значения (последние значения в более коротком столбце) устанавливаются в NULL.

Соединения «по состоянию на»

Обычная операция при работе с временными или упорядоченными аналогичным образом данными — это поиск ближайшего (первого) события в таблице ссылок (например, цен). Это называется соединением «по состоянию на»:

Присоединение цен к сделкам со стоком:

SELECT t.*, p.price
FROM trades t
ASOF JOIN prices p
       ON t.symbol = p.symbol AND t.when >= p.when;

Соединение «по состоянию на» требует как минимум одного неравенства в условии упорядочивания. Неравенство может быть любым условием неравенства (>=, >, <=, <) для любого типа данных, но наиболее распространённой формой является >= для временного типа. Все остальные условия должны быть равенствами (или NOT DISTINCT). Это означает, что порядок таблиц слева/справа имеет значение.

ASOF соединяет каждую строку слева с не более чем одной строкой справа. Его можно задать как OUTER соединение, чтобы найти неспаренные строки (например, сделки без цен или цены без сделок).

Присоединение цен или NULL к сделкам со стоком:

SELECT *
FROM trades t
ASOF LEFT JOIN prices p
            ON t.symbol = p.symbol
           AND t.when >= p.when;

Соединения «по состоянию на» также могут задавать условия соединения по совпадающим именам столбцов с помощью синтаксиса USING, но последним атрибутом в списке должно быть неравенство, которое будет больше или равно (>=):

SELECT *
FROM trades t
ASOF JOIN prices p USING (symbol, "when");

Возвращает символ, trades.when, price (но НЕ prices.when):

Если вы комбинируете USING с SELECT * таким образом, запрос вернёт значения столбцов слева (запрос) для совпадений, а не значения столбцов справа (строка). Чтобы получить prices времена в примере, вам нужно явно указать столбцы:

SELECT t.symbol, t.when AS trade_when, p.when AS price_when, price
FROM trades t
ASOF LEFT JOIN prices p USING (symbol, "when");

Самосоединения

DuckDB позволяет самосоединения для всех типов соединений. Обратите внимание, что таблицы необходимо алиасить, использование одного имени таблицы без алиасов приведёт к ошибке:

CREATE TABLE t(x int);
SELECT * FROM t JOIN t USING(x);

Binder Error: Duplicate alias "t" in query!

Добавление алиасов позволяет запросу успешно проанализироваться:

SELECT * FROM t AS t t1 JOIN t t2 USING(x);

Синтаксис с `FROM`-первым

SQL DuckDB поддерживает синтаксис с FROM-первым, т. е. позволяет поместить FROM предложение перед SELECT предложением или полностью опустить SELECT предложение. Мы используем следующий пример для его демонстрации:

CREATE TABLE tbl AS
    SELECT *
    FROM (VALUES ('a'), ('b')) t1(s), range(1, 3) t2(i);

Синтаксис с `FROM`-первым и `SELECT` предложением

Следующее утверждение демонстрирует использование синтаксиса с FROM-первым:

FROM tbl
SELECT i, s;

Это эквивалентно:

SELECT i, s
FROM tbl;

i	s
1	a
2	a
1	b
2	b

Синтаксис с `FROM`-первым без `SELECT` предложения

Следующее утверждение демонстрирует использование необязательного SELECT предложения:

FROM tbl;

Это эквивалентно:

SELECT *
FROM tbl;

s	i
a	1
a	2
b	1
b	2