什么是适当的交叉表的SQL查询语法吗?What is the proper syntax for a cross-table SQL query?

- 此内容更新于:2014-12-30
主题:

现在,我拥有的 这并不提供一个语法错误,但是价值观,它应该返回行(s),它只是返回0行。 我想做的就是让gp_id gp。keyword_id列关键字,关键字表是一个特定值和practice_link是实践表对应gp_id我有,这是存储在表的id列。

原文:

Right now, I have

SELECT gp_id FROM gp.keywords 
WHERE keyword_id = 15 
AND (SELECT practice_link FROM gp.practices 
     WHERE practice_link IS NOT NULL 
     AND id = gp_id)

This does not provide a syntax error, however for values where it should return row(s), it just returns 0 rows.

What I'm trying to do is get the gp_id from gp.keywords where the the keywords table keyword_id column is a specific value and the practice_link is the practices table corresponds to the gp_id that I have, which is stored in the id column of that table.

解决方案:
我甚至不确定这是有效的SQL,因此我很惊讶它是工作: 这个怎么样: 选择kw.gp_id p.practice_link 从全科医生。关键字为千瓦 内部加入全科医生。实践作为p 在P。id = kw.gp_id kw.keyword_id = 15 我会避开隐式连接在另一个例子。它只会导致以后眼泪。
原文:

I'm not even sure that is valid SQL, so I'm surprised it is working at all:

SELECT gp_id
FROM gp.keywords
WHERE keyword_id = 15
    AND (SELECT practice_link FROM gp.practices WHERE practice_link IS NOT NULL AND id = gp_id)

How about this instead:

SELECT kw.gp_id, p.practice_link
FROM gp.keywords AS kw
INNER JOIN gp.practices AS p
    ON p.id = kw.gp_id
WHERE kw.keyword_id = 15

I would steer clear of implicit joins as in the other examples. It only leads to tears later.

解决方案:
原文:


select k.gp_id 
from gp.keywords as k,
     gp.practices as p
where
keyword_id=15
and practice_link is not null
and p.id=k.gp_id

Guido García的回复:5秒的速度比我:)我# 39;想知道如果有任何优势做p。id = k。gp_id最后还是做作为第一个where子句。

(原文:5 seconds faster than me :) I'd like to know if there is any advantage on doing p.id = k.gp_id at the end vs. doing it as the first where clause.)

Cade Roux的回复:通常会没有影响,优化器将处理它,但是检查你的执行计划。

(原文:Will usually make no difference, as the optimizer will handle it, but check your execution plans.)

SquareCog的回复:我们甚至略表名相同的!不应该有差异在现代数据库管理系统,使用基于成本,没有订单,优化。可能会有差异,如果你使用一个加入条款相反,不过这# 39;年代也主要是语法,而不是功能。

(原文:We even abbreviated the table names the same! There should be no difference on modern DBMSes, which use cost-based, not order-based, optimizers. There may be a difference if you use a join clause instead, but that's also mostly syntactic, not functional.)

StingyJack的回复:@Guido - where子句的顺序绝对可以对性能产生影响。由于加入,加入条件应该进入FROM子句允许优化器选择最好的计划,并使其更易于阅读。

(原文:@Guido - ordering of the where clause can definitely make a difference in performance. Since there is a join, the join condition should be moved into the FROM clause to allow the optimizer to choose the best plan, and make it easier to read.)

Cade Roux的回复:@StingyJack——我经常搬东西到加入条款内加入然而,根据我的经验,优化器总是聪明anough有效做到这一点已经在执行计划。

(原文:@StingyJack - I do often move things into the JOIN clause on INNER JOINs however, in my experience, the optimizer is always smart anough to effectively do that already in the execution plan.)

解决方案:
原文:
SELECT k.gp_id
FROM gp.keywords k, gp.practices p
WHERE 
   p.id = k.gp_id.AND
   k.keyword_id = 15 AND
   p.practice_link is not null
解决方案:
原文:
SELECT g.gp_id, p.practice_link FROM gp.keywords g, gp.practices p 
WHERE
g.keyword_id = 15 AND p.practice_link IS NOT NULL AND p.id = g.gp_id