SQL -是子查询还是不子查询？

Question

我很难用大量的记录来做速度测试。有人能告诉我在处理数百万条记录时哪个查询应该更快吗？我最喜欢Tomas的查询，但是当处理一个更大的表时，不同的不是会使它慢很多吗？

我惊讶地发现，我的子查询并没有像我想象的那样慢下来。

*我的。在00.0640419 > 00.1030255秒内产生22行。

SELECT
    [planning].[id] as planningId,
    [planning].[type] as planningType,
    [planning].[from] as planningFrom,
    [planning].[till] as planningTill,
    [worker].[intId] as workerId,
    [worker].[name] as workerName,
    [site].[intId] as siteId,
    [site].[name] as siteName
FROM
    [worker]
    LEFT JOIN [planning] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
    LEFT JOIN [site] ON [planning].[siteId] = [site].[intId] AND [planning].[companyId] = [site].[companyId]
WHERE 
    [worker].[companyId] = 2
    AND ( [planning].[id] IS NULL  OR  ( [planning].[from] <= '2014-04-30' AND [planning].[till] >= '2014-04-01') )
    AND ([worker].[intId] IN (
        SELECT 
          [worker].[intId] 
        FROM 
          [planning]
          INNER JOIN [worker] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
        WHERE
          [worker].[companyId] = 2
          AND ([planning].[type] = 'absent' OR ([planning].[siteId] IN ('7710122')))
      ) OR [worker].[intId] IN ('7701260')
  )

* Sirko。在00.0684108 > 00.0955292秒内产生22行。

SELECT
    [planning].[id] as planningId,
    [planning].[type] as planningType,
    [planning].[from] as planningFrom,
    [planning].[till] as planningTill,
    [worker].[intId] as workerId,
    [worker].[name] as workerName,
    [site].[intId] as siteId,
    [site].[name] as siteName
FROM
    [worker]
    LEFT JOIN [planning] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
    LEFT JOIN [site] ON [planning].[siteId] = [site].[intId] AND [planning].[companyId] = [site].[companyId]
    LEFT JOIN ( SELECT DISTINCT
                    [worker].[intId] 
                FROM 
                    [planning]
                    LEFT JOIN [worker] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
                WHERE
                    [worker].[companyId] = 2
                    AND ([planning].[type] = 'absent' OR ([planning].[type] = 'site' AND [planning].[siteId] IN ('7710122')))
               ) AS filter ON filter.[intId] = [worker].[intId]
WHERE 
    [worker].[companyId] = 2
    AND ( ( [planning].[from] <= '2014-04-30' AND [planning].[till] >= '2014-04-01') OR [worker].[intId] IN ('7701260') )

* Tomas Pastircak。在00.0674178 > 00.0850567秒内产生22行。

SELECT DISTINCT
    [planning].[id] as planningId,
    [planning].[type] as planningType,
    [planning].[from] as planningFrom,
    [planning].[till] as planningTill,
    [worker].[intId] as workerId,
    [worker].[name] as workerName,
    [site].[intId] as siteId,
    [site].[name] as siteName
FROM
    [worker]
    LEFT JOIN [planning] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
    LEFT JOIN [site] ON [planning].[siteId] = [site].[intId] AND [planning].[companyId] = [site].[companyId]
    LEFT JOIN [planning] p2 ON p2.[workerId] = [worker].[intId] AND p2.[companyId] = [worker].[companyId]
WHERE 
    [worker].[companyId] = 2
        AND ( [planning].[id] IS NULL  OR  ( [planning].[from] <= '2014-04-30' AND [planning].[till] >= '2014-04-01') )
    AND (p2.[type] = 'absent' OR p2.[siteId] IN ('7710122') OR [worker].[intId] IN ('7701260'))

Answer 1

您为什么不直接将它添加到WHERE条件中，而不使用所有的子查询内容呢？它似乎返回相同的数据..。

SELECT
...
FROM
...
LEFT JOIN [planning] p2 ON p2.[workerId] = [worker].[intId] AND p2.[companyId] = [worker].[companyId]

WHERE 
    [worker].[companyId] = 2
    AND ( [planning].[id] IS NULL  OR  ( [planning].[from] <= '2014-04-30' AND [planning].[till] >= '2014-04-01') )
    AND (p2.[type] = 'absent' OR (p2.[type] = 'site' AND p2.[siteId] IN ('7710122'))

Answer 2

我不太熟悉the语法，但下面这样的内容应该可以工作：

SELECT
    [planning].[id] as planningId,
    [planning].[type] as planningType,
    [planning].[from] as planningFrom,
    [planning].[till] as planningTill,
    [planning].[busyMon] as busyMon,
    [planning].[busyTue] as busyTue,
    [planning].[busyWed] as busyWed,
    [planning].[busyThu] as busyThu,
    [planning].[busyFri] as busyFri,
    [planning].[busySat] as busySat,
    [planning].[busySun] as busySun,
    [planning].[busyHolidays] as busyHolidays,
    [worker].[intId] as workerId,
    [worker].[name] as workerName,
    [worker].[partner] as workerPartner,
    [site].[intId] as siteId,
    [site].[name] as siteName
FROM
    [worker]
    LEFT JOIN [planning] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
    LEFT JOIN [site] ON [planning].[siteId] = [site].[intId] AND [planning].[companyId] = [site].[companyId]
    LEFT JOIN ( SELECT DISTINCT
                    [worker].[intId] 
                FROM 
                    [planning]
                    LEFT JOIN [worker] ON [planning].[workerId] = [worker].[intId] AND [planning].[companyId] = [worker].[companyId]
                WHERE
                    [worker].[companyId] = 2
                    AND ([planning].[type] = 'absent' OR ([planning].[type] = 'site' AND [planning].[siteId] IN ('7710122','123456')))
               ) AS filter ON filter.[intId] = [worker].[intId] OR [worker].[intId] IN ('987654','654321')
WHERE 
    [worker].[companyId] = 2
      AND ( [planning].[id] IS NULL  OR  ( [planning].[from] <= '2014-04-30' AND [planning].[till] >= '2014-04-01') )   

Answer 3

这不是一个完整的答案，但我的观点并没有很好地表达在评论中，所以我会在这里提出。

在我看来，您的大子查询似乎是错误的，首先：

SELECT worker.intId 
  FROM planning
       LEFT JOIN 
       worker ON planning.workerId = worker.intId 
             AND planning.companyId = worker.companyId
 WHERE worker.companyId = 2
   AND (   planning.type = 'absent' 
        OR (    planning.type = 'site' 
            AND planning.siteId IN ('7710122','123456')
           )
       )

与worker.companyId =2相结合的从计划到工作的左连接将导致内部连接。

你能用语言澄清你试图用子查询完成的任务吗？