这两种连接表方法之间的区别? [英] Difference between these two joining table approaches?

问题描述

假设我们有两个表，Users 和 Posts.user_id 是Posts 表中的外键，是Users 表中的主键.

Consider we have two tables, Users and Posts. user_id is the foreign key in Posts table and is primary key in Users table.

这两个sql查询有什么区别?

Whats the difference between these two sql queries?

select user.name, post.title 
  from users as user, posts as post 
 where post.user_id = user.user_id;

对比

select user.name, post.title 
  from users as user join posts as post using user_id;

推荐答案

除了语法之外，对于小片段，它们的工作方式完全相同.但如果可能，请始终使用 ANSI-JOIN 编写新查询.

Other than syntax, for the small snippet, they work exactly the same. But if at all possible, always write new queries using ANSI-JOINs.

从语义上来说，逗号表示法是用来产生两个表之间的CARTESIAN积，即产生一个A表所有记录和B表所有记录的矩阵，所以有4条记录和6条记录的两张表分别产生24记录.使用 WHERE 子句，您可以从这个笛卡尔积中选择您真正想要的行.然而，MySQL 并没有真正贯彻并制作这个巨大的矩阵，但在语义上这就是它的意思.

As for semantically, the comma notation is used to produce a CARTESIAN product between two tables, which means produce a matrix of all records from table A with all records from table B, so two tables with 4 and 6 records respectively produces 24 records. Using the WHERE clause, you can then pick the rows you actually want from this cartesian product. However, MySQL doesn't actually follow through and make this huge matrix, but semantically this is what it means.

JOIN 语法是 ANSI 标准，它更清楚地定义了表的交互方式.通过将 ON 子句放在 JOIN 旁边，可以清楚地说明是什么将两个表联系在一起.

A JOIN syntax is the ANSI standard that more clearly defines how tables interact. By putting the ON clause next to the JOIN, it makes it clear what links the two tables together.

在功能上，它们将对您的两个查询执行相同的操作.当您开始使用其他 [OUTER] JOIN 类型时，区别就出现了.

Functionally, they will perform the same for your two queries. The difference comes in when you start using other [OUTER] JOIN types.

特别是对于 MySQL，逗号符号确实有一个区别

For MySQL specifically, comma-notation does have one difference

STRAIGHT_JOIN 与 JOIN 类似，只是左表总是在右表之前被读取.这可用于连接优化器将表按错误顺序排列的那些(少数)情况.

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

然而，寄希望于这一点不明智区别.

However, it would not be wise to bank on this difference.

这篇关于这两种连接表方法之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！