MySQL: Select from one table, add value from another table if exists

Question

Lets assume two tables:

STUDENTS:
id | name | faculty_id

and:

FACULTIES:
id | name

I am trying to query for students by students.id and also return faculties.name where students.faculty_id = faculties.id.

I used:

SELECT students.*, faculties.name FROM students 
JOIN faculties on students.faculty_id = faculties.id
WHERE students.id = ' ... ';

This works well, however there is a problem:

If, by some mistake, the student was assigned a non-existent faculty_id than he would never be found. I would like to write more 'flexible' query that would return faculty.name only if match is found, otherwise just leave it empty.

Also, the query has to have no memory intensive loops, since you could query by name, etc... which results in large number of matches.

dbinns66 · Accepted Answer · 2015-03-12T20:41:38.177

2

A left join will return all students that meet your criteria whether or not student.faculty_id is associated with a valid faculty.id... the ifnull sets the value to blank if rather than "null"

    SELECT 
        students.*, 
        ifnull(faculties.name,'')
    FROM 
        students 
        LEFT JOIN faculties on students.faculty_id = faculties.id
    WHERE students.id = ' ... ';

edited Mar 12 '15 at 20:41

answered Mar 12 '15 at 20:27

dbinns66

790
6
7

score -3 · Answer 2 · edited Mar 12 '15 at 20:20

-3

Instead of JOIN in your query. Try INNER JOIN

SELECT students.*, faculties.name FROM students 
INNER JOIN faculties on students.faculty_id = faculties.id
WHERE students.id = ' ... ';

edited Mar 12 '15 at 20:20

Jens

67,715
15
98
113

answered Mar 12 '15 at 20:19

Brett Ezra

121
1
11

Wouldn't that be the same as JOIN? – Dean Mar 12 '15 at 20:21
There are different JOINs. INNER JOIN, OUTER JOIN FULL JOIN etc. – Brett Ezra Mar 12 '15 at 20:25
3

I meant that JOIN is short for INNER JOIN – Dean Mar 12 '15 at 20:28
1

You can see it's the same here: http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join – Dean Mar 12 '15 at 20:34

MySQL: Select from one table, add value from another table if exists

2 Answers2