I was trying to full join to tables as the code below,
SELECT C.cust_name,O.ord_num
FROM customers C
FULL JOIN orders O
ON C.cust_code = O.cust_code;
but this code is not working.Is anyone can solve this?
4 Answers
You can use:
SELECT C.cust_name, O.ord_num
FROM customers C
LEFT JOIN orders O
ON C.cust_code = O.cust_code
UNION ALL
SELECT null, O.ord_num
FROM orders O
LEFT JOIN customers C
ON C.cust_code = O.cust_code
WHERE C.cust_code IS NULL;
Comments
FULL OUTER JOIN result without FULL OUTER JOIN support:
SELECT C.cust_name,O.ord_num
FROM customers C
LEFT JOIN orders O
ON C.cust_code = O.cust_code
UNION ALL
SELECT NULL, O.ord_num
FROM orders O
WHERE NOT EXISTS (select 1 from customers C where C.cust_code = O.cust_code)
Comments
UNION ALL & LEFT JOIN will be equivalent :
SELECT C.cust_name, O.ord_num
FROM customers C LEFT JOIN
orders O
ON C.cust_code = O.cust_code
UNION ALL -- Use UNION if you don't want duplicate
SELECT C.cust_name, O.ord_num
FROM orders O LEFT JOIN
customers C
ON C.cust_code = O.cust_code;
2 Comments
jarlh
Will return duplicates for the matching rows.
Jeewantha Lahiru
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.cust_code' at line 8 this error was shown upYou are using Full Join in your Query, Correct Query will be:
SELECT C.cust_name,O.ord_num
FROM customers C
FULL OUTER JOIN orders O
ON C.cust_code = O.cust_code;
There is no such Join FULL JOIN, Correct is FULL OUTER JOIN
1 Comment
jarlh
FULL JOIN is the same as FULL OUTER JOIN, since the OUTER keyword is optional. However, MySQL doesn't support it.
full joinfor this? Isn't each order related to one and and only one customer?