sql outer join shows the error in mysql workbench

Question

I have two tables country and state:

country
-----------
cid
countrycode
countryname

state
-------
sid
cid (fk)
statecode
statename

I am writing a join as:

SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   state s, 
       country c 
WHERE  s.cid = (+) c.cid 
       AND c.id = 1

The above query gives a syntax error. Does MySQL not accept '+' symbol?

`+` is a non-ANSI standard way of saying [`LEFT JOIN`](http://en.wikipedia.org/wiki/Join_(SQL)#Alternate_syntaxes). — Kermit, Oct 08 '13 at 13:07
@AnkitSharma Just because you never used it, doesn't mean it's wrong. The world doesn't revolve around MySQL. — Kermit, Oct 08 '13 at 13:10
(+) apparently is an atavism from the Jurassic http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation — Mihai, Oct 08 '13 at 13:12
@Mihai Jurassic or not, some are still taught this notation or must use it. — Kermit, Oct 08 '13 at 13:16
@AnkitSharma [*...the + notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place.*](http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation) — Kermit, Oct 08 '13 at 13:18
@Ankit he's saying that some people must use it because they are dealing with legacy code or old-school colleagues. — Aaron Bertrand, Oct 08 '13 at 14:38
And the `WHERE s.cid = (+) c.cid` would yield an error in Oracle as well. The old Oracle syntax would be either `WHERE s.cid = c.cid (+)` (meaning: `s LEFT JOIN c`) or `WHERE s.cid (+) = c.cid` (meaning: `s RIGHT JOIN c`) — ypercubeᵀᴹ, Oct 08 '13 at 14:48

score 3 · Accepted Answer · answered Oct 08 '13 at 13:15

No, MySQL does not accept the + symbol. Aside from a few exceptions, MySQL uses ANSI standard JOIN syntax:

SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   state s 
       LEFT JOIN country c 
              ON c.id = s.cid 
WHERE  c.id = 1

Sashi Kant · Answer 2 · 2013-10-08T13:11:56.723

0

Try this::

select 
s.sid,
s.statename,
c.countryName 
from country c 
LEFT JOIN state s on  s.cid = c.cid 
WHERE c.id=1

edited Oct 08 '13 at 13:11

answered Oct 08 '13 at 12:54

Sashi Kant

`+` is a non-ANSI standard way of saying [`LEFT JOIN`](http://en.wikipedia.org/wiki/Join_(SQL)#Alternate_syntaxes). – Kermit Oct 08 '13 at 13:09

bgs · Answer 3 · 2013-10-08T13:18:16.727

0

Remove plus sign and . symbol at end of your query...

Select s.sid,s.statename,c.countryName from state s,
left join country c on s.cid = c.cid where c.id=1

edited Oct 08 '13 at 13:18

answered Oct 08 '13 at 12:56

bgs

`+` is a non-ANSI standard way of saying [`LEFT JOIN`](http://en.wikipedia.org/wiki/Join_(SQL)#Alternate_syntaxes). – Kermit Oct 08 '13 at 13:08
1

Ok.. But one more , am not recommend a outer join , but user wants to skip from the error, so only i suggest.. Thanks for comment.. – bgs Oct 08 '13 at 13:11

Chamly Idunil · Answer 4 · 2013-10-08T13:31:25.897

0

this will help you.

 SELECT s.sid, 
       s.statename, 
       c.countryname 
FROM   country c 
LEFT JOIN state s on (s.cid = c.cid )
where c.cid = 1;

edited Oct 08 '13 at 13:31

answered Oct 08 '13 at 13:03

Chamly Idunil

`+` is a non-ANSI standard way of saying [`LEFT JOIN`](http://en.wikipedia.org/wiki/Join_(SQL)#Alternate_syntaxes). – Kermit Oct 08 '13 at 13:07

4 Answers4