关键字'LEFT'附近的语法不正确

Chasing my tail trying to figure this out, googling it just leads to far more complicated examples.

追逐我的尾巴试图解决这个问题,谷歌搜索它只会导致更复杂的例子。

I've joined two tables, which join fine. Prof has asked us to list rows from the orders table where shipped date is NULL, or in the words of the exercise, 'orders that have not shipped yet.'

我加入了两张桌子,加入很好。教授要求我们列出订单表中的行,其中发货日期为NULL,或者在练习的单词中列出“尚未发货的订单”。

Join works fine until I add the IS NULL line and then I get the error: Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'LEFT'.

加入工作正常,直到我添加IS NULL行,然后我得到错误:消息156,级别15,状态1,行13关键字'LEFT'附近的语法不正确。

I've written this 20 different ways and spent the afternoon googling it but can't get rid of the error. I know it's going to be something simple but...

我写了这20种不同的方式,并在下午谷歌搜索它,但无法摆脱错误。我知道它会变得简单但......

SELECT
    customers.customer_id,
    customers.name,
    customers.phone,
    orders.order_id,
    orders.order_date,
    orders.shipped_date
FROM
    orders
WHERE
    orders.shipped_date IS NULL
LEFT OUTER JOIN customers ON customers.customer_id=orders.customer_id

3 个解决方案

#1

There is a defined order of how to compose a query

有一个如何撰写查询的已定义顺序

select
from
join
where
group by
having
order by

You cannot mix that order.

你不能混合那个订单。

#2

LEFT JOIN should come first before the WHERE clause.

LEFT JOIN应该在WHERE子句之前出现。

SELECT  customers.customer_id,
        customers.name,
        customers.phone,
        orders.order_id,
        orders.order_date,
        orders.shipped_date
FROM    orders 
        LEFT OUTER JOIN customers 
            ON customers.customer_id=orders.customer_id
WHERE   orders.shipped_date IS NULL

For additional information about joins, see

有关联接的其他信息,请参阅

Visual Representation of SQL Joins

SQL连接的可视化表示

#3

To clarify LEFT JOIN, Every record on the "LEFT" side (from source) regardless of a match in the right side (joined to)

澄清LEFT JOIN,“LEFT”侧(来自源)的每条记录,无论右侧是否匹配(加入)

So, if you were to SWAP, for example, give me all customers REGARDLESS of having an order on file, you would do something like

所以,如果你是SWAP,例如,给我所有的客户,无论是否有订单存档,你会做类似的事情

Select
      C.Customer_ID,
      C.Name,
      C.Phone,
      O.Order_Date
   from
      Customers C
         LEFT JOIN Orders O
            on C.Customer_ID = O.Customer_ID

would show all customers, and if an order existed, would show that date too.

会显示所有客户,如果存在订单,也会显示该日期。

What you are looking for is just a list of all orders that have not been shipped AND who the order was for. This would be a standers INNER JOIN as you are expecting a match to exist on BOTH sides.

您正在寻找的只是尚未发货的所有订单的清单以及订单的订单。这将是一个标准INNER JOIN,因为你期望两方都存在匹配。

Select
      C.Customer_ID,
      C.Name,
      C.Phone,
      O.Order_Date
   from 
      Orders O
         INNER JOIN Customers C
            on O.Customer_ID = C.Customer_ID
   where
      O.Shipped_Date IS NULL

So the WHERE clause in this case is explicitly on the primary "FROM" table source "Orders" (aliased as just "O"). And you only want those that have NOT shipped, thus only care about O.Shipped_Date is NULL

因此,在这种情况下,WHERE子句显式地在主“FROM”表源“Orders”上(别名为“O”)。而且你只想要那些没有发货的,因此只关心O.Shipped_Date是NULL

#1