1

I'm new to MySQL and trying to perform a full outer join operation on 3 tables:

Student:-

usn(Primary key)

name

plays :-

usn(foreign key)

sport_id(foreign key)

sport :-

sport_id(primary key)

sport_name

I want to get names of Students who play some sports and if they don't play any, i want NULL (thus the full outer join),

I tried executing the query :

select student.name, sport.name 
from student 
full outer join plays on student.usn = plays.usn
full outer join sport on plays.sport_id = sport.sport_id;

But, i am getting an error:

#1064 - 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 'outer join plays on student.usn = plays.usn 
LIMIT 0, 25' at line 3

Can you please tell me what is it that i am doing wrong...?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Lakshya Sharma
  • 57
  • 7
  • The accepted answer on the proposed duplicate is simply wrong -- and I don't care if it has 600+ upvotes. It removes duplicates. I'm not sure if the right thing to do in this situation is to reopen the question or not. – Gordon Linoff Nov 24 '19 at 13:07

1 Answers1

1

You don't want a FULL JOIN. You want a LEFT JOIN:

select s.name, sp.name 
from student s left outer join
     plays p
     on s.usn = p.usn left outer join
     sport sp
     on p.sport_id = sp.sport_id;

A left join keeps all rows in the first table and matching rows in the subsequent tables -- which is exactly what you want.

FULL JOIN is rarely needed. I write a lot of SQL and months go by without my using full join in any database.

In this example, a FULL JOIN would be used if you also wanted all sports that have no students. That is NULL values could appar in any column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786