SQL完全加入语法问题

Question

I'm trying to do a full join in SQL but I keep getting this error:

You have an error in your SQL syntax; check the ma…yroll P ON A.project_id = P.project_id' at line 1

This is the query. I can't figure out what is wrong!

SELECT A.quantity, A.unit_price, P.paid_amount,  FROM assets A FULL JOIN payroll P ON A.project_id = P.project_id

Thanks!

Answer 1

FULL JOIN is almost never necessary. Why not just do this?

SELECT A.quantity, A.unit_price, P.paid_amount
FROM assets A LEFT JOIN
     payroll P
     ON A.project_id = P.project_id;

Or, if you might have different projects in each table:

SELECT a.quantity, a.unit_price, pr.paid_amount
FROM projects p LEFT JOIN
     assets a
     ON a.project_id = p.project_id LEFT JOIN
     payroll pr
     ON a.project_id = p.project_id
WHERE a.project_id IS NOT NULL or pr.project_id IS NOT NULL;

(Presumably if you have project_id, then you have a projects table with one row per project.)

Even in databases that support FULL JOIN, the two LEFT JOINs is likely to have better performance than the FULL JOIN.