MySQL - SELECT，JOIN [英] MySQL - SELECT, JOIN

问题描述

几个月前，我正在编程一个简单的应用程序与其他人在PHP。在这里，我们需要从多个表中基于userid和另一个值，从userid选择的行中获取一个SELECT。

Few months ago I was programming a simple application with som other guy in PHP. There we needed to preform a SELECT from multiple tables based on a userid and another value that you needed to get from the row that was selected by userid.

我的第一个想法是创建多个SELECT并解析PHP脚本中的所有输出（所有的mysql_num_rows（）和类似的函数用于检查），但是那个家伙告诉我他会这样做。 好的没问题！我想，只有更少的我写。那么，当我发现他只用一个SQL语句做了这个惊喜：

My first idea was to create multiple SELECTs and parse all the output in the PHP script (with all that mysql_num_rows() and similar functions for checking), but then the guy told me he'll do that. "Okay no problem!" I thought, just much more less for me to write. Well, what a surprise when i found out he did it with just one SQL statement:

SELECT
  d.uid AS uid, p.pasmo_cas AS pasmo, d.pasmo AS id_pasmo ...
FROM
  table_values AS d, sectors AS p
WHERE
  d.userid='$userid' and p.pasmo_id=d.pasmo
ORDER BY
  datum DESC, p.pasmo_id DESC

$ b b

（缩写语句（...））

(shortened piece of the statement (...))

此外，任何引用这两个解释和例子会很方便（不是从MySQL ref - ）

Also any references to explanations and examples of these two would come in pretty handy (not from the MySQL ref though - I'm really a novice in this kind of stuff and it's written pretty roughly there.)

推荐答案

这是一个新手，

, notation was replaced in ANSI-92 standard, and so is in one sense now 20 years out of date.

此外，在执行OUTER时，在ANSI-92标准中替换了符号， JOIN和其他更复杂的查询， JOIN 符号更加明确，可读，并且（在我看来）可调试。

Also, when doing OUTER JOINs and other more complex queries, the JOIN notation is much more explicit, readable, and (in my opinion) debuggable.

作为一般原则，避免，并使用 JOIN 。

As a general principle, avoid , and use JOIN.

在优先级方面，JOIN的ON子句发生在WHERE子句之前。这允许 a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL 来检查<$ c中没有匹配行的情况$ c> b 。

In terms of precedence, a JOIN's ON clause happens before the WHERE clause. This allows things like a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL to check for cases where there is NOT a matching row in b.

使用，符号与处理WHERE ON条件。

Using , notation is similar to processing the WHERE and ON conditions at the same time.

这篇关于MySQL - SELECT，JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！