mysql - 为什么我收到此错误 - 字段列表中的未知列 'customers.c_id'

Question

我收到这个错误字段列表中的未知列“customers.c_id”当我在表上使用 FULL JOIN 命令时

我有两个表

1-客户 2-订单

Host: localhost
Database: abcd
    SQL query: SELECT * FROM `customers` LIMIT 0, 30 ; 
Rows: 6

c_id    c_name  city    pincode
1   gagan   ghaziabad   201005
2   raman   delhi       20200
3   manish  punjab      10123
4   vinay   jammu       31201
5   praveen kerala      40001
6   raghav  goa         10200


Host: localhost
Database: abcd
    SQL query: SELECT * FROM `orders` LIMIT 0, 30 ; 
Rows: 4

orderId c_id    orderDate
1102    5   2014-02-05
1109    2   2015-01-15
1012    3   2016-03-10
1117    7   2017-05-25

我的 sql 命令是:-

    SELECT customers.c_id,orders.orderId,customers.c_name
FROM customers
FULL JOIN orders
ON
customers.c_id=order.c_id;

sql command error

错误:-

ERROR 1054 <42S22>: Unknown column 'customers.c_id' in 'field list'

Answer 1

由于 MySQL 不支持 FULL JOIN，您需要使用 LEFT JOIN UNION 您的查询和 RIGHT JOIN。

SELECT c.c_id, o.orderId, c.c_name
FROM customers c
LEFT JOIN orders o ON c.c_id = o.c_id
UNION ALL
SELECT c.c_id, o.orderId, c.c_name
FROM customers c
RIGHT JOIN orders o ON c.c_id = o.c_id;