JOIN: Объединение таблиц

PostgreSQL JOIN: Объединение данных из нескольких таблиц

Добро пожаловать обратно в нашу серию учебных пособий по PostgreSQL! В этом уроке мы рассмотрим JOINs, мощную функцию PostgreSQL, которая позволяет объединять строки из двух или более таблиц на основе связанных столбцов. Понимание JOINs необходимо для эффективного выполнения запросов в реляционных базах данных.

Введение в JOINs

В PostgreSQL используется предложение JOIN для объединения строк из двух или более таблиц на основе связанного столбца между ними. JOINs являются основополагающими, когда требуется извлекать данные, распределённые по нескольким таблицам.

Основной синтаксис

SELECT columns
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;
  • SELECT: Указывает столбцы, которые вы хотите извлечь.
  • FROM: Указывает основную таблицу.
  • JOIN: Объединяет строки с другой таблицей.
  • ON: Указывает условие для сопоставления строк.

Типы JOINs в PostgreSQL

PostgreSQL поддерживает несколько типов JOINs:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN

Давайте подробно рассмотрим каждый тип с примерами.

1. INNER JOIN

INNER JOIN возвращает строки, когда есть совпадение в обеих таблицах.

Пример: Извлечение книг вместе с именами авторов

Предположим, у нас есть таблица books и таблица authors. Чтобы получить названия книг вместе с именами их авторов:

SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author = authors.name;

Смотреть инициализацию схемы

Пояснение:

  • Мы соединяем books и authors, где столбец author в books совпадает со столбцом name в таблице authors.
  • Возвращаются только строки с совпадающими авторами в обеих таблицах.

2. LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы и совпадающие строки из правой таблицы. Несовпадающие строки в правой таблице приводят к значениям NULL.

Пример: Извлечение всех книг и их авторов, включая книги без соответствующего автора в таблице авторов

SELECT books.title, authors.name
FROM books
LEFT JOIN authors ON books.author = authors.name;

Пояснение:

  • Возвращаются все книги.
  • Если автор книги не найден в таблице authors, то authors.name будет NULL.

3. RIGHT JOIN

RIGHT JOIN возвращает все строки из правой таблицы и совпадающие строки из левой таблицы. Несовпадающие строки в левой таблице приводят к значениям NULL.

Пример: Извлечение всех авторов и их книг, включая авторов, которые не написали ни одной книги

SELECT books.title, authors.name
FROM books
RIGHT JOIN authors ON books.author = authors.name;

Пояснение:

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

4. FULL OUTER JOIN

FULL OUTER JOIN возвращает все строки, когда есть совпадение хотя бы в одной из таблиц. Несовпадающие строки имеют значения NULL для столбцов из таблицы без совпадения.

Пример: Извлечение всех книг и авторов, включая несовпадающие записи с обеих сторон

SELECT books.title, authors.name
FROM books
FULL OUTER JOIN authors ON books.author = authors.name;

Пояснение:

  • Объединяются результаты обоих LEFT и RIGHT JOINs.
  • Возвращаются все книги и авторы, с NULLs там, где нет совпадений.

5. CROSS JOIN

CROSS JOIN возвращает декартово произведение строк из присоединяемых таблиц. Оно объединяет каждую строку первой таблицы со всеми строками второй таблицы.

Пример: Генерация всех возможных комбинаций книг и фильмов

Предположим, у нас есть таблица movies:

SELECT books.title AS book_title, movies.title AS movie_title
FROM books
CROSS JOIN movies;

Пояснение:

  • Каждое название книги объединяется с каждым названием фильма.
  • Полезно для генерации комбинаций или тестирования.

Объединение нескольких таблиц

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

Пример: Извлечение названий книг, авторов и любых связанных фильмов

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

-- Предположим, существует таблица book_movies с колонками book_title и movie_title
 
SELECT books.title, authors.name, movies.title
FROM books
INNER JOIN authors ON books.author = authors.name
INNER JOIN book_movies ON books.title = book_movies.book_title
INNER JOIN movies ON book_movies.movie_title = movies.title;

Пояснение:

  • Мы соединяем books с authors, затем с book_movies и movies.
  • Возвращаются только записи с совпадающими элементами во всех таблицах.

Использование псевдонимов таблиц

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

Пример: Использование псевдонимов для улучшения читаемости

SELECT b.title, a.name
FROM books AS b
INNER JOIN authors AS a ON b.author = a.name;
  • b и a — это псевдонимы для таблиц books и authors соответственно.

Self JOIN

Self JOIN — это обычный JOIN, при котором таблица соединяется сама с собой.

Пример: Поиск книг с одинаковой ценой

SELECT b1.title AS book1, b2.title AS book2, b1.price
FROM books AS b1
INNER JOIN books AS b2 ON b1.price = b2.price AND b1.title <> b2.title;

Пояснение:

  • Мы соединяем таблицу books с самой собой, где цены равны, но названия разные.
  • Полезно для поиска дубликатов или связанных записей в одной таблице.

JOIN с USING

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

Пример: Упрощение условия соединения

SELECT books.title, authors.name
FROM books
INNER JOIN authors USING (name);

Примечание: В нашей схеме у таблицы books есть столбец author, а у таблицы authors — name. Так как имена столбцов различаются, USING неприменима, если только имена столбцов не совпадают.

NATURAL JOIN

NATURAL JOIN автоматически объединяет таблицы по столбцам с одинаковыми именами.

Пример: Натуральное соединение между таблицами с общими именами столбцов

-- Если обе таблицы имели столбец с именем 'author'
 
SELECT *
FROM books
NATURAL JOIN authors;

Внимание: Используйте NATURAL JOIN осторожно, так как он может непреднамеренно объединять столбцы, которые вы не планировали сопоставлять.

Заключение

JOINs в PostgreSQL важны для выполнения запросов к данным из нескольких таблиц. Освоив JOINs, вы сможете писать сложные запросы, предоставляющие ценную информацию из ваших реляционных данных.

Основные моменты:

  • INNER JOIN: Возвращает совпадающие строки из обеих таблиц.
  • LEFT JOIN: Возвращает все строки из левой таблицы и совпадающие строки из правой таблицы.
  • RIGHT JOIN: Возвращает все строки из правой таблицы и совпадающие строки из левой таблицы.
  • FULL OUTER JOIN: Возвращает все строки, когда есть совпадение хотя бы в одной таблице.
  • CROSS JOIN: Возвращает декартово произведение обеих таблиц.
  • Используйте псевдонимы таблиц для удобочитаемости.
  • Будьте осторожны с NATURAL JOIN и клаузулой USING.

Schema Initialization

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('1984', 'George Orwell', 8.99, 328),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy');
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');

Не стесняйтесь экспериментировать с примерами и изменять запросы, чтобы углубить свои знания о JOINs в PostgreSQL. Удачи вам в запросах!

WHERE: Фильтрация данныхGROUP BY: Агрегация данных