英文:
why different SQL JOIN return the same DB
问题
Here's the translated content you requested:
我在Codewars上练习SQL。有一个任务需要使用JOIN。
其中一个答案是：
SELECT companies.*, products.*, companies.name AS company_name
FROM companies
INNER JOIN products ON companies.id = products.company_id;
我认为我们先选取公司，然后是产品（它们的列），最后得到数据库。
但我尝试了这段代码：
SELECT products.*, companies.name AS company_name
FROM products
INNER JOIN companies ON company_id = companies.id;
结果却相同。JOIN是如何工作的？在FROM后面输入什么很重要吗？SELECT中的顺序重要吗？
英文:
I training SQL on codewars. And there is task, where you need to use join
The one of answers was:
SELECT companies.*, products.*, companies.name AS company_name
FROM companies
inner join products ON companies.id=products.company_id;
And i think, that we take companies, then products(their columns) and get db
But i tried this code:
SELECT products.*, companies.name AS company_name
from products
inner join companies on company_id=companies.id;
And got the same result.
How work JOIN? Is it important, what we type after FROM? Is order in SELECT important?
答案1
得分: 1
内连接操作是_可交换的_，就像算术中的+或*一样。像A JOIN B这样连接表与B JOIN A得到的结果相同。
像PostgreSQL这样的数据库软件可能会选择以与您在查询中指定的顺序不同的方式连接表格。PostgreSQL包含一个聪明的查询优化器，解释您的SQL查询并以其认为最有效的方式执行它。
SQL的美妙之一在于它更多或更少是_声明性_的，因此您编写查询作为对所需结果的抽象描述，而不是应该执行的方式。当然，也有例外情况——没有编程语言是完美的理论实现——但这是一般原则。
关于PostgreSQL如何处理这一点的详细信息，请阅读https://www.postgresql.org/docs/current/explicit-joins.html。
英文:
The inner join operation is commutative, like + or * in arithmetic. Joining tables like A JOIN B gives the same result as B JOIN A.
Database software like PostgreSQL may choose to join the tables in a different order than you specified them in your query. PostgreSQL includes a clever query optimizer that interprets your SQL query and executes it in the way it thinks will be most efficient.
Part of the beauty of SQL is that it is more or less declarative, so you write queries as an abstract description of the result you want, not the way it should be executed. There are exceptions to this—no programming language is a perfect implementation of the theory behind it—but that's the general principle.
For details on how PostgreSQL handles this, read https://www.postgresql.org/docs/current/explicit-joins.html
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论