Postgresql Join

PostgreSQL JOIN: 联合多个表中的数据

欢迎回到我们的PostgreSQL教程系列！在本课中，我们将探讨JOIN，PostgreSQL中的一个强大功能，它允许您基于相关列将来自两个或多个表的行组合在一起。理解JOIN对于有效查询关系数据库是至关重要的。

JOIN简介

在PostgreSQL中，JOIN子句用于基于它们之间的相关列将来自两个或更多个表的行组合。当需要检索分布在多个表中的数据时，JOIN是基础。

基本语法

SELECT columns
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;
  • SELECT：指定要检索的列。
  • FROM：指定主表。
  • JOIN：将另一个表中的行组合在一起。
  • ON：指定匹配行的条件。

PostgreSQL中的JOIN类型

PostgreSQL支持几种类型的JOIN：

  • 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，其中books中的author列与authors中的name列匹配。
  • 只返回在两个表中匹配的作者的行。

2. LEFT JOIN

LEFT JOIN返回左表中的所有行以及右表中匹配的行。右表中未匹配的行将显示为NULL值。

示例：检索所有书籍及其作者，包括在authors表中未找到匹配作者的书籍

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 JOIN的结果。
  • 所有书籍和作者都被列出，未匹配的地方用NULL表示。

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）

自连接是一个常规的连接，但该表是与自身连接。

示例：查找价格相同的书籍

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与自身连接，条件是价格相同但书名不同。
  • 常用于在同一表中查找重复项或相关记录。

使用USING子句的JOIN

当两个表共享同名列时，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时需谨慎，因为它可能意外地基于您不打算匹配的列进行连接。

结论

PostgreSQL中的JOIN对于在多个表中查询数据是必不可少的。通过掌握JOIN，您可以编写复杂的查询，从您的关系数据中获取有价值的见解。

关键点总结：

  • INNER JOIN：返回两个表中匹配的行。
  • LEFT JOIN：返回左表中的所有行以及右表中匹配的行。
  • RIGHT JOIN：返回右表中的所有行以及左表中匹配的行。
  • FULL OUTER JOIN：当表的一侧有匹配时返回所有行。
  • CROSS JOIN：返回两个表的笛卡尔积。
  • 使用表别名以提高可读性。
  • 使用NATURAL JOIN和USING子句时要谨慎。

Schema Initialization

要运行提供的示例，请确保您的PostgreSQL数据库使用以下模式进行设置：

-- 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');

请随意尝试这些示例并修改查询以加深对PostgreSQL JOIN的理解。祝您查询愉快！

Postgresql InsertPostgresql Select