OUTER JOIN Syntax error in SQL statement "

Question

Could anyone help me understand why this query is not working?

Modify the query to list all the products (product) and their associated category (product_category) if they have one. Otherwise, do not display a value (null) for the category.

-- SQL request(s)‌‌‌‌‌‌‌‌‌‌‌ below
SELECT P.name as PRODUCT_NAME, PC.name as CATEGORY_NAME
FROM product as P
OUTER JOIN PRODUCT_CATEGORY as PC on P.product_id =  PC.product_category_id

it keeps showing me this error:

Syntax error in SQL statement "                       
SELECT P.NAME AS PRODUCT_NAME, PC.NAME AS CATEGORY_NAME
FROM PRODUCT AS P
OUTER[*] JOIN PRODUCT_CATEGORY AS PC ON P.PRODUCT_ID =  PC.PRODUCT_CATEGORY_ID
"; SQL statement:
-- SQL request(s) below
SELECT P.name as PRODUCT_NAME, PC.name as CATEGORY_NAME
FROM product as P
OUTER JOIN PRODUCT_CATEGORY as PC on P.product_id =  PC.product_category_id
 [42000-200]

Thanks a lot

I don't know. I have passed an assessment on codingame.com, but I think they use MYSQL. — Adouani Riadh, Apr 11 '21 at 23:09
Well you need to know, because the error will be specific to a RDBMS. — Dale K, Apr 11 '21 at 23:10
Possible duplicate: OUTER JOIN isn't a MySQL thing: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — Jerry Jeremiah, Apr 11 '21 at 23:11
As mentioned, 'Outer join' isn't a thing. Someone needs a new recruiter. — Strawberry, Apr 11 '21 at 23:42

score 2 · Accepted Answer · answered Apr 12 '21 at 01:58

OUTER JOIN is not valid syntax in ANSI SQL or any implementation I've used.

Here's a good reference: https://crate.io/docs/sql-99/en/latest/chapters/30.html#joined-tables

You can say JOIN or INNER JOIN. These are synonyms.

You can say LEFT JOIN or LEFT OUTER JOIN. These are synonyms.

You can say RIGHT JOIN or RIGHT OUTER JOIN. These are synonyms.

You can say FULL JOIN or FULL OUTER JOIN. These are synonyms.

You should see the pattern now. The words INNER and OUTER are optional keywords.

OUTER JOIN (without LEFT, RIGHT, or FULL) is not one of the choices.

I suggest to satisfy your recruiter, you use LEFT OUTER JOIN.

Thanks. I have tested your answer and It works like a charm. Thanks for the reference I will used it for my future preparation :) — Adouani Riadh, Apr 12 '21 at 03:16

score 1 · Answer 2 · answered Apr 11 '21 at 23:10

Hmm, I don't know if OUTER JOIN is synonymous with FULL [OUTER] JOIN in any DBMS... So OUTER JOIN should probably read FULL OUTER JOIN or simply FULL JOIN.

But regardless, you don't need a full join here. A full join would list categories without a product. You need a a left join.

SELECT p.name AS product_name,
       pc.name AS category_name
       FROM product AS p
            LEFT JOIN product_category AS pc
                      ON p.product_id = pc.product_category_id;

score 0 · Answer 3 · answered Dec 02 '21 at 10:26

0

as long as category null values are not allowed, then I think you should use inner join

answered Dec 02 '21 at 10:26

Israa

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 02 '21 at 13:11

OUTER JOIN Syntax error in SQL statement "

3 Answers3