sql - JOIN ON SQL 语法

Question

我正在上我的第一个数据库类(class)，需要一些关于我需要更改哪些内容才能让我的脚本进行处理的信息。目前我有 2 个表，一个“订单”表和一个“客户”表，这就是它们的编码方式。

CREATE TABLE customers (
  customer_id           INT          ,
  customer_first_name   VARCHAR(20),
  customer_last_name    VARCHAR(20)     NOT NULL,
  customer_address      VARCHAR(50)    NOT NULL,
  customer_city         VARCHAR(20)     NOT NULL,
  customer_state        CHAR(2)         NOT NULL,
  customer_zip          CHAR(5)     NOT NULL,
  customer_phone        CHAR(10)     NOT NULL,
  customer_fax          CHAR(10),
  CONSTRAINT customers_pk 
    PRIMARY KEY (customer_id)
);

CREATE TABLE order (
  order_id          INT         NOT NULL,
  customer_id       INT         NOT NULL,
  order_date        DATE        NOT NULL,
  shipped_date      DATE,
  employee_id       INT,
  CONSTRAINT orders_pk
    PRIMARY KEY (order_id),
  CONSTRAINT orders_fk_customers
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
  CONSTRAINT orders_fk_employees
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

我将这两个表连接在一起的脚本如下:

SELECT or.order_id, or.order_date, or.customer_city,
    cu.customer_first_name, cu.customer_last_name
  FROM orders or INNER JOIN customers cu
  ON or.customer_first_name = cu.customer_first_name
  AND or.customer_last_name = cu.customer_last_name;

现在很明显，我知道它是不正确的并且可能有多个错误，所以要温和。我很想知道我能做些什么来让它发挥作用。请指教。

Answer 1

两件事:

• 不要使用或作为别名。那是一个保留字。

• 通过外键连接。

查询应该如下所示:

SELECT o.order_id, o.order_date, cu.customer_city, 
    cu.customer_first_name, cu.customer_last_name
  FROM orders o
  INNER JOIN customers cu 
    on o.customer_id = cu.customer_id